0

I want to fetch a Package DDL in Oracle 8i.

8i doesn't support DBMS_METADATA.get_ddl.

Can it be done using export?

2

DBMS_METADATA.GET_DDL is not mandatory to view the source of a package.

You can use DBA_SOURCE/ALL_SOURCE: https://docs.oracle.com/cd/A87860_01/doc/server.817/a76961/ch277.htm#101236

select text from dba_source
where owner = 'OWNER_OF_PACKAGE'
and name = 'NAME_OF_PACKAGE' order by type, line;
| improve this answer | |
  • Thank you @balazs-papp – Aashutosh Aug 5 '19 at 19:32
1

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 parameters:

# 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.

Source:
Oracle8i Utilities
Release 2 (8.1.6)
Part Number A76955-01
https://docs.oracle.com/cd/A87860_01/doc/index.htm

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.