Skip to Main Content
  • Questions

  • Parsing the CLOB data seperated by delimiters

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Umesh.

Asked: September 07, 2018 - 8:50 pm UTC

Last updated: September 12, 2018 - 7:39 am UTC

Version: Oracle Database 11g Enterprise 11.2.0.4.0 - 64bit

Viewed 10K+ times! This question is

You Asked 

CREATE TABLE T
(
DT_FIELD DATE,
SKEY NUMBER,
FIELD_VALUES CLOB
)
INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}');
INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}');
INSERT INTO T VALUES('07-SEP-18',12,'101|} 88028|}YYY|}101030|}');
INSERT INTO T VALUES('08-SEP-18',15,'102|} 88029 |}XXX|}101040|}');
INSERT INTO T VALUES('09-SEP-18',20,'501|} 88030 |}XYZ|}101050|}BBB|}111|}');
commit;


Data in T table and the FIELD_VALUES field is seperated by delimiter '|}' and field_values column can contain more than one value separated by delimiter
This needs to be done for the Entire data daily and automated

DT_FIELD  SKEY   FIELD_VALUES
06-SEP-18  10  68|} 88026 |}ABC|}101010|}ADSD|}
07-SEP-18  11  70|} 88027 |}DEF|}
07-SEP-18  12  101|} 88028 |}YYY|}101030|}
08-SEP-18  15  102|} 88029 |}XXX|}101040|}
09-SEP-18  20  501|} 88030 |}XYZ|}101050|}BBB|}111|}


Expected Output from PL/SQL 

DT_FIELD  SKEY  FIELD_1  FIELD_2  FIELD_3  FIELD_4  FIELD 5  FIELD6
06-SEP-18   10 68   88026   ABC   101010  ADSD
07-SEP-18  11 70    88027   DEF  
07-SEP-18  12 101    88028  YYY  101030
08-SEP-18  15 102    88029  XXX  101040
09-SEP-18  20 501    88030  XYZ  101050 BBB  111

and Connor said...

Thanks for the test script. If you have an upper limit on how many fields you expect to get (I've used 6 below) then a pivot should do the trick

SQL> CREATE TABLE T
  2  (
  3  DT_FIELD DATE,
  4  SKEY NUMBER,
  5  FIELD_VALUES CLOB
  6  );

Table created.

SQL> INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}');

1 row created.

SQL> INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}');

1 row created.

SQL> INSERT INTO T VALUES('07-SEP-18',12,'101|} 88028|}YYY|}101030|}');

1 row created.

SQL> INSERT INTO T VALUES('08-SEP-18',15,'102|} 88029 |}XXX|}101040|}');

1 row created.

SQL> INSERT INTO T VALUES('09-SEP-18',20,'501|} 88030 |}XYZ|}101050|}BBB|}111|}');

1 row created.

SQL> commit;

Commit complete.

SQL> with
  2  fields_to_rows  as
  3  (
  4  select
  5        t.DT_FIELD,
  6        t.SKEY,
  7        c1.column_value as key,
  8        cast(regexp_substr(t.FIELD_VALUES, '[^\|\}]+', 1, c1.column_value) as varchar2(40)) as fname
  9      from
 10        t,
 11        table(cast(multiset(select level from dual connect by  level <= 0.5*length (regexp_replace(t.FIELD_VALUES, '[^\|\}]+'))) as sys.OdciNumberList)) c1
 12  )
 13  select * from fields_to_rows
 14  pivot ( max(fname) as colval for ( key ) in (1 as F1, 2 as F2, 3 as F3, 4 as F4, 5 as F5, 6 as F6 ));

DT_FIELD              SKEY F1_COLVAL    F2_COLVAL    F3_COLVAL    F4_COLVAL    F5_COLVAL    F6_COLVAL
--------------- ---------- ------------ ------------ ------------ ------------ ------------ ------------
08-SEP-18               15 102           88029       XXX          101040
06-SEP-18               10 68            88026       ABC          101010       ADSD
07-SEP-18               12 101           88028       YYY          101030
07-SEP-18               11 70            88027       DEF
09-SEP-18               20 501           88030       XYZ          101050       BBB          111

5 rows selected.

SQL>
SQL>
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Upper limit of 400 fields

Umesh, September 12, 2018 - 10:53 am UTC

Upper limit is for 400 fields

on dynamic pivots

A reader, September 12, 2018 - 3:31 pm UTC

Just grab the code from Stew Ashton on generic pivot and have them used it here.
https://stewashton.wordpress.com/2018/05/28/generic-pivot-function/

Then it goes like this.

demo@ORA12C> set feedback off
demo@ORA12C> drop table t purge;
demo@ORA12C> drop table gtt purge;
demo@ORA12C> CREATE TABLE T
  2     (
  3     DT_FIELD DATE,
  4     SKEY NUMBER,
  5     FIELD_VALUES clob
  6     );
demo@ORA12C> INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}');
demo@ORA12C> INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}');
demo@ORA12C> INSERT INTO T VALUES('07-SEP-18',12,'101|} 88028|}YYY|}101030|}');
demo@ORA12C> INSERT INTO T VALUES('08-SEP-18',15,'102|} 88029 |}XXX|}101040|}');
demo@ORA12C> INSERT INTO T VALUES('09-SEP-18',20,'501|} 88030 |}XYZ|}101050|}BBB|}111|}');
demo@ORA12C> COMMIT;
demo@ORA12C> create global temporary table gtt(dt_field date,skey int,x1 number,x2 varchar2(30) )
  2  on commit preserve rows;
demo@ORA12C> set feedback on
demo@ORA12C>
demo@ORA12C> insert into gtt(dt_field,skey,x1,x2)
  2  SELECT dt_field,
  3      skey,
  4      column_value x1,
  5      trim(regexp_substr( field_values, '[^|}]+',1,column_value)) x2
  6    FROM t,
  7      TABLE( CAST( multiset
  8      (SELECT level
  9      FROM dual
 10        CONNECT BY level <= TRUNC((LENGTH( field_values ) - LENGTH( REPLACE(field_values,'|}')))/2)
 11      ) as sys.odcinumberlist) );

22 rows created.

demo@ORA12C> variable x refcursor
demo@ORA12C> exec :x := generic_pivot('( select dt_field,skey,x1,x2 from gtt order by dt_field,x1)','x1','max(x2)');

PL/SQL procedure successfully completed.

demo@ORA12C> print x

DT_FIELD          SKEY 1          2          3          4          5          6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
06-SEP-0018         10 68         88026      ABC        101010     ADSD
07-SEP-0018         11 70         88027      DEF
07-SEP-0018         12 101        88028      YYY        101030
08-SEP-0018         15 102        88029      XXX        101040
09-SEP-0018         20 501        88030      XYZ        101050     BBB        111

5 rows selected.

demo@ORA12C>

on dynamic pivots

Rajeshwaran, Jeyabal, September 12, 2018 - 3:31 pm UTC

Just grab the code from Stew Ashton on generic pivot and have them used it here.
https://stewashton.wordpress.com/2018/05/28/generic-pivot-function/

Then it goes like this.

demo@ORA12C> set feedback off
demo@ORA12C> drop table t purge;
demo@ORA12C> drop table gtt purge;
demo@ORA12C> CREATE TABLE T
  2     (
  3     DT_FIELD DATE,
  4     SKEY NUMBER,
  5     FIELD_VALUES clob
  6     );
demo@ORA12C> INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}');
demo@ORA12C> INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}');
demo@ORA12C> INSERT INTO T VALUES('07-SEP-18',12,'101|} 88028|}YYY|}101030|}');
demo@ORA12C> INSERT INTO T VALUES('08-SEP-18',15,'102|} 88029 |}XXX|}101040|}');
demo@ORA12C> INSERT INTO T VALUES('09-SEP-18',20,'501|} 88030 |}XYZ|}101050|}BBB|}111|}');
demo@ORA12C> COMMIT;
demo@ORA12C> create global temporary table gtt(dt_field date,skey int,x1 number,x2 varchar2(30) )
  2  on commit preserve rows;
demo@ORA12C> set feedback on
demo@ORA12C>
demo@ORA12C> insert into gtt(dt_field,skey,x1,x2)
  2  SELECT dt_field,
  3      skey,
  4      column_value x1,
  5      trim(regexp_substr( field_values, '[^|}]+',1,column_value)) x2
  6    FROM t,
  7      TABLE( CAST( multiset
  8      (SELECT level
  9      FROM dual
 10        CONNECT BY level <= TRUNC((LENGTH( field_values ) - LENGTH( REPLACE(field_values,'|}')))/2)
 11      ) as sys.odcinumberlist) );

22 rows created.

demo@ORA12C> variable x refcursor
demo@ORA12C> exec :x := generic_pivot('( select dt_field,skey,x1,x2 from gtt order by dt_field,x1)','x1','max(x2)');

PL/SQL procedure successfully completed.

demo@ORA12C> print x

DT_FIELD          SKEY 1          2          3          4          5          6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
06-SEP-0018         10 68         88026      ABC        101010     ADSD
07-SEP-0018         11 70         88027      DEF
07-SEP-0018         12 101        88028      YYY        101030
08-SEP-0018         15 102        88029      XXX        101040
09-SEP-0018         20 501        88030      XYZ        101050     BBB        111

5 rows selected.

demo@ORA12C>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.