data_tran.sql

This procedure is useful when one wants to propagate new records to other Oracle sites where there is no networking; for example, if you want to send
master data such as new product details or sales tax information to sales depots. The approach adopted here is 'STORE AND FORWARD'. Using
ORACLE's time proven Export/Import utilites, one can transfer the data into an user that has been especially created for this purpose in the remote site. The
user should have privileges to update tables in other users' schema. All the database tables should be dropped before import. Input the target owner name of
the target database schema. The tables must have at least one column as primary key. This PL/SQL procedure transfers data based on the tables in
table_order table to an user HYD. 

This routine finds all primary keys defined for a given table in the data dictionary (for owners other than SYS and SYSTEM). It could be used by any user to
see the primary keys which are "available" to them. The table name is passed as parameter. It then builds dynamic SQL statements that are executed using the
Oracle-provided package DBMS_SQL to update the target table. This continues until all the tables are updated. 


SET SERVEROUTPUT ON;
DECLARE
------------------------------------------------------------------------
-- The following cursor fetches table name from the table order list.
-----------------------------------------------------------------------
CURSOR table_list IS
SELECT table_name FROM table_order; 
-------------------------------------------------------------------------
-- The following cursor identifies the primary key of a specified table.
-- This is required only when new rows are to be inserted into the target
-- table.
-------------------------------------------------------------------------
cursor pk_columns (oner IN VARCHAR2, tname IN VARCHAR2) is
(SELECT CC.COLUMN_NAME
  FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS CC
 WHERE C.OWNER = UPPER(oner)
   AND C.TABLE_NAME = UPPER(tname)
   AND C.OWNER NOT IN ('SYS','SYSTEM')
   AND C.CONSTRAINT_TYPE = 'P'
   AND CC.OWNER = UPPER(oner)
   AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME);
--ORDER BY C.OWNER, C.TABLE_NAME, CC.POSITION
------------------------------------------------------------------------- 
column_id       pk_table.pk_column%type;
tabname         table_order.table_name%type;
str             VARCHAR2(2000);
owner           VARCHAR2(30);
 
-------------------------------------------------------------------------
-- The following procedure executes the DDL statements, which in turn 
-- update the target table.
------------------------------------------------------------------------- 
PROCEDURE DYNASQL (string IN VARCHAR2) AS
  cur integer;
  rc  integer;
BEGIN
--dbms_output.put_line(str);
--dbms_output.put_line(string);

  cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur, string, DBMS_SQL.NATIVE);
  rc := DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
END;
 
-------------------------------------------------------------------------
-- The following is the string concate and calling the procedures.
-------------------------------------------------------------------------
Begin
owner := 'EQLOS'; 
dbms_output.enable;
OPEN table_list;

LOOP
FETCH table_list into tabname;
EXIT WHEN table_list%notfound;
OPEN pk_columns (user, tabname);

fetch pk_columns into column_id;
str     := str || column_id || ',';

<<inner_loop>>
LOOP
fetch pk_columns into column_id;
exit when pk_columns%notfound;
str     := 'CONCAT('||str||column_id||')'||','; 
END LOOP;

close pk_columns;
--str := '*,';  
----------------------------------------------------------------
--Delete above line if you want to compare by primary keys only.
----------------------------------------------------------------
str := substr(str,1,(length(str) - 1));
----------------------------------------------------------------
-- Removes the trailing comma.
----------------------------------------------------------------
 
str := 'insert into '|| owner||'.'||tabname ||'( SELECT * FROM '|| tabname || 
        ' WHERE '|| str||' IN '||' (select ' || str || ' from ' || tabname 
        || ' minus ' || 'select ' || str || ' from ' || owner||'.'||tabname || '))';

--dbms_output.put_line(str);  
                                          
dynasql(str);
str := ' ';
-----------------------------------------------------------------
-- Initializes the str variable here.
-----------------------------------------------------------------
END LOOP;

CLOSE table_list;   
 
EXCEPTION
  WHEN OTHERS THEN
    CLOSE pk_columns; 
    CLOSE table_list; 
    dbms_output.put_line('Severe Error in processing...');  
END;
/
--------------------------------------------------------------------------
-- The following script is for creating table_order table.
--------------------------------------------------------------------------
drop table table_order;
create table table_order
(srl_no     number(3)    not null,
table_name  varchar2(30) not null,
primary key  (srl_no));
--------------------------------------------------------------------------