Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

How to get procedure code in Oracle

How to get procedure code in Oracle
Frequently we need to find the procedure code, without using any developers’ tools like SQL Developer or TOAD. The easiest way to get the code is to use USER_SOURCE or DBA_SOURCE views or DBMS_METADATA PL/SQL Package.

Below example shows both using USER_SOURCE, and DBMS_METADATA.GET_DDL function. It assumes that procedure resides in current user’s schema.

SQL> conn user1/pass1
Connected.
SQL>
SQL> create or replace procedure proc100
2 as
3 begin
4 dbms_output.put_line('Krystian');
5 end;
6
7 /

Procedure created.

SQL> set serveroutput on
SQL> exec proc100
Krystian

PL/SQL procedure successfully completed.

SQL> select text from user_source where name = 'PROC100' order by line
2 /

TEXT
--------------------------------------------------------------------------
procedure proc100
as
begin
dbms_output.put_line('Krystian');
end;

SQL> set long 2000000
SQL> select dbms_metadata.get_ddl('PROCEDURE','PROC100')
2 from dual
3 /

DBMS_METADATA.GET_DDL('PROCEDURE','PROC100')
-------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE "USER1"."PROC100"
as
begin
dbms_output.put_line('Krystian');
end;

To get procedure source as administrator we will use DBA_SOURCE view, and one more time DBMS_METADATA.GET_DDL function but this time with additional parameter , which enables to specify the schema

SQL> conn / as sysdba
Connected.
SQL> select text from dba_source
2 where name = 'PROC100' and owner = 'USER1'
3 order by line
4 /

TEXT
------------------------------------------------------------------------
procedure proc100
as
begin
dbms_output.put_line('Krystian');
end;

SQL> select dbms_metadata.get_ddl('PROCEDURE','PROC100','USER1')
2 from dual
3 /

DBMS_METADATA.GET_DDL('PROCEDURE','PROC100','USER1')
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE "USER1"."PROC100"
as
begin
dbms_output.put_line('Krystian');
end;
Categories: