Problem solve Get help with specific problems with your technologies, process and projects.

Exporting a stored procedure/function/package

How do you export a stored procedure/function/package? Is it possible?

Yes, it is possible. But unfortunately, there is no export parameter like TABLE for procedures/function/packages. It would be nice to have a parameter for export like 'FUNCTION=my_function'.

The main reason that this parameter doesn't exist is that many people just reverse engineer the proc/fn/pkg definition. There are many scripts out on the Web to reverse engineer the SQL statements to re-generate the proc/fn/pkg. There is no data involved with these objects, so no real export is necessary. Also, Oracle 9i has the DBMS_METADATA package to reverse engineer objects for you.

If you do need to use export for proc/fn/pkg exports, your only option is a FULL export. Use the ROWS=N parameter so that no data is exported. You'll get all database objects this way! You can use the SHOW=Y parameter on import to see the SQL statements for a proc/fn/pkg.

For More Information

  • What do you think about this answer? E-mail the editors at [email protected] with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture