How to declare %rowtype dynamically?

Question

Below is the sample code in which I have stored all the table names in one table (table_config) and trying to insert one record of every table into its temporary table and trying to get the particular rowid for further need.

So I need every table rowtype to make this work, something dynamic. Could you please help me with this?

DECLARE
l_row table_name%ROWTYPE;
l_rowid ROWID;
l_table_name all_tab_partitions.table_name%TYPE;
l_temp_table_name all_tab_partitions.table_name%TYPE;
BEGIN
 FOR tab IN
 (select table_name from
 Table_config)
   LOOP
    l_table_name:= tab.table_name;
    l_temp_table_name:= 'TEMP_'||l_table_name;
    SELECT * INTO l_row
    FROM l_table_name
    WHERE ROWNUM=1;
    INSERT INTO l_temp_table_name VALUES l_row
    RETURNING ROWID INTO l_rowid;
    COMMIT;
   END LOOP;
END; 

Thank you, Pradeep

Answer 1

Thank you guys for your response. Actually I was trying to implement partition exchange on interval partitioned tables. I achieved it by using Dynamic Sql now. Initially I was trying to implement it by using rowid which is ok when I hard coded for one table, but when I thought of configuring it and using it for multiple tables I got stuck at that %ROWTYPE.

In the below code I have hard coded table name in few places which can be modified as dynamic but the problem is how to get the %ROWTYPE for the every table we pass.

DECLARE
l_table_name        table_config.table_name%TYPE;
l_query_temp        VARCHAR2(1000);
l_part_table_name   all_tab_partitions.table_name%TYPE;
l_part_name         all_tab_partitions.partition_name%TYPE;
l_temp_table_name   all_tab_partitions.table_name%TYPE; 
l_row               test_archival%ROWTYPE;
l_rowid             ROWID;
l_arch_table_name   all_tab_partitions.table_name%TYPE; 
l_arch_part_name    VARCHAR2(30);  
l_query_arch        VARCHAR2(1000);
l_query_source      VARCHAR2(1000);
BEGIN
<<outer_loop>>
FOR tab IN
(SELECT table_name FROM
table_config)
LOOP  
    l_table_name:= tab.table_name;          
    <<inner_loop>>
    FOR part IN                      
            (SELECT table_name, partition_position, partition_name FROM
                (SELECT table_name, partition_position, partition_name, 
                DENSE_RANK() OVER (PARTITION BY table_name ORDER BY     partition_position DESC) AS RANK
                FROM all_tab_partitions
                WHERE table_name=l_table_name
                ) WHERE RANK NOT IN(1, 2) ORDER BY partition_position)
                LOOP                        
                    l_part_table_name:= part.table_name;
                    l_part_name:= part.partition_name; 
                    l_temp_table_name := 'TEMP_'||l_part_table_name;
                    l_arch_table_name := 'ARCH_'||l_part_table_name;

                    l_query_temp := 'ALTER TABLE '
                                    || l_part_table_name                                                              
                                    || ' EXCHANGE PARTITION ' 
                                    || l_part_name
                                    || ' WITH TABLE '
                                    || l_temp_table_name
                                    ||' INCLUDING INDEXES WITHOUT VALIDATION';
                    EXECUTE IMMEDIATE l_query_temp;
                    COMMIT;         

                    SELECT * INTO l_row FROM temp_test_archival WHERE ROWNUM = 1;

                    INSERT INTO arch_test_archival VALUES l_row RETURNING ROWID INTO l_rowid;     
                    COMMIT;

                    SELECT subobject_name 
                    INTO l_arch_part_name FROM user_objects    
                    WHERE data_object_id = dbms_rowid.rowid_object(l_rowid);

                    DELETE from arch_test_archival where rowid=l_rowid;
                    COMMIT;

                    l_query_arch := 'ALTER TABLE '
                    ||'ARCH_TEST_ARCHIVAL'
                    ||' EXCHANGE PARTITION '
                    ||l_arch_part_name         
                    ||' WITH TABLE '
                    ||'TEMP_TEST_ARCHIVAL'
                    ||' INCLUDING INDEXES WITHOUT VALIDATION';  
                    EXECUTE IMMEDIATE l_query_arch;
                END LOOP;
END LOOP;
END;
/

Answer 2

You should investigate EXECUTE IMMEDIATE INTO. I think this would be an excellent way to get the ROWID when combined with some dynamic SQL examples from above. Here's an example:

DECLARE
  DYN_SQL  VARCHAR(4000) := 'SELECT 1 FROM DUAL';
  INTO_VAR NUMBER(1);
BEGIN
  EXECUTE IMMEDIATE DYN_SQL INTO INTO_VAR;

  DBMS_OUTPUT.PUT_LINE(INTO_VAR);  
END;

Answer 3

Below is solution. What do you need this rowids for? I would be much simpler without it, as you cannot use returning with insert as select

DECLARE
    l_rowid ROWID;
    l_table_name all_tab_partitions.table_name%TYPE;
    l_temp_table_name all_tab_partitions.table_name%TYPE;
    v_sql1 varchar2(4000);
    v_sql2 varchar2(4000);
    BEGIN
     FOR tab IN (select table_name from Table_config) LOOP
        l_table_name:= tab.table_name;
        l_temp_table_name:= 'TEMP_'||l_table_name;
        v_sql1 := 'select rowid from ' || l_table_name || ' where rownum =1 for update';
        v_sql2 := 'insert into ' || l_temp_table_name  || ' select * from ' || l_table_name || ' where rownum = 1';
        execute immediate v_sql1 into l_rowid;
        execute immediate v_sql2;
        commit;
       END LOOP;
    END; 
    /

Answer 4

Without coding the complete answer for you. Why don't you do something like this?

FOR tab IN
(select table_name from
Table_config)

EXECUTE_IMMEDIATE(
'declare
l_row '||table_name||'%ROWTYPE;
begin
INSERT INTO '||l_temp_table_name
SELECT * FROM '||l_table_name||' WHERE ROWNUM=1;
end;');

EXECUTE_IMMEDIATE ('SELECT ROWID FROM '||l_table_name)
INTO l_rowid;

END LOOP;

• it assumes target table is empty to begin with with only one record inserted during the process.

Answer 5

You can't do that as already mentioned in the comment by OldProgrammer above.

You'll have to use Dynamic SQL to achieve what you're trying to achieve.

DECLARE
temp_table VARCHAR2(255);
source_table VARCHAR2(255);
sql_stmt VARCHAR2(255);
CURSOR c1 IS
SELECT table_name FROM user_Tables;
BEGIN
   FOR c1_Rec IN c1 LOOP
      temp_table := 'TEMP_'||c1_rec.table_name;
      source_table := c1_rec.table_name;
      sql_stmt := 'INSERT INTO '||temp_table||' SELECT * FROM '||source_table||' WHERE rownum = 1';
      EXECUTE IMMEDIATE sql_stmt;
   END LOOP;
END;
/