The source code for compiled procedures, functions, packages, or package bodies is accessible from the USER_SOURCE view.
Refer to Chapter 5 for a detailed explaination on the differences among data dictionary views that have prefixes: USER, ALL, and DBA. The PL/SQL code you compile in your schema can be selected at any time from USER_SOURCE.
Query USER_SOURCE for the PL/SQL code you have compiled in you account.
Query ALL_SOURCE for everything in USER_SOURCE plus all other PL/SQL to which you have been given the EXECUTE privilege.
Query DBA_SOURCE is all the PL/SQL code in the database.
Because the scope of DBA views is everything in the database, you must have either the Oracle DBA role or the Oracle SELECT_CATALOG_ROLE role. The DBA role has high privileges. The SELECT_CATALOG_ROLE is intended for users who need to query data dictionary views. Application developers should be given this role.
The view USER_OBJECT uses the column names:
OBJECT_NAME
OBJECT_TYPE
The views USER_DEPENDENCIES and USER_SOURCE refer to the same components with a different name:
NAME
TYPE
You can always retrieve stored procedure code from this view. A procedure recreated and compiled with errors is retrievable from this view.
SQL> desc user_source Name Null? Type ------------------------------ -------- --------------- NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000)
The columns in the USER_SOURCE view have the following description.
NAME | This is the name in the CREATE OR REPLACE clause. This is not the host file. Running the script @MY_HELLO.SQL with a CREATE OR REPLACE PROCEDURE HELLO statement creates the object name HELLO. The data dictionary stores all attributes in upper case. |
TYPE | This is FUNCTION, PROCEDURE, PACKAGE, or PACKAGE BODY. There is never an underscore in PACKAGE BODY. |
LINE | This identifies a line of text. A 50-line text file compiled as a stored procedure is 50 lines of text in the data dictionary. Oracle does not reformat the text. If the program fails at run time, a line number will be included in the error message. This should be matched with the LINE number in this view. It is always possible to identify the specific line within a stored procedure at which execution failed. |
TEXT | This is a line of text as read from the original source file. |
The text of a PL/SQL program is available through this view. You can query USER_SOURCE and redirect the code text to a SQL*Plus spool file. If the original source code is lost, you can redirect the text to a spool file with a SQL extension. The following SQL script queries the source for the HELLO procedure spooling it to a SQL file named MY_HELLO.SQL. The SQL*Plus command SET TERM suppresses output for the script while the source HELLO is spooled to the SQL file.
-- Filename generate_my_hello.sql set feedback off set pagesize 0 set term off spool my_hello.sql SELECT '-- Filename MY_HELLO.SQL' FROM dual; SELECT 'CREATE OR REPLACE ' FROM dual; SELECT text FROM user_source WHERE name='HELLO'; SELECT '/' FROM DUAL; spool off set term on set feedback on set pagesize 1000
Running this script generates a file MY_HELLO.SQL. You can edit and recompile the HELLO procedure using this output file.
SQL> @generate_my_hello.sql
This produces the following text file, MY_HELLO.SQL, as shown next:
-- Filename MY_HELLO.SQL CREATE OR REPLACE PROCEDURE hello IS BEGIN dbms_output.put_line('Hello'), END; /
You select the specification of an Oracle package from ALL_SOURCE. The following generates a spool file DBMS_OUTPUT with the package specification and documentation for using this Oracle package.
set feedback off set pagesize 0 set term off spool dbms_output SELECT text FROM all_source WHERE name='DBMS_OUTPUT'; spool off set term on set feedback on set pagesize 1000
The output from this script will be a text file, DBMS_OUTPUT.LST, shown next. This text, extracted from ALL_SOURCE, is the package specification as compiled in the database. The following illustrates the clarity of the interface definition and documentation that accompanies many of the Oracle packages.
package dbms_output as ------------ -- OVERVIEW -- -- These procedures accumulate information in a buffer -- (via "put" and "put_line") so that it can be retrieved -- out later (via "get_line" or "get_lines"). If this -- package is disabled then all calls to this package are -- simply ignored. This way, these routines are only -- active when the client is one that is able to deal -- with the information. This is good for debugging, or -- SP's that want to display messages or reports -- to sql*dba or plus (like 'describing procedures', etc.). -- The default buffer size is 20000 bytes. The -- minimum is 2000 and the maximum is 1,000,000 ----------- -- EXAMPLE -- -- A trigger might want to print out some debugging -- information. To do this the trigger would do -- dbms_output.put_line('I got here:'||:new.col||' is -- the new value'), If the client had enabled the -- dbms_output package then this put_line would be -- buffered and the client could, after executing the -- statement (presumably some insert, delete or update -- that caused the trigger to fire) execute -- begin dbms_output.get_line(:buffer, :status); end; -- to get the line of information back. It could then -- display the buffer on the screen. The client would -- repeat calls to get_line until status came back as -- non-zero. For better performance, the client would -- use calls to get_lines which can return an array of -- lines. -- -- SQL*DBA and SQL*PLUS, for instance, implement a -- 'SET SERVEROUTPUT ON' command so that they know -- whether to make calls to get_line(s) after issuing -- insert, update, delete or anonymous PL/SQL calls -- (these are the only ones that can cause triggers or -- stored procedures to be executed). ------------ -- SECURITY -- -- At the end of this script, a public synonym -- (dbms_output) is created and execute permission on -- this package is granted to public. ---------------------------- -- PROCEDURES AND FUNCTIONS -- procedure enable (buffer_size in integer default 20000); pragma restrict_references(enable,WNDS,RNDS); -- Enable calls to put, put_line, new_line, get_line -- and get_lines. Calls to these procedures are -- noops if the package has not been enabled. -- Set default amount of information to buffer. -- Cleanup data buffered from any dead sessions. -- Multiple calls to enable are allowed. -- Input parameters: -- buffer_size -- Amount of information, in bytes, to buffer. -- Varchar2, number and date items are stored in -- their internal representation. The information -- is stored in the SGA. An error is raised if the -- buffer size is exceeded. If there are multiple -- calls to enable, then the buffer_size is generally -- the largest of the values specified, and will -- always be >= than the smallest value -- specified. Currently a more accurate determination -- is not possible. The maximum size is 1,000,000, -- the minimum is 2000. procedure disable; pragma restrict_references(disable,WNDS,RNDS); -- Disable calls to put, put_line, new_line, get_line -- and get_lines. Also purge the buffer of any remaining -- information. procedure put(a varchar2); pragma restrict_references(put,WNDS,RNDS); procedure put(a number); pragma restrict_references(put,WNDS,RNDS); -- Put a piece of information in the buffer. -- When retrieved by get_line(s), the number and -- date items will be formated with to_char using -- the default formats. If you want another format -- then format it explicitly. -- Input parameters: -- a -- Item to buffer procedure put_line(a varchar2); pragma restrict_references(put_line,WNDS,RNDS); procedure put_line(a number); pragma restrict_references(put_line,WNDS,RNDS); -- Put a piece of information in the buffer followed by -- an end-of-line marker. When retrieved by get_line(s), -- the number and date items will be formated with -- to_char using the default formats. If you -- want another format then format it explicitly. -- get_line(s) return "lines" as delimited by "newlines". -- So every call to put_line or new_line will generate a -- line that will be returned by get_line(s). -- Input parameters: -- a -- Item to buffer -- Errors raised: -- -20000, ORU-10027: buffer overflow, limit of -- <buf_limit> bytes. -- -20000, ORU-10028: line length overflow, limit -- of 255 bytes per line. procedure new_line; pragma restrict_references(new_line,WNDS,RNDS); -- Put an end-of-line marker. get_line(s) return "lines" -- as delimited by "newlines". So every call to -- put_line or new_line will generate a line that will -- be returned by get_line(s). -- Errors raised: -- -20000, ORU-10027: buffer overflow, limit of -- <buf_limit> bytes. -- -20000, ORU-10028: line length overflow, limit -- of 255 bytes per line. procedure get_line(line out varchar2, status out integer); pragma restrict_references(get_line,WNDS,RNDS); -- Get a single line back that has been buffered. -- The lines are delimited by calls to put_line or -- new_line. The line will be constructed taking all -- the items up to a newline, converting all the items -- to varchar2, and concatenating them into a single -- line. If the client fails to retrieve all lines before -- the next put, put_line or new_line, the non-retrieved -- lines will be discarded. This is so if the client is -- interrupted while selecting back the information, -- there will not be junk left over which would -- look like it was part of the NEXT set of lines. -- Output parameters: -- line -- This line will hold the line - it may be up to 255 -- bytes long. -- status -- This will be 0 upon successful completion of the -- call. 1 means that there are no more lines. type chararr is table of varchar2(255) index by binary_integer; procedure get_lines(lines out chararr, numlines in out integer); pragma restrict_references(get_lines,WNDS,RNDS); -- Get multiple lines back that have been buffered. -- The lines are delimited by calls to put_line or -- new_line. The line will be -- constructed taking all the items up to a newline, -- converting all the items to varchar2, and -- concatenating them into a single line. Once get_lines -- is executed, the client should continue to retrieve -- all lines because the next put, put_line or new_line -- will first purge the buffer of leftover data. This is -- so if the client is interrupted while selecting back -- the information, there will not be junk left over. -- Input parameters: -- numlines -- This is the maximum number of lines that the -- caller is prepared to accept. This procedure will -- not return more than this number of lines. -- Output parameters: -- lines -- This array will hold the lines - they may -- be up to 255 bytes long each. The array is indexed -- beginning with 0 and increases sequentially. From a -- 3GL host program the array begins with whatever is -- the convention for that language. -- numlines -- This will be the number of lines actually returned. -- If it is less than the value passed in, then there -- are no more lines. end;
3.141.37.10