A script-enabled browser is required for this page to function properly.

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,destination_cursor);
  ELSE
 EXIT;
END IF;
END LOOP;
  EXEC_SQL.PARSE(destination_connid, destination_cursor, 'commit');
  ignore := EXEC_SQL.EXECUTE(destination_connid,destination_cursor);
  EXEC_SQL.CLOSE_CURSOR(destination_connid,destination_cursor);
  EXEC_SQL.CLOSE_CURSOR(source_connid, source_cursor);
  EXEC_SQL.CLOSE_CONNECTION(destination_connid);
  EXEC_SQL.CLOSE_CONNECTION(source_connid);
 EXCEPTION
 WHEN EXEC_SQL.PACKAGE_ERROR THEN
  IF EXEC_SQL.LAST_ERROR_CODE(source_connid) != 0 THEN
   TEXT_IO.PUT_LINE('ERROR (source: ' || 
   TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(source_connid))|| '): ' ||
   EXEC_SQL.LAST_ERROR_MESG(source_connid));
  END IF;
 IF EXEC_SQL.LAST_ERROR_CODE(destination_connid) != 0 THEN
  TEXT_IO.PUT_LINE('ERROR (destination: ' ||
  TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(destination_connid))|| '): ' ||
  EXEC_SQL.LAST_ERROR_MESG(destination_connid));
 END IF;
 IF EXEC_SQL.IS_CONNECTED(destination_connid) THEN
 IF EXEC_SQL.IS_OPEN(destination_connid,
  destination_cursor) THEN
  EXEC_SQL.CLOSE_CURSOR(destination_connid,destination_cursor);
 END IF;
  EXEC_SQL.CLOSE_CONNECTION(destination_connid);
 END IF; 
  IF EXEC_SQL.IS_CONNECTED(source_connid) THEN
  IF EXEC_SQL.IS_OPEN(source_connid, source_cursor) THEN
  EXEC_SQL.CLOSE_CURSOR(source_connid, source_cursor);
 END IF;
 EXEC_SQL.CLOSE_CONNECTION(source_connid);
 END IF; 
END;
 
 
 
 
 
 
 
 
 
 
 
 
 
 
-- Open the connections. If the user does not specify a secondary 
 connection, the primary connection is used.




-- Prepare a cursor to select from the source table.







-- Prepare a cursor to insert into the destination table.


-- Fetch a row from the source table, and insert it into the destination table.
-- Get column values for the row; these are stored as local variables.

-- Bind the values into the cursor that inserts into the destination table. 





-- No more rows to copy.


-- Commit the destination cursor.


-- And close everything.



-- This is the general error raised by the EXEC_SQL package. Get information 
 (error number and message) about the error on the source connection or 
 the destination connection.




-- Close all connections and cursors.

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package

Changing the primary database connection

Connection and cursor handles

EXEC_SQL predefined exceptions

Retrieving result sets from queries or non-Oracle stored procedures

About built-in packages