Copying data between two databases
The following procedure does not specifically require the use of dynamic SQL, but it illustrates the
concepts in the EXEC_SQL package.
The procedure copies the rows from the source table (on the source connection) to the destination table (on
the destination connection). It assumes the source and destination tables have the following columns:
ID of type NUMBER
NAME of type VARCHAR2(30)
BIRTHDATE of type DATE
PROCEDURE copy (source_table IN VARCHAR2,
destination_table IN VARCHAR2,
source_connection IN VARCHAR2 DEFAULT NULL,
destination_connection IN VARCHAR2 DEFAULT NULL)
IS
id NUMBER;
name VARCHAR2(30);
birthdate DATE;
source_connid EXEC_SQL.ConnType;
destination_connid EXEC_SQL.ConnType;
source_cursor EXEC_SQL.CursType;
destination_cursor EXEC_SQL.CursType;
ignore PLS_INTEGER
BEGIN
IF source_connection IS NULL THEN
source_connid := EXEC_SQL.DEFAULT_CONNECTION;
ELSE
source_connid :=
EXEC_SQL.OPEN_CONNECTION(source_connection);
END IF;
IF destination_connection IS NULL THEN
destination_connid := EXEC_SQL.CURR_CONNECTION;
ELSE
destination_connid :=
EXEC_SQL.OPEN_CONNECTION(destination_connection);
END IF;
source_cursor := EXEC_SQL.OPEN_CURSOR(source_connid);
EXEC_SQL.PARSE(source_connid, source_cursor,
'SELECT id, name, birthdate FROM ' || source_table);
EXEC_SQL.DEFINE_COLUMN(source_connid, source_cursor, 1,
id);
EXEC_SQL.DEFINE_COLUMN(source_connid, source_cursor, 2,
name, 30);
EXEC_SQL.DEFINE_COLUMN(source_connid, source_cursor, 3,
birthdate);
ignore := EXEC_SQL.EXECUTE(source_connid, source_cursor);
destination_cursor :=
EXEC_SQL.OPEN_CURSOR(destination_connid);
EXEC_SQL.PARSE(destination_connid, destination_cursor,
'INSERT INTO ' || destination_table || '
(id, name, birthdate) VALUES (:id, :name, :birthdate)');
LOOP
IF EXEC_SQL.FETCH_ROWS(source_connid, source_cursor) > 0
THEN
EXEC_SQL.COLUMN_VALUE(source_connid, source_cursor,
1, id);
EXEC_SQL.COLUMN_VALUE(source_connid, source_cursor,
2, name);
EXEC_SQL.COLUMN_VALUE(source_connid, source_cursor,
3, birthdate);
EXEC_SQL.BIND_VARIABLE(destination_connid,
destination_cursor, ':id', id);
EXEC_SQL.BIND_VARIABLE(destination_connid,
destination_cursor, ':name', name);
EXEC_SQL.BIND_VARIABLE(destination_connid,
destination_cursor, ':birthdate', birthdate);
ignore := EXEC_SQL.EXECUTE(destination_connid,

which part d u want?