Oracle PL/SQL/System Tables Views/user source

Check user procedure status and text

   <source lang="sql">
  

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>

 </source>
   
  

Get code from user_source table

   <source lang="sql">
 

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>

 </source>
   
  

Query user_source table for a stored procedure name and procedure type

   <source lang="sql">
 

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>

 </source>