Query user_source table for a stored procedure name and procedure type : user_source « System Tables Views « Oracle PL / SQL

1. Oracle PL / SQL
2. System Tables Views
3. user_source

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>

   
  

Related examples in the same category