Oracle – How to do Exp and Imp by using PL/SQL

oracleplsql

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

Best Solution

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.