Copy tables : Utility Procedure « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Utility Procedure

Copy tables

 
SQL>
SQL> CREATE TABLE source (
  2    key NUMBER(5),
  3    value VARCHAR2(50)  );

Table created.

SQL>
SQL>
SQL> CREATE TABLE destination (
  2    key NUMBER(5),
  3    value VARCHAR2(50)  );

Table created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE CopyTables AS
  2    v_Key   source.key%TYPE;
  3    v_Value source.value%TYPE;
  4
  5    CURSOR c_AllData IS
  6      SELECT *
  7        FROM source;
  8  BEGIN
  9    OPEN c_AllData;
 10
 11    LOOP
 12      FETCH c_AllData INTO v_Key, v_Value;
 13      EXIT WHEN c_AllData%NOTFOUND;
 14
 15      INSERT INTO destination (key, value)
 16        VALUES (v_Key, TO_NUMBER(v_Value));
 17    END LOOP;
 18
 19    CLOSE c_AllData;
 20  END CopyTables;
 21  /

Procedure created.

SQL>
SQL> show error
No errors.
SQL>
SQL> drop table source;

Table dropped.

SQL>
SQL> drop table destination;

Table dropped.

SQL>
SQL>

 

Related examples in the same category