Myujth

CREATE GLOBAL TEMPORARY TABLE tt_temptable(
 RowNums NUMBER(3,0),
 procNums NUMBER(18,0)
) ON COMMIT PRESERVE ROWS;

 inputString VARCHAR2 ;

 inputString := '12,13,14,15'


SELECT REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) ProcNums
FROM dual CONNECT BY REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) IS NOT NULL;

 INSERT INTO tt_temptable( 
 SELECT identity(3) RowNums,procNums
 FROM 

);

Want to insert 12 , 13, 14 , 15 and identity of 3 length in the temptable so total 4 rows in temptable

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> CREATE GLOBAL TEMPORARY TABLE tt_temptable(
 2 RowNums NUMBER(3,0) GENERATED ALWAYS AS IDENTITY,
 3 procNums NUMBER(18,0)
 4 ) ON COMMIT PRESERVE ROWS;

Table created

SQL> 
SQL> DECLARE
 2 inputString VARCHAR2(50) := '12,13,14,15';
 3 BEGIN
 4 INSERT INTO tt_temptable(procNums)
 5 SELECT REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) ProcNums
 6 FROM dual
 7 CONNECT BY REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) IS NOT NULL;
 8 END;
 9 /

PL/SQL procedure successfully completed

SQL> SELECT * FROM tt_temptable;

ROWNUMS PROCNUMS
------- -------------------
 1 12
 2 13
 3 14
 4 15

To reset the IDENTITY column (RowNums), use :

SQL> ALTER TABLE tt_temptable MODIFY( RowNums Generated as Identity (START WITH 1));

whenever the shared locks on the table are released.

insert 
 into tt_temptable 
select NVL((select max(a.rownums) 
 from tt_temptable a
 ),100)+rownum
 ,procNums 
 from (SELECT REGEXP_SUBSTR ('10,20,30','[^,]+',1,LEVEL) ProcNums,level as lvl
 FROM dual 
 CONNECT BY REGEXP_SUBSTR ('10,20,30','[^,]+',1,LEVEL) IS NOT NULL
 )x