Oracle PL/SQL/System Tables Views/user source

Check user procedure status and text

   
SQL>
SQL>
SQL> 
SQL> CREATE OR REPLACE PROCEDURE Simple AS
  2    v_Counter NUMBER;
  3  BEGIN
  4    v_Counter := 7;
  5  END Simple;
  6  /
Procedure created.
SQL>
SQL> COLUMN object_name format a20
SQL> COLUMN line format 9999
SQL> COLUMN position format 99999
SQL> COLUMN text format a59
SQL>
SQL> 
SQL> 
SQL> SELECT object_name, object_type, status
  2    FROM user_objects
  3    WHERE object_name = "SIMPLE";
OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- ----------
SIMPLE               PROCEDURE           VALID
1 row selected.
SQL>
SQL> SELECT text FROM user_source WHERE name = "SIMPLE" ORDER BY line;
TEXT
-----------------------------------------------------------
PROCEDURE Simple AS
  v_Counter NUMBER;
BEGIN
  v_Counter := 7;
END Simple;
5 rows selected.
SQL>
SQL> SELECT line, position, text FROM user_errors WHERE name = "SIMPLE" ORDER BY sequence;
no rows selected
SQL>
SQL> CREATE OR REPLACE PROCEDURE Simple AS
  2    v_Counter NUMBER;
  3  BEGIN
  4    v_Counter := 7
  5  END Simple;
  6  /
Warning: Procedure created with compilation errors.
SQL>
SQL>
SQL> SELECT text FROM user_source WHERE name = "SIMPLE" ORDER BY line;
TEXT
-----------------------------------------------------------
PROCEDURE Simple AS
  v_Counter NUMBER;
BEGIN
  v_Counter := 7
END Simple;
5 rows selected.
SQL>

Get code from user_source table

  
SQL>
SQL> prompt set define off
set define off
SQL> select decode( type||"-"||to_char(line,"fm99999"),
  2                 "PACKAGE BODY-1", "/"||chr(10),
  3                  null) ||
  4         decode(line,1,"create or replace ", "" ) ||
  5         text text
  6    from user_source
  7   where name = upper("&1")
  8   order by type, line;
Enter value for 1: prompt /
SQL> prompt set define on
set define on
SQL>
SQL> set feedback on
SQL> set heading on
SQL> set termout on
SQL> set linesize 100
SQL>

Query user_source table for a stored procedure name and procedure type

  
SQL>
SQL> create table myLogTable(
  2    username varchar2(30),
  3    date_time timestamp,
  4    message varchar2(4000) );
Table created.
SQL>
SQL> create or replace procedure log_message( p_message varchar2 ) as
  2  pragma autonomous_transaction;
  3    begin
  4    insert into myLogTable( username, date_time, message )
  5    values ( user, current_date, p_message );
  6    commit;
  7  end log_message;
  8  /
Procedure created.
Procedure created.
SQL>  select text
  2      from user_source
  3     where name = "LOG_MESSAGE"
  4       and type = "PROCEDURE"
  5     order by line;
TEXT
-----------------------------------------------------------------------------------------
procedure log_message( p_message varchar2 ) as
pragma autonomous_transaction;
  begin
  insert into myLogTable( username, date_time, message )
  values ( user, current_date, p_message );
  commit;
end log_message;
7 rows selected.
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL>
SQL>