问题描述:

I have something like 1300 stored procedures in oracle toad. I want to save each stored procedure to a separate script file as I need to update it to another database. How can this be performed

网友答案:

The easiest way to do this is not to need to do it. Best practice is to write scripts in an editor and run them into the database using TOAD.

Why is this best practice? Two words: source control .

But given that's not where you're at now, you can use DBMS_METADATA.GET_DDL to suck out all the package source. In the docs.


Here is another approach, which makes it easy to produce separate files for each object, that is a file for each package spec and body. Separate files is the best way to source control your PL/SQL. (You can extend this method for stand-alone procedures and functions, types and triggers.

These queries will generate SQL*Plus scripts which you can run in TOAD.

select 'spool '||object_name||'_spec.sql'||chr(10)
       ||'select text from user_source where type=''PACKAGE'' and name = '''||object_name||''' order by line;'||chr(10)
       ||'spool off'
from user_objects 
where object_type='PACKAGE'
order by object_name
/

select 'spool '||object_name||'_body.sql'||chr(10)
       ||'select text from user_source where type=''PACKAGE BODY'' and name = '''||object_name||''' order by line;'||chr(10)
       ||'spool off'
from user_objects 
where object_type='PACKAGE BODY'
order by object_name
/

You may need to adjust the LINESIZE and to switch off HEADINGS, depending on your environment settings.

网友答案:

In Toad see the Export DDL feature off of the main Database menu beneath the Export flyout. Add your objects there. You have several output options and script generation options. For instance you can leave out the schema name if you want to compile them in another schema, etc. Play around with that as I think it'll do anything you need.

相关阅读:
Top