How do I Open a Stored Procedure and Edit it in SQL*Plus

Question

I need to make some changes to an old Oracle stored procedure on an obsolete box that is being kept around to run an old legacy process. The only tool I can connect to the db with is SQLPlus. How do I load a stored proc into memory for editing in SQLPlus?

The tutorials I've found on-line don't exlain how that's done. :-(

Solution 1

The box is HPUX with no TNS listener running which pretty much wipes out the entire fleet of dev tools. However, the DBA was able to connect using an admin tool called OEM and make the necissary changes. Thanks all for your suggestions.

Solution 2

It would be a lot easier to download the trial version of TOAD

scratch that previous suggestion, I just tried out the Oracle SQL Developer link suggested and it works peachy fine for editing procs.

for SQLPlus you pretty much need to display the contents of the stored proc, and spool it to a file (as suggested by DCookie :-)) :

sqlplus> spool myprocname.sql;
sqlplus> select text from all_source where name = 'MYPROCNAME' and type = 'PROCEDURE' order by line;
sqlplus> quit;

then edit the local SQL file in a decent editor.

Then use SQLPlus to run the SQL file to re-build the proc for testing.

>sqlplus username/password@tnsnamesentry @myproc.sql

In short, a massive pain in the keester. :-)

Solution 3

You can use dbms_metadata.get_ddl function to retrieve code, it is much better then all_sources.

spool /tmp/my_proc.txt
set long 100000
select dbms_metadata.get_ddl('PROCEDURE','NAME','OWNER') from dual;
spool off