How To Insert Into Temp Table When Looping Through A String - Oracle - Pl/sql

CREATE GLOBAL TEMPORARY TABLE tt_temptable( RowNums NUMBER(3,0), procNums NUMBER(18,0) ) ON COMMIT PRESERVE ROWS; inputString VARCHAR2 ; inputString := '12,13,14,

Solution 1:

If you use Oracle 12c, then you may define an IDENTITY column through GENERATED ALWAYS AS IDENTITY in your table definition and follow the way below :

SQL>CREATEGLOBAL TEMPORARY TABLE tt_temptable(
  2        RowNums NUMBER(3,0) GENERATED ALWAYS ASIDENTITY,
  3        procNums  NUMBER(18,0)
  4    ) ONCOMMIT PRESERVE ROWS;

Table created

SQL>SQL>DECLARE2    inputString  VARCHAR2(50) :='12,13,14,15';
  3BEGIN4INSERTINTO tt_temptable(procNums)
  5SELECT REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) ProcNums
  6FROM dual
  7CONNECTBY  REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) ISNOTNULL;
  8END;
  9/

PL/SQLprocedure successfully completed

SQL>SELECT*FROM tt_temptable;

ROWNUMS            PROCNUMS
------- -------------------112213314415

To reset the IDENTITY column (RowNums), use :

SQL>ALTERTABLE tt_temptable MODIFY( RowNums Generated asIdentity (STARTWITH1));

whenever the shared locks on the table are released.

Solution 2:

insertinto tt_temptable 
select NVL((selectmax(a.rownums) 
                from tt_temptable a
              ),100)+rownum
         ,procNums 
  from (SELECT REGEXP_SUBSTR ('10,20,30','[^,]+',1,LEVEL) ProcNums,level as lvl
          FROM dual 
       CONNECTBY  REGEXP_SUBSTR ('10,20,30','[^,]+',1,LEVEL) ISNOTNULL
       )x