How can I do Exp and Imp by using PL/SQL

Question

How can I do Exp and Imp by using PL/SQL?

Answer 1

This could be done in a few ways.

First, if you're using 10g or later, you can consider using data pump (expdp and impdp) as opposed to imp and exp. These are the newer and more capable versions of those tools.

As for how to call them from PL/SQL, you can do so by:

• You can make an external procedure call to a DLL (or shared library if you're on UNIX)
• you could write a simple Java class (to run in the Oracle JVM) that would call out using Java
• you could use Advanced Queues or DBMS_PIPE to communicate with external applications
• You could use UTL_TCP to interact over the network (e.g. SOAP)
• In 10g, you could use the DBMS_SCHEDULER package to call OS commands

The first and last options should be well documented in the Oracle online docs - the other two options would require a little more coordination and coding.

Answer 2

If you are using impdp/expdp - Datapump (10g or later) - , you can simply use DBMS_DATAPUMP:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_datpmp.htm

Answer 3

You can see an example about exactly you are requesting in the Examples of Using the Data Pump API inside the Oracle Database Utilities book.