9.6 USER_SOURCE

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;