Home » RDBMS Server » Server Utilities » Viewing SP from sql plus window
Viewing SP from sql plus window [message #71237] Sun, 29 September 2002 22:48 Go to next message
rajesh
Messages: 173
Registered: November 1998
Senior Member
hi
I don't have TOAD installed in my machine. Is it possible to see the code of a compiled stored procedure(or a function) from the SQL PLUS window?
If yes, please send me the required select statement to view the code.
Re: Viewing SP from sql plus window [message #71240 is a reply to message #71237] Mon, 30 September 2002 04:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
SQL> ed
Wrote file afiedt.buf

  1* select line,text from user_source where name='WRITE_LOG'
SQL> /

      LINE TEXT
---------- ------------------------------------------------------------
         1 PROCEDURE write_log
         2 (fname IN varchar2,
         3 message IN varchar2,
         4 mo IN varchar2)
         5 is
         6 log_file  UTL_FILE.FILE_TYPE;         -- file handle for the
            log file

         7 begin
         8 log_file := UTL_FILE.FOPEN('/rman/logs',fname,mo);
         9 UTL_FILE.PUTF(log_file,'%sn',message);
        10 UTL_FILE.FCLOSE(log_file);
        11 EXCEPTION
        12         WHEN NO_DATA_FOUND THEN
        13              DBMS_OUTPUT.PUT_LINE('no_data_found');
        14      WHEN UTL_FILE.INVALID_PATH THEN
        15              DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
        16      WHEN UTL_FILE.WRITE_ERROR THEN
        17                 DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR')
           ;

        18              WHEN OTHERS THEN
        19                 DBMS_OUTPUT.PUT_LINE('other stuff');
        20 end;

20 rows selected.
Re: Viewing SP from sql plus window [message #71242 is a reply to message #71237] Mon, 30 September 2002 04:21 Go to previous message
Srinivas
Messages: 138
Registered: June 2000
Senior Member
Try this select statement to see the coding in a stored procedure from SQL prompt (SQL plus).
select text from USER_SOURCE where name='spname' ;
Note that in the above statement, spname is your procedure name.
Another way to see the code written in a stored procedure is by using DBA studio. You can have a gui based screen when you can see many things.
Best of luck
Previous Topic: Re: SQLLoader
Next Topic: Re: SQLLoader
Goto Forum:
  


Current Time: Sun Apr 28 21:58:26 CDT 2024