Queries to generate statements for data migration

Query to generate script to drop table/objects in database.

SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME||' PURGE;'
FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%_EXCEL70';


Query to generate script to get count of value in different tables.

DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
dbms_output.put_line('SELECT '''||I.TABLE_NAME||''', P.SHORT_NAME,COUNT(0)'||CHR(10)||
' FROM '|| I.OWNER||'.'||i.table_name||' T ,POWERMED.FACILITY_PROF P ,POWERMED.LOCATION L
 WHERE P.SHORT_NAME IN (''GRANDVIEW'')
 AND P.SEQ_NUM= T.'||i.column_name||'
 AND P.SEQ_NUM=L.FACILITY_SEQ_NUM
 AND L.SHORT_NAME= ''GRANDVIEW''
 GROUP BY P.SHORT_NAME
UNION ALL') ;
end if;
end loop;
end;

Query to generate table backup statement script with required criteria.


DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
 dbms_output.put_line('CREATE TABLE '||I.TABLE_NAME||'_EXCEL70 AS
 SELECT  T.*  FROM '||I.OWNER||'.'|| i.table_name||' T ,POWERMED.FACILITY_PROF P ,powermed.location l
  WHERE P.SHORT_NAME IN (''GRANDVIEW'')
 AND P.SEQ_NUM= T.'||i.column_name ||'
  AND P.SEQ_NUM=L.FACILITY_SEQ_NUM
 AND L.SHORT_NAME= ''GRANDVIEW''
 /
 ') ;
end if;
end loop;
end;


Query to generate script to disable constraints on tables in database.

   select  'ALTER TABLE '||a.OWNER||'.'||a.table_name||'  DISABLE   CONSTRAINT '|| a.constraint_name ||' ;'
    from all_constraints A,ALL_cons_columns B
WHERE    A.constraint_name = B.constraint_name
AND A.constraint_type in ('R','U')
   and A.table_name  in  ('CHARGE_PROF',
'INS_OFFICE_VISIT',
'LEDGER_ENTRY',
'PATIENT_APPOINTMENT',
'PATIENT_PROF',
'PROVIDER_SCHEDULE',
'SLOT',
'LOCATION')
   --and A.owner = 'SEQUEL'
   and B.column_name='FACILITY_SEQ_NUM'
/

Query to generate script to enable constraints on tables in database.

select 'ALTER TABLE '||a.OWNER||'.'||a.table_name||'  ENABLE    CONSTRAINT '|| a.constraint_name ||' ;'
    from all_constraints A,ALL_cons_columns B
WHERE    A.constraint_name = B.constraint_name
AND A.constraint_type in ('R','U')
   and A.table_name  in  ('CHARGE_PROF',
'INS_OFFICE_VISIT',
'LEDGER_ENTRY',
'PATIENT_APPOINTMENT',
'PATIENT_PROF',
'PROVIDER_SCHEDULE',
'SLOT',
'LOCATION')
   --and A.owner = 'SEQUEL'
   and B.column_name='FACILITY_SEQ_NUM'
/

Query to generate update statement script to update data in tables.


DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
 dbms_output.put_line('UPDATE  '||i.owner||'.'||I.TABLE_NAME||'  N
SET '||I.COLUMN_NAME||'= (SELECT SEQ_NUM FROM POWERMED.FACILITY_PROF WHERE SHORT_NAME=''INCAREHEAL'' )
 WHERE  '||I.COLUMN_NAME||' in (select F.seq_num from POWERMED.FACILITY_PROF F ,POWERMED.LOCATION L WHERE  F.SHORT_NAME IN (''GRANDVIEW'')  AND F.SEQ_NUM=L.FACILITY_SEQ_NUM AND L.SEQ_NUM=N.LOCATION_SEQ_NUM AND L.FACILITY_SEQ_NUM=N.FACILITY_SEQ_NUM AND L.SHORT_NAME= ''GRANDVIEW'')
 /') ;
end if;
end loop;
end;
/