Re: Stored Procedures

Steven H. wrote:
>
> Is there an easy way of retrieving stored procedures from
> a database (Oracle 7.1)? At the moment we have to do a
> select statement in SQL Plus to retrieve the code from one
> of the internal tables, but the output is not easy to manage -
> we have to select the output with the mouse and copy it into
> another program.

Yeah that's a problem.
Well the best idea that I can think of is to write a quick script to automatically do it for any stored procedures.

Lets say that you had a stored procedure in the data dictionary. This script is independent of the name - you will be promplted for it.

create a script file : PLSQL.sql

Now store the following info in it :

spool c:\Code.txt
select text from user_source
where name='&ProcedureName'
order by line
/
spool off

Now save this file in some directory, for the moment the root directory c:\PLSQL.sql.

In SQLPlus at the prompt type the following: SQL> @\PLSQL You will be prompted for the Stored procedure name. Enter it - beware that it may be stored in capitals, so if necessary enter it as such.

This will get the info that you need and store it in the file Code.txt. This script is less that perfect (I mean you will have to delete the query that produced the code at the top - but hey), but it gets the job done.

You can make it better. Change the name Code.txt to whatever name you prefer.

Cheerio.

Kugendran Naidoo
Iscor I.T.
Newcastle
South Africa Received on Wed Jul 23 1997 - 00:00:00 CDT