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 SQL*Plus. How do I load a stored proc into memory for editing in SQL*Plus?
The tutorials I've found on-line don't exlain how that's done. :-(
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. :-)
Ron
Can you not download and install Oracle SQL Developer? It's free.
For Unix, there's TOra, (open source)
If you can connect to the db using SQL*Plus, you should be able to connect using a SQL IDE. That said, you cannot open a proc for edit in SQL*Plus, but you can compile one. You can copy your updated proc into the command prompt (line by line if necessary) and compile it.
If you need to access the current source in the manual fashion, you can query the USER_SOURCE table.
I strongly suggest, however, looking into connecting to the DB with an IDE.
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.