The code of the Oracle definition object can be found in the user_source data dictionary.

// In Oracle, we can view the object definition code from the data dictionary user_source (View;
// Let's first look at the structure of the user_source View:
Desc user_source;
Name Type Nullable Default Comments
-----------------------------------------------------------------------------------------------------
NAME VARCHAR2 (30) Y Name of the object
TYPE VARCHAR2 (12) Y Type of the object: "TYPE", "type body", "PROCEDURE", "FUNCTION ",
"PACKAGE", "package body" or "java source"
Line number y Line number of this line of source
TEXT VARCHAR2 (4000) Y Source text
//
// The following code defines the user_source View:
Create or replace force view "SYS". "USER_SOURCE" ("NAME", "TYPE", "LINE", "TEXT")
Select o. name,
Decode (o. type #,
7, 'processure ',
8, 'function ',
9, 'package ',
11, 'package body ',
12, 'trigger ',
13, 'type ',
14, 'Type body ',
'Undefined '),
S. line,
S. source
From sys. obj $ o,
Sys. source $ s
Where o. obj # = s. obj #
And (o. type # in (7, 8, 9, 11, 12, 14) OR
(O. type # = 13 AND o. subname is null ))
And o. owner # = userenv ('schemaid ')
Union all
Select o. name,
'Java source ',
S. joxftlno,
S. joxftsrc
From sys. obj $ o,
X $ joxfs s
Where o. obj # = s. joxftobn
And o. type # = 28
And o. owner # = userenv ('schemaid ');
//
// The following is a procedure: show_employee,
// Now let's look at its definition code. Note that the passed parameters should be capitalized:
Set linesize 1000;
Set pagesize 1000;
Set long 10000;
Select type, line | ''| text
From user_source
Where name = 'show _ EMPLOYEE ';
//
Type line | ''| TEXT
------------ Success -----------------------------------------------------------------------------------------------------
PROCEDURE 1 procedure show_employee (empno_in in emp. empno % type)
PROCEDURE 2
PROCEDURE 3 v_sign number;
PROCEDURE 4 v_empno emp. empno % type;
PROCEDURE 5 v_ename emp. ename % type;
PROCEDURE 6 v_deptno emp. deptno % type;
PROCEDURE 7 begin
PROCEDURE 8 select 1 into v_sign
PROCEDURE 9 from dual
PROCEDURE 10 where exists (select count (*) from emp where empno = empno_in );
PROCEDURE 11 if v_sign = 1 then
PROCEDURE 12 select empno, ename, deptno into v_empno, v_ename, v_deptno
PROCEDURE 13 from emp where empno = empno_in;
PROCEDURE 14 dbms_output.put_line ('information of '| empno_in | 'are :');
PROCEDURE 15 dbms_output.put_line ('empno: '| v_empno |', ename: '| v_ename |', deptno: '| v_deptno );
PROCEDURE 16 end if;
PROCEDURE 17 exception
PROCEDURE 18 when others then
PROCEDURE 19 dbms_output.put_line ('no data found ');
PROCEDURE 20 end show_employee;
PROCEDURE 21
PROCEDURE 22
//
// Let's execute the Stored Procedure show_employee:
Exec show_employee ('20140901 ');
Information of7788 are:
Empno: 7788, ename: SCOTT, deptno: 20
PL/SQL procedure successfully completed