Bumped by Community user
    Bumped by Community user
    Bumped by Community user
4 Update on the format, clarifying the jobs
source | link

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have: that is running in DEV.

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA''DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')      
      loop      
      begin     
       execute immediate   execute immediate 'insert'INSERT intoINTO '||r.table_name|| ||@DBLINKSIT2
 r.table_name || '@DBLINKSIT2 SELECT * FROM AAA                   select a.'* 
                          from DDSHIST.||r.table_nametable_name|| ;a  
         commit;                 INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION 
                          WHERE b.GOLDEN_COPY = 'N'';   
       exception when others then null; 
      end;
      end loop;     
    end;

I think I would just edit here since I cannot comment yet.

Actually this isput COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not a one-off solution. My apologies I should have provided more information.

This is actually to be incorporated into SAS Data Integration, and thencontain this will be run as a batch jobcolumn. So for every month, the job will be triggeredjoining condition is both SNAPSHOT_DT are the same, and backupDDS_VERSION are the same, WHERE golden copy to SIT2in LKUP table = 'Y'. Would export/import or network_link dothen loop the job?script, and insert into @DBLINKSIT2.

Before exporting/importing,But I have another job, which is truncatingcan't get the tables in SIT2 so DEV data can be exportedscript to SIT2run and I don't know where I am getting this wrong.

Any help would be appreciated. Thank you.

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2. Below is the syntax that I have:

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA')     
      loop      
      begin     
           execute immediate 'insert into ' || r.table_name || '@DBLINKSIT2 SELECT * FROM AAA.' ||r.table_name ; 
         commit;
      end;
      end loop;     
    end;

I think I would just edit here since I cannot comment yet.

Actually this is not a one-off solution. My apologies I should have provided more information.

This is actually to be incorporated into SAS Data Integration, and then this will be run as a batch job. So for every month, the job will be triggered and backup the golden copy to SIT2. Would export/import or network_link do the job?

Before exporting/importing, I have another job, which is truncating the tables in SIT2 so DEV data can be exported to SIT2.

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.

begin       
    for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')      
      loop      
      begin     
       execute immediate     'INSERT INTO ||r.table_name|| @DBLINKSIT2
                          select a.* 
                          from DDSHIST.||r.table_name|| a  
                          INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION 
                          WHERE b.GOLDEN_COPY = 'N'';   
       exception when others then null; 
      end;
      end loop;     
    end;

I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.

But I can't get the script to run and I don't know where I am getting this wrong.

Any help would be appreciated. Thank you.

3 Extra Information, clarify format
source | link

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2. Below is the syntax that I have:

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA')     
      loop      
      begin     
           execute immediate 'insert into ' || r.table_name || '@DBLINKSIT2 SELECT * FROM AAA.' ||r.table_name ; 
         commit;
      end;
      end loop;     
    end;

I think I would just edit here since I cannot comment yet. Actually

Actually this is not a one-off solution. My apologies I should have provided more information. This

This is actually to be incorporated into SAS Data Integration, and then this will be run as a batch job. So for every month, the job will be triggered and backup the golden copy to SIT2. Would export/import or network_link do the job?

Before exporingexporting/importing, I have another job, which is truncating the tables in SIT2 so DEV data can be exported to SIT2.

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2. Below is the syntax that I have:

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA')     
      loop      
      begin     
           execute immediate 'insert into ' || r.table_name || '@DBLINKSIT2 SELECT * FROM AAA.' ||r.table_name ; 
         commit;
      end;
      end loop;     
    end;

I think I would just edit here since I cannot comment yet. Actually this is not a one-off solution. My apologies I should have provided more information. This is actually to be incorporated into SAS Data Integration, and then this will be run as a batch job. So for every month, the job will be triggered and backup the golden copy to SIT2. Would export/import or network_link do the job?

Before exporing/importing, I have another job, which is truncating the tables in SIT2 so DEV data can be exported to SIT2.

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2. Below is the syntax that I have:

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA')     
      loop      
      begin     
           execute immediate 'insert into ' || r.table_name || '@DBLINKSIT2 SELECT * FROM AAA.' ||r.table_name ; 
         commit;
      end;
      end loop;     
    end;

I think I would just edit here since I cannot comment yet.

Actually this is not a one-off solution. My apologies I should have provided more information.

This is actually to be incorporated into SAS Data Integration, and then this will be run as a batch job. So for every month, the job will be triggered and backup the golden copy to SIT2. Would export/import or network_link do the job?

Before exporting/importing, I have another job, which is truncating the tables in SIT2 so DEV data can be exported to SIT2.

2 Extra Information
source | link

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2. Below is the syntax that I have:

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA')     
      loop      
      begin     
           execute immediate 'insert into ' || r.table_name || '@DBLINKSIT2 SELECT * FROM AAA.' ||r.table_name ; 
         commit;
      end;
      end loop;     
    end;

However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist.

Another problem thatthink I hadwould just edit here since I cannot comment yet. Actually this is the different table structuresnot a one-off solution. My apologies I copied manually the SQL body from the tables in DEV, then pasted the syntaxshould have provided more information. This is actually to be incorporated into SIT2SAS Data Integration, and then this will be run to create the same tables in SIT2as a batch job.

  So how I can detect IF the table does not exist in SIT2for every month, copy the SQL structure in DEV,job will be triggered and insert into SIT2? As far as I knowbackup the CREATE, INSERT INTO does notgolden copy to SIT2. Would export/import or network_link do the SQL structurejob?

Also how canBefore exporing/importing, I detect if there is different table structure (existing table in DEV)have another job, e.g. one more columnwhich is added, then appendtruncating the extra column into existing tabletables in SIT2?

I am relatively new to Oracle SQL Developer, so any advice or help wouldDEV data can be much appreciated. Thank you very muchexported to SIT2.

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2. Below is the syntax that I have:

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA')     
      loop      
      begin     
           execute immediate 'insert into ' || r.table_name || '@DBLINKSIT2 SELECT * FROM AAA.' ||r.table_name ; 
         commit;
      end;
      end loop;     
    end;

However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist.

Another problem that I had is the different table structures. I copied manually the SQL body from the tables in DEV, then pasted the syntax into SIT2, and run to create the same tables in SIT2.

  So how I can detect IF the table does not exist in SIT2, copy the SQL structure in DEV, and insert into SIT2? As far as I know the CREATE, INSERT INTO does not copy the SQL structure?

Also how can I detect if there is different table structure (existing table in DEV), e.g. one more column is added, then append the extra column into existing table in SIT2?

I am relatively new to Oracle SQL Developer, so any advice or help would be much appreciated. Thank you very much.

Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2. Below is the syntax that I have:

    begin       
    for r in (select DISTINCT TABLE_NAME  from all_tab_columns where owner = 'AAA')     
      loop      
      begin     
           execute immediate 'insert into ' || r.table_name || '@DBLINKSIT2 SELECT * FROM AAA.' ||r.table_name ; 
         commit;
      end;
      end loop;     
    end;

I think I would just edit here since I cannot comment yet. Actually this is not a one-off solution. My apologies I should have provided more information. This is actually to be incorporated into SAS Data Integration, and then this will be run as a batch job. So for every month, the job will be triggered and backup the golden copy to SIT2. Would export/import or network_link do the job?

Before exporing/importing, I have another job, which is truncating the tables in SIT2 so DEV data can be exported to SIT2.

1
source | link