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.
|