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.

Related Solutions

Mysql – How to select the nth row in a SQL database table

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Sql – the difference between SQL, PL-SQL and T-SQL

SQL is a query language to operate on sets.

It is more or less standardized, and used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.
PL/SQL is a proprietary procedural language used by Oracle
PL/pgSQL is a procedural language used by PostgreSQL
TSQL is a proprietary procedural language used by Microsoft in SQL Server.

Procedural languages are designed to extend SQL's abilities while being able to integrate well with SQL. Several features such as local variables and string/data processing are added. These features make the language Turing-complete.

They are also used to write stored procedures: pieces of code residing on the server to manage complex business rules that are hard or impossible to manage with pure set-based operations.

Best Solution

Related Solutions

Mysql – How to select the nth row in a SQL database table

Sql – the difference between SQL, PL-SQL and T-SQL

Related Question