Re: How to export stored procedures

Actually, you have to:

select	text
from	user_source
order	by
	name,
	type,
	line;

You have to order the text so that it comes out in the proper sequence. Note that store procedures, functions and packages are in user_source (which is actually a view of sys.obj$ and sys.source$).

Packages and the associated package body have the same name, so you need to order on the type. Otherwise the package and package body gets co-mingled, leading to compiliation errors.

If you're planning on using the file so that you can compile all the procs, functions, and packages in another schema, you might want to write a ProC program, which inserts the 'create or replace ' at the top of the script, as well as a character '/' at the end of each proc.

I've written a small program that does just that (had to compile 200 procs in a test db). It's easy enough to do, as well as for triggers and views. Email me if you want a copy.

Phil

In article <33A0309E.5049_at_gssec.bt.co.uk>, Brian Ewins <Brian.Ewins_at_gssec.bt.co.uk> says:
>
>Jakub Dadak wrote:
>>
>> Helo all,
>>
>> how to simply export all stored procedures in schema.
>
>connect <schema>
>select text from user_source;
Received on Fri Jun 13 1997 - 00:00:00 CDT