CREATE OR REPLACE TYPE typ_rowid IS record (
            TABLE_NAME VARCHAR2(100),
            COL_VALUE NUMBER(38),
            ROW_ID VARCHAR2(100)
            );

           

            CREATE OR REPLACE TYPE t_typ_rowid IS TABLE OF TYP_rowid;

 

Can you please help to understand this?

How to used Oracle types in below case instead of PL/SQL type?

           

create table tb_load_stats
(
TABLE_NAME    VARCHAR2(30 CHAR),
COL_VALUE     NUMBER(38),
ROW_ID        VARCHAR2(30 CHAR)
)
/

 

DECLARE
            TYPE typ_rowid IS record (
            TABLE_NAME VARCHAR2(100),
            COL_VALUE NUMBER(38),
            ROW_ID VARCHAR2(100)
            );
          

            TYPE t_typ_rowid IS TABLE OF TYP_rowid;
         
            tb_col_rowid           t_typ_rowid := t_typ_rowid();
 
            TYPE r_cursor IS REF CURSOR;
            c_cur_var r_cursor;
BEGIN
     OPEN c_cur_var FOR SELECT /*+ PARALLEL(ORDER_HISTORY, 2) */ 'ORDER_HISTORY', ORDER_ID, ROWID FROM ORDER_HISTORY WHERE posting_dt <= TO_DATE('01/01/2008', 'MM/DD/YYYY');

     LOOP

         FETCH c_cur_var BULK COLLECT INTO tb_col_rowid LIMIT 5000;
                       EXIT WHEN tb_col_rowid.COUNT = 0;
                        FORALL i IN tb_col_rowid.FIRST .. tb_col_rowid.LAST
             INSERT INTO tb_load_stats VALUES (tb_col_rowid(i).table_name, tb_col_rowid(i).col_value, tb_col_rowid(i).row_id);
    
                         tb_col_rowid.DELETE;
     END LOOP;
     CLOSE c_cur_var;
END;

Well... for starters

SQL>  CREATE OR REPLACE TYPE typ_rowid IS record (
  2              TABLE_NAME VARCHAR2(100),
  3              COL_VALUE NUMBER(38),
  4              ROW_ID VARCHAR2(100)
  5              );
  6  /

Warning: Type created with compilation errors.

Once we fix that, we dont need to repeat the definitions in the plsql code, we simply define variables to hold the data.

Also, because our nested table is a table of "typ_rowid" objects, our cursor must fetch a list of those *objects*. So we put the constructor around the individual colums values.

SQL>  CREATE OR REPLACE TYPE typ_rowid IS object (
  2              TABLE_NAME VARCHAR2(100),
  3              COL_VALUE NUMBER(38),
  4              ROW_ID VARCHAR2(100)
  5              );
  6  /

Type created.

SQL>
SQL>
SQL>             CREATE OR REPLACE TYPE t_typ_rowid IS TABLE OF TYP_rowid;
  2  /

Type created.

SQL>
SQL> create table tb_load_stats
  2  (
  3  TABLE_NAME    VARCHAR2(30 CHAR),
  4  COL_VALUE     NUMBER(38),
  5  ROW_ID        VARCHAR2(30 CHAR)
  6  )
  7  /

Table created.

SQL> DECLARE
  2              tb_col_rowid           t_typ_rowid := t_typ_rowid();
  3
  4              TYPE r_cursor IS REF CURSOR;
  5              c_cur_var r_cursor;
  6  BEGIN
  7       OPEN c_cur_var FOR SELECT typ_rowid('ORDER_HISTORY', object_id, ROWID) FROM t WHERE rownum <= 100;
  8
  9       LOOP
 10
 11           FETCH c_cur_var BULK COLLECT INTO tb_col_rowid LIMIT 5000;
 12                         EXIT WHEN tb_col_rowid.COUNT = 0;
 13                          FORALL i IN tb_col_rowid.FIRST .. tb_col_rowid.LAST
 14               INSERT INTO tb_load_stats VALUES (tb_col_rowid(i).table_name, tb_col_rowid(i).col_value, tb_col_rowid(i).row_id);
 15
 16                           tb_col_rowid.DELETE;
 17       END LOOP;
 18       CLOSE c_cur_var;
 19  END;
 20  /

PL/SQL procedure successfully completed.