oracle procedure looping a string and insert into another table

Question

I have this string : RC1500ZQ10RC2400ZQ20RC23ZQ3RC2322ZQ22

I need to create a procedure or trigger to split the above string and then insert it as a rows to another tables . 

Like This :

RC = NEW ROW . 
ZQ = NEW COULMN .
Row 1 RC1500ZQ10 = 1500,10 
Row 2 RC2400ZQ20 = 2400,20
Row 3 RC23  ZQ3  = 23,3
Row 4 RC2322ZQ22 = 2322,22

and so on .. 

Can anyone help on this ?

Answer 1

Any specific reason why you need procedure or Trigger for this?.

Use REGEXP_SUBSTR with CONNECT BY in a single SQL. You may include the query in your procedure to perform insert by passing the string argument.

'RC(.+?)ZQ' - match anything between RC and next ZQ

'ZQ(.+?)(RC|$)' - match anything between ZQ and next RC or line end

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table t as 
select 'RC1500ZQ10RC2400ZQ20RC23ZQ3RC2322ZQ22' as s from dual;

Query 1:

--INSERT INTO yourtarget(rc,zq) 
SELECT REGEXP_SUBSTR(s, 'RC(.+?)ZQ', 1, LEVEL, NULL, 1)     AS RC,
       REGEXP_SUBSTR(s, 'ZQ(.+?)(RC|$)', 1, LEVEL, NULL, 1) AS ZQ
FROM   t --or DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(s, 'RC(.+?)ZQ')

Results:

|   RC | ZQ |
|------|----|
| 1500 | 10 |
| 2400 | 20 |
|   23 |  3 |
| 2322 | 22 |