DECLARE BEGIN Insert into XXTEST1(name,value) with TEST as ( select 'BIND_TYPE=LAY_FLAT|BIND_WIDTH =SFLY_WIDE|COVER_FINISH=MATTE|COVER_TYPE=HARD_PHOTO|FORMFACTOR=10X10|ORIENTATION=LANDSCAPE|PAGES_FINISH=NONE|PAGES_TYPE=STANDARD|PAGE_COUNT=Set Page Quantity|BIND_WIDTH=SFLY_DEFAULT|DUST_JACKET_FINISH=MATTE|INNER BOX=8X8|' str from dual) select REGEXP_SUBSTR(STR,'[^|=]+',1,level * 2 -1 ) Name, REGEXP_SUBSTR(STR,'[^|=]+',1,level * 2) value from test connect by level <= REGEXP_COUNT(STR,'=') ; END;
/--Change this program into procedure
ReplyDeleteDECLARE
BEGIN
Insert into XXTEST1(name,value)
with TEST as (
select 'BIND_TYPE=LAY_FLAT|BIND_WIDTH =SFLY_WIDE|COVER_FINISH=MATTE|COVER_TYPE=HARD_PHOTO|FORMFACTOR=10X10|ORIENTATION=LANDSCAPE|PAGES_FINISH=NONE|PAGES_TYPE=STANDARD|PAGE_COUNT=Set Page Quantity|BIND_WIDTH=SFLY_DEFAULT|DUST_JACKET_FINISH=MATTE|INNER BOX=8X8|' str from dual)
select REGEXP_SUBSTR(STR,'[^|=]+',1,level * 2 -1 ) Name,
REGEXP_SUBSTR(STR,'[^|=]+',1,level * 2) value
from test
connect by level <= REGEXP_COUNT(STR,'=') ;
END;
SELECT * FROM XXTEST1;