2 Replies Latest reply: Jul 3, 2013 8:12 AM by seggione RSS

    Transfer values from ROWTYPE into ROWTYPE with different COLUMN-Specs. by keeping Default_Data-Values

    seggione

      Hi,

       

      i have an issue about dynamically transfer values from different columns in two given variables of rowtype-type.

       

      My intention is to transfer the values from table1 to table2.

      Basically i've declared to vars of v_table1 and v_table2 % rowtype, each of them contains columns with the same and also individual column names.

      v_table2 % rowtype should be initialized with the default values.

       

      I would like to insert values of v_table2 into table2 later by doing "INSERT INTO TABLE2 VALUES v_table2".

      Before inserting i need to modify several column values by different rules, but those rules aren't relevant for my actual request.

       

      The point is, how can i transfer the values of v_table1 into v_table2 for those columns hanving the same column-name, without hard coding the column names because those two tables contain a lot of columns.

       

      Furthermore values in v_table2 initialized with default_data values shouldn't be overwritten by non-existing data in the same named column of v_table1.

      Example: If v_table1.D is NULL it shouldn't overwrite the former initialized default value 'Monday'.

       

      Conditions:

       

      Table1 contains the following values:

       

      Column NameValue
      A'1'
      B'2'
      CNULL
      D'somedata'
      ENULL
      F'3'

       

      After transferring the values of v_table1 into v_table2 i assume the following data:

       

      Column NameValueSource
      CNULLTable1.C
      D'somedata'Table1.D
      E'Tuesday'DATA_DEFAULT of Table2
      F'3'Table1.F
      GNULL
      HNULL

       

      Right now i'm already able to fill v_table2 with the default values:

       

      declare
        lv_query varchar2(1000);
        
        type cur_typ is ref cursor;
        rcur_typ cur_typ;
        
        v_table2 table2%rowtype;
      
      begin
          for rec in (select *
                      from user_tab_columns
                      where table_name = 'TABLE2'
                      order by column_id
                      )
          loop
            if rec.data_default is null then
                lv_query := lv_query||', null';
              elsif rec.data_type = 'NUMBER' then
                lv_query := lv_query||', '||rec.data_default;
              elsif rec.data_type = 'DATE' then
                lv_query := lv_query||', '||rec.data_default;
              else
                lv_query := lv_query||', '||rec.data_default||'';
            end if;
          end loop;
          
          lv_query := 'SELECT'||substr(lv_query, 2)||' FROM DUAL';
          dbms_output.put_line(lv_query);
          
          open rcur_typ for lv_query;
          fetch rcur_typ into v_table2;
          close rcur_typ;
      end;
      

       

       

      I have no idea how to achieve the transfer from v_table1 to v_table2 without overriding the default values of table2.


      As far as i know i can find out those columns with matching columns names by querying:

       

      select column_name from user_tab_cols where table_name='TABLE1' intersect select column_name from user_tab_cols where table_name='TABLE2'
      

       

      Maybe you can give me some advices.

       

      Thank you very much.

       

      Sample data:

       

      CREATE TABLE "TABLE1" 
         (    "A" VARCHAR2(20 BYTE), 
          "B" VARCHAR2(20 BYTE), 
          "C" VARCHAR2(20 BYTE), 
          "D" VARCHAR2(20 BYTE), 
          "E" VARCHAR2(20 BYTE), 
          "F" VARCHAR2(20 BYTE)
         );
      
        CREATE TABLE "TABLE2" 
         (    "C" VARCHAR2(20 BYTE), 
          "D" VARCHAR2(20 BYTE) DEFAULT 'Monday', 
          "E" VARCHAR2(20 BYTE) DEFAULT 'Tuesday', 
          "F" VARCHAR2(20 BYTE) DEFAULT 'Wednesday', 
          "G" VARCHAR2(20 BYTE) DEFAULT 'Thursday', 
          "H" VARCHAR2(20 BYTE)
         );
      
      INSERT INTO "TABLE1" (A, B, D, F) VALUES ('1', '2', 'somedata', '3');
      
      INSERT INTO "TABLE1" (A, B, D, F) VALUES ('1', '2', 'somedata', '3');
      

       

       

      Banner:

      Oracle Database 11g Release 11.2.0.3.0 - 64bit Production                       

      PL/SQL Release 11.2.0.3.0 - Production                                          

      CORE    11.2.0.3.0    Production                                                        

      TNS for Linux: Version 11.2.0.3.0 - Production                                  

      NLSRTL Version 11.2.0.3.0 - Production  

        • 1. Re: Transfer values from ROWTYPE into ROWTYPE with different COLUMN-Specs. by keeping Default_Data-Values
          kordirko

          You can build dynamic query, that checks values of all columns in table1,

          and if some column contains null, then retrieves default value of corresponding column of table2,

          or null if that column does not have default vallue.

           

          Something like this pseudocode

           

          SELECT
             if column A is not null then take A from table1, else take default for column A from table2
             .....
             if column X is not null then take A from table1, else take default for column X from table2
          FROM table1
          

           

          The default valule can be retrieved from USER_TAB_COLUMNS

           

          select column_name, data_default from user_tab_columns
          where table_name = 'TABLE2'
          order by column_id
          ;
          
          
          COLUMN_NAME     DATA_DEFAULT                       
          ------------------------- ---------------------------
          C                    
          D                                   'Monday'                             
          E                                   'Tuesday'                            
          F                                   'Wednesday'                          
          G                                   'Thursday'                           
          H                    
          
           6 rows selected    
          

           

          Unfortunately, the type of DATA_DEFAULT column is LONG, and it can't be converted to char or varchar2

          in plain sql, to do this we need small helper function:

           

          create or replace function get_col_default(
            p_table_name  in  user_tab_columns.table_name%type,
            p_column_name  in  user_tab_columns.column_name%type
          )
          return varchar2
          as
            l_data_default LONG;
          begin
            select data_default into l_data_default
            from  user_tab_columns u
            where u.table_name = p_table_name
            and u.column_name = p_column_name;
            
            return substr( l_data_default, 1, 4000 );
          end;
          /
          

           

          Using that function we can get all columns from table1 and default values of corresponding columns in table2

           

              select  t2.column_name col_t2, 
                      t1.column_name col_t1, 
                      get_col_default('TABLE2',t2.column_name) col_t2_default
              from (
                select * from user_tab_columns where table_name = 'TABLE2' 
              ) t2
              left join( 
                  select * from user_tab_columns where table_name = 'TABLE1' 
              ) t1 on ( t1.column_name = t2.column_name ) 
              order by t2.column_id
          

              ;

           

           

          COL_T2                         COL_T1                         COL_T2_DEFAULT                            
          ------------------------------ ------------------------------ ----------------------
          C                                       C                           
          D                                        D                                   'Monday'    
          E                                        E                                  'Tuesday'   
          F                                        F                                   'Wednesday' 
          G                                                                             'Thursday'  
          H              
          
           6 rows selected 
          

           

           

          and then we can build dynamic query that transfers rows from T1 to T2

           

           

          WITH cols AS (
              select  t2.column_name col_t2, 
                      t1.column_name col_t1, 
                      get_col_default('TABLE2',t2.column_name) col_t2_default
              from (
                select * from user_tab_columns where table_name = 'TABLE2' 
              ) t2
              left join( 
                  select * from user_tab_columns where table_name = 'TABLE1' 
              ) t1 on ( t1.column_name = t2.column_name ) 
              order by t2.column_id
          ),
          qry AS (
            -- INSERT INTO table2 ( list of columns ...... )
            SELECT 'INSERT INTO table2(' clause from dual
            union all
            SELECT  '    ' || case when rownum > 1 then ',' end || ' ' || col_t2
            FROM cols
            union all
            select ') ' from dual
            union all
            --  SELECT ..(list of values)... FROM table1
            SELECT 'SELECT ' sql from dual 
            union all
            SELECT  '    ' || case when rownum > 1 then ',' end ||
                     NVL2( col_t2_default, 'nvl('|| nvl( col_t1, 'NULL')||','||col_t2_default||')' , nvl( col_t1, 'NULL')  ) || ' as '||col_t2
            FROM cols
            union all
            SELECT 'FROM table1' from dual
          )
          SELECT listagg( clause , chr(13)) within group( order by rownum) sql
          from qry
          
          ;
          

           

          The above query produces a query that transfers table1 to table2, it can be executed using EXECUTE IMMEDIATE.

           

          INSERT INTO table2(
               C
              , D
              , E
              , F
              , G
              , H
          ) 
          SELECT 
              C as C
              ,nvl(D,'Monday') as D
              ,nvl(E,'Tuesday') as E
              ,nvl(F,'Wednesday') as F
              ,nvl(NULL,'Thursday') as G
              ,NULL as H
          FROM table1 
          

           

          If you want to manipulate values before inserting them to table2,

          just skip INSERT INTO block from this query.

          PL/SQL routine that manipulates records could look like this:

           

          declare
            TYPE tab2curtyp IS REF CURSOR;
            tab2cur tab2curtyp;
            v_sql varchar2(32767);
            t2_rec  table2%rowtype;
          begin
              WITH cols AS (
                  select  t2.column_name col_t2, 
                          t1.column_name col_t1, 
                          get_col_default('TABLE2',t2.column_name) col_t2_default
                  from (
                    select * from user_tab_columns where table_name = 'TABLE2' 
                  ) t2
                  left join( 
                      select * from user_tab_columns where table_name = 'TABLE1' 
                  ) t1 on ( t1.column_name = t2.column_name ) 
                  order by t2.column_id
              ),
              qry AS (
                --  SELECT ..(list of values)... FROM table1
                SELECT 'SELECT ' clause from dual 
                union all
                SELECT  '    ' || case when rownum > 1 then ',' end ||
                         NVL2( col_t2_default, 'nvl('|| nvl( col_t1, 'NULL')||','||col_t2_default||')' , nvl( col_t1, 'NULL')  ) || ' as '||col_t2
                FROM cols
                union all
                SELECT 'FROM table1' from dual
              )
              SELECT listagg( clause , chr(13)) within group( order by rownum) sql
              into v_sql
              from qry
              ;
              
              open tab2cur for v_sql;
              LOOP
                  FETCH tab2cur INTO t2_rec;
                  EXIT WHEN tab2cur%NOTFOUND;
                  
                  -- apply your rules here and modify column values
                  
                  INSERT INTO table2 VALUES t2_rec;
              END LOOP;
          
            CLOSE tab2cur;
          end;
          /
          
          • 2. Re: Transfer values from ROWTYPE into ROWTYPE with different COLUMN-Specs. by keeping Default_Data-Values
            seggione

            This works like a charme! Thank you very much!

             

            Let's see what happens if i'm having different DATA_TYPES in those tables; not only VARCHAR2...