I want to fetch a Package DDL in Oracle 8i.
8i doesn't support DBMS_METADATA.get_ddl.
Can it be done using export?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
DBMS_METADATA.GET_DDL is not mandatory to view the source of a package.
You can use
select text from dba_source where owner = 'OWNER_OF_PACKAGE' and name = 'NAME_OF_PACKAGE' order by type, line;
You are right, you can use export to get the source code. You export into a dumpfile and the you import the dumpfile with the SHOW=Y option. When you use this option the dumpfile is not imported into the database but the commands that the import would use will be written to logfile of the import.
You cannot export only a package but whole schema or the whole database.
# export schema SCOTT, do not export table data or table statistics: OWNER=SCOTT ROWS=NO STATISTICS=NONE # define the log file and the dump file LOG=/tmp/exp_schema.log FILE=/tmp/schema.dmp
You can store this parameters in a file, e,g, /tmp/exp_schema.par and then execute
exp system/manager paefile=/tmp/exp_schema.par
for import you can use the following parameters
# SHOW=Y causes the program to write the commands to the logdile FULL=Y SHOW=Y # define the log file and the dump file LOG=/tmp/imp_schema.log FILE=/tmp/schema.dmp
You can use the following parameters in export or import parameter file to avoid the generation of code for constraints,indexes, grants or triggers.
GRANTS=N INDEXES=N TRIGGERS=N # valid only for export CONSTRAINTS=N
The importlogfile will contain the source code of all packages, function and procedures of schema SCOTT. Maybe you have to edit the extracted code and remove some line breaks that were added by the import program.
Release 2 (8.1.6)
Part Number A76955-01