Help us understand the problem. What is going on with this article?

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていく話

More than 1 year has passed since last update.

まえがき

タイトルどおり不毛な遊びです。4000バイト越えのカラム文字に遭遇したらどうしようとかなぞの思いつきをしたために、春分の日が終わった話です。

以下の記事で紹介されているファンクションのほうが実用性はあると思います。

Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法

参考文献

DBMS_LOB.LOADCLOBFROMFILEプロシージャを使ってテキストファイルを Oracle Database の CLOB型列 に ロードする。

CLOB型の列 を 1行だけ取得する リテラルSQL を 引数にして、その CLOB型のデータを 2000文字ずつ分解して 複数レコードとして返却する Table Function を作成

事前準備

ディレクトリ作成し、4000バイト越えのテキストファイルをおく。sysユーザーで権限付与。

CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

実行例

[oracle@f285aba0589a ~]$ cd /mnt/18cr3/share/dump/ORCL/aine/in
[oracle@f285aba0589a in]$ ll
total 8
-rw-r--r--. 1 1000 1001 4199 Mar 21 18:31 clob.txt
[oracle@f285aba0589a in]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Mar 21 18:51:11 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';

Directory created.

Elapsed: 00:00:00.04
SYS@pdb1> GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;

Grant succeeded.

Elapsed: 00:00:00.03
SYS@pdb1> GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

Grant succeeded.

Elapsed: 00:00:00.02
SYS@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

取込テーブル作成

DROP TABLE test___clob___ PURGE;
CREATE TABLE test___clob___(rn number,clb CLOB);
SELECT rn,dbms_lob.getlength(clb) as len FROM test___clob___;

実行例

[oracle@f285aba0589a in]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Mar 21 19:01:17 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Thu Mar 21 2019 18:54:56 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> DROP TABLE test___clob___ PURGE;

Table dropped.

Elapsed: 00:00:00.04
AINE@pdb1> CREATE TABLE test___clob___(rn number,clb CLOB);

Table created.

Elapsed: 00:00:00.00
AINE@pdb1> SELECT rn,dbms_lob.getlength(clb) as len FROM test___clob___;

no rows selected

Elapsed: 00:00:00.01

プロシージャでファイルをテーブルに取り込む

そのまま参考文献のスクリプト活用させてもらいました。ありがとうございます。BFILENAMEコンストラクタの第一引数は大文字。

DECLARE
  bfile_p  BFILE;       -- BFILE pointer
  clob_loc CLOB;        -- CLOB locator
  n_c1     NUMBER;      -- test___clob___ Column C1
  n_doffs  NUMBER := 1; -- dest_offset
  n_soffs  NUMBER := 1; -- src_offset
  n_lctx   NUMBER := 0; -- lang_context
  n_warn   NUMBER;      -- warning
BEGIN
  -- BFILE pointer set
  bfile_p := BFILENAME('DP_IN_AINE_DIR', 'clob.txt');
  -- Insert record and CLOB locator get
  SELECT NVL(MAX(RN), 0) + 1 INTO n_c1 FROM test___clob___;
  INSERT INTO test___clob___ VALUES (n_c1, EMPTY_CLOB())
  RETURNING CLB INTO clob_loc;
  -- File open
  DBMS_LOB.FILEOPEN(bfile_p, DBMS_LOB.FILE_READONLY);
  -- LOAD CLOB from text file
  DBMS_LOB.LOADCLOBFROMFILE(
      clob_loc                   -- CLOB locator
    , bfile_p                    -- BFILE pointer
    , DBMS_LOB.LOBMAXSIZE        -- amount
    , n_doffs                    -- dest_offset
    , n_soffs                    -- src_offset
    , NLS_CHARSET_ID('AL32UTF8') -- BFILE Characterset ID
    , n_lctx                     -- lang_context
    , n_warn                     -- warning
  );
  COMMIT;
  -- File close
  DBMS_LOB.FILECLOSE(bfile_p);
END;
/

確認

SELECT * FROM test___clob___;

        RN CLB                                                                             
---------- --------------------------------------------------------------------------------
         1 TANPG,GRXBG,OOFDS,EWLPP,QFOCB,UJMKH,GTVLB,TQKGQ,GDRKT,ZSYVL,MPJYT,UVXBQ,OXRWR,QH

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていくパッケージファンクション

ちょろちょろ感が出てて楽しい。500の意味は500番目のカンマを発見した位置ってことで、だいたい500個ぐらいの要素読み込んで、コレクションに変換してちょろちょろ入れていく感じです。この数字をいじれば、ちょろちょろ感を調節できます。300ちょろちょろだったり、1000ちょろちょろだったり。build_sqlでは切り取ったカンマ要素をバインド変数で受け取り、動的sqlの実行時に代入します。このbuild_sqlがなかなか大変だったけど、楽しかった!!基本的なロジックの考え方は以下の記事を参考にしました。

Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法

CREATE OR REPLACE TYPE item IS OBJECT (val CLOB);
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
CREATE OR REPLACE PACKAGE item_liz
IS
    FUNCTION clbliz(p_clb IN CLOB) RETURN liz;
END;
/

CREATE OR REPLACE PACKAGE BODY item_liz
IS
    FUNCTION clbliz(p_clb IN CLOB) RETURN liz
    IS
        rt liz;
        tmpliz liz;
        tmpclb CLOB;
        build_sql CLOB;
        len INTEGER;
        oft INTEGER;
    BEGIN
        rt := liz(item(to_clob(' ')));
        len := dbms_lob.getlength(p_clb);
        oft := 1;
        build_sql := to_clob('WITH sub AS ( SELECT :tmpclb as tmpclb FROM dual ) ,rec ( rn  ,srt  ,nxt  ,len  ,ele  ,liz ) AS ( SELECT 1 AS rn  ,1 AS srt  ,1 AS nxt  ,instr(tmpclb ,'||''','''||' ,1 ,1) - 1 AS len  ,dbms_lob.getlength(tmpclb) - dbms_lob.getlength(REPLACE(tmpclb , ' || ''',''' || ','''') ) + 1 AS ele  ,liz(item(to_clob(dbms_lob.substr(tmpclb ,instr(tmpclb ,' || ''','''||' ,1 ,1) - 1 ,1) ) ) ) AS liz FROM sub UNION ALL SELECT s1.rn + 1 AS rn  ,instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) AS srt  ,instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) + 1 AS nxt  ,nvl(nullif(instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) - 1 AS len  ,s1.ele  ,s1.liz MULTISET UNION ALL liz(item(to_clob(dbms_lob.substr(s2.tmpclb ,nvl(nullif(instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) - 1 ,instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) + 1) ) ) ) AS liz FROM rec s1  ,sub s2 WHERE s1.rn + 1 <= s1.ele ) SELECT liz FROM ( SELECT s3.*  ,row_number() OVER( PARTITION BY s3.rn ORDER BY s3.tmpseq ) AS seq FROM ( SELECT s1.*  ,s2.*  ,ROWNUM AS tmpseq FROM rec s1  ,TABLE ( liz ) s2 ) s3 ) WHERE rn = ele AND ele = seq');
        LOOP
            tmpliz := liz(item(to_clob(' ')));
            tmpclb := to_clob(dbms_lob.substr(p_clb, nvl(nullif(instr(p_clb,',',1,500),0),500), oft));
            dbms_output.put_line(tmpclb);
            dbms_output.put_line(build_sql);
            EXECUTE IMMEDIATE build_sql INTO tmpliz USING IN tmpclb;
            rt := rt MULTISET UNION ALL tmpliz;
            oft := oft + nvl(nullif(instr(p_clb,',',1,500),0),500);
            EXIT WHEN oft > len;
        END LOOP;
        RETURN rt;
    END;
END;
/

テストデータ

4000 overはto_clob()かましても4000 overあーだーこーだいわれるので、上記手順で投入したテーブルより実施。

4000 below

WITH sub AS (SELECT LEVEL AS lv FROM dual CONNECT BY LEVEL <= 600)
SELECT RTRIM(XMLAGG(XMLELEMENT(E,dbms_random.STRING('U',5),',').EXTRACT('//text()')).getclobval(),',') AS liz FROM sub;

SELECT dbms_lob.getlength((to_clob(
'BFYYC,MFWWD,NGNQV,UHTTZ,DURYJ,PMDBU,UQJIU,ZVFYE,TKVME,XMONH,IIVME,KULCD,GANGL,HFJVU,YADOM,ISEMW,WUMWG,ZBETX,CBFLK,ESAVA,SLOQW,UROQR,GQFPA,MZOKE,FIKBG,JEWPH,IKPBN,TPDLW,QIVZM,DOFAV,JYJGC,YUTNM,CZZGS,UJVSM,JPWFD,SMZGG,ENUYO,LJKUA,FRDKD,ICFUF,PXUWB,MQVFI,EZHIK,QLIRN,VPMWW,MYZKX,XCXBD,GAKUU,UOJCJ,PIOSU,WXUGB,MKTWL,KNXDK,IFITF,KFWAP,EFUKP,HUXEE,YOIUZ,SLYRX,EWPKC,VTTPV,LCZBM,BSNTQ,PMLLF,HXTRW,IJSQA,GANEE,DGZDG,KQAIT,IBKDI,KDMET,RBDIL,MIZPQ,GVTTU,WXBLN,BGUCZ,QUWDE,ARPIN,OSIDW,BWWCM,DXIFU,XFTYW,RSXFO,QJREZ,TKMBX,SZYIP,NJSKE,LRBWD,XJHGS,DIRUB,AQGON,BQHDC,NSDRH,WDPHK,TGAMF,MNVSS,TZOOE,BLISI,NFUZU,EFKVZ,OCXUN,GCUJG,TKHSR,RUOAZ,EQUBK,NKFWB,LCXQM,HGRIO,IFTZA,EXOOU,EKBQB,YQIAK,HOWHW,GRNJA,GCTTZ,FVSQY,IOQYR,NANYN,RNFFV,FLLNJ,EDRIO,DPXIR,XYXQI,ZBXUV,TJBOF,BHFEM,OAVHH,WSFVA,FYMVD,ZMHCP,AFUOX,KEYNS,PDXEZ,EVPVD,ZGVGR,XINVH,QHKYA,MKAFU,PHJTF,EDHLZ,DMNYW,SCDCT,RVGYR,QPIEF,ZKXFX,FLZOC,WMAJF,NZMBC,FCVMS,GYYCJ,JCRIE,ARZXK,VYPCC,XIGKS,SVKZR,SQVEZ,OVBFL,BNRIC,YVLRE,IZESD,ZRUMJ,BUHEI,AGUYQ,GZCUL,AHBVJ,XDKEK,VPMYM,YXWNQ,KNYDB,OCLTZ,TZLUZ,JOXXN,LQXRH,MERVD,XKQSP,BVDHD,XWKPE,VYBVJ,MINLH,RADVX,SZVKR,JBHXG,MLUHD,QMWSC,WRAJO,FBQVL,ZOCCN,OFLXB,BLNPR,PZZHA,UWUWG,HEWAN,UAVAI,AAATA,RNHGS,NHZIW,GVMPI,FVHYO,BAMSC,KMOPK,QQNPH,MKBWO,DLRMC,CGQIH,OCAFT,TYUVI,YXQVL,NHWQE,UZSQA,ECSJK,SBRQQ,JKLJC,VIYNK,OYZJT,HBQBY,PHDYC,EQVMZ,OHBHU,QDFPS,FORQK,YJUZL,QRYTO,MPCFO,UXGPS,JPCPJ,ZGESO,AFNSC,PGMKT,DURJZ,EXDWH,YXHTZ,HJADE,EREHH,PEIRZ,NABGC,JUASF,GFBZH,ANWTG,TLEOA,KJVSR,EKDXN,AHBTS,NPFFE,MSRDA,VYTWN,NLKCV,BYKWD,UFJBB,SBMZM,YXWGE,LEUMN,SYPIQ,VIZEO,RKLOF,NLTQZ,HWRSU,SWPWH,PQJLL,KBRAF,HSXKA,DLHZV,KLQDG,MQMXM,DXPNA,DVNGG,TSMNK,IACAU,NHBNL,VDZYJ,XFOQT,HWTYH,JEALI,UUQGH,HETYA,OTTNY,MTSVW,MAIGI,PJHAG,WCVER,DKERI,XUIJG,GIMQC,GRATP,DJANH,NAWTE,GWDRQ,URMDG,TGQTZ,SQYAA,MFFFE,KYEOC,CAENY,WDDDG,MKGKJ,CCQLI,OMCZJ,KGBVE,WTNHT,RDUWC,ZCIHL,VIHVQ,TMQQB,LLLPU,BAXQN,GCKFF,FBTTQ,ZHKGF,AAOMU,YFYSI,MEUTT,NIKSQ,BNJPJ,JMYAH,RMLDH,VYCPG,OOOZZ,ZOCEI,PIYPU,KKBKV,GWLEM,TATWJ,TXZHQ,ANXRO,SCGYX,LXVFS,QEYAK,BHOTW,IIADP,GJLNM,ZDERT,DIIZR,LQYVJ,EZGVJ,XVGSS,IIQKL,XWYJJ,KIGJK,TSFRR,SMZKL,PGGUE,RLMIE,PGFYB,VBHCT,PSZBB,OOSRC,UYWRF,COKWR,OYMOJ,UNGZT,LBRJN,RUVMV,TFKEV,VITJX,MQFOH,YNMIA,HZUJL,HPRMS,YIQGW,KUVHS,SAWEI,SAFVV,RCUHF,FNAWI,OGBZE,JAZKO,RFNEF,QSVOH,CHUHA,DNDHS,SIBJS,WJXES,HSKJA,WSMCT,MPHQP,OKPCL,CTIMY,JBXCJ,IZNYQ,CDNTD,XATOJ,HWMZU,IMQME,FBMGC,GBMQS,DSLDJ,RHQVA,MBFYX,EKJSW,NFPLX,EKOGT,PELZN,QOTPQ,IYRRD,DGWQU,CPSGD,AXVSP,LXUHA,ZRTSN,USIKN,RJVHN,ZNEYZ,TFJVF,GARKY,IXJLV,RBPYF,QGBCL,OXJWO,LQZRV,EYTJX,TBFUZ,NAXWL,EELAC,FTGFC,ULIKP,ECIFF,PUQEZ,QUCZI,ABNHP,NWTXA,GTFRI,IFICE,SPRNQ,DUFDB,NTISJ,KYEDM,EJBKX,XCVMB,UMSQV,QFIMS,OJMGT,QDEVL,VKRDO,PNUIC,FPRWS,JJOBW,CNNQT,YAEOS,IXGEE,JCFKE,SKYEP,GSWUU,NGRJB,OIRHD,IVRPG,JZTCD,NCYZB,SUGMN,TNKGC,KMOVM,NKBCT,ZUXYR,JGZTU,OYMPO,CCFSK,KFEEC,WMEDF,HCTWZ,JEMLG,NBJTS,YORBF,XVHGX,CQJCV,PNBMQ,NHRCZ,JQGCD,DESVD,BJZLM,FEJFB,EUAMT,EPRKU,YXHIH,AYLZT,GRTCG,CSFWW,PMLCM,CVWTN,XCQDL,JKYWM,YKBZE,KWSSP,BFDVW,LACEB,NWNPW,SBDBC,ORPXV,UWIVF,SWBWJ,UNQGQ,KXVKB,UCCCF,NLJIN,XZAES,QSZFX,WRBFD,ILBWN,QMSAY,AKTFN,FTAWN,MEIGW,HUNLH,YKODC,ABVAN,VZKXX,EXTGU,JEFOJ,ETVJZ,XKCOB,CLUBO,SPAHK,KIGZC,CERRV,HPHZM,AYGDB,WCVSY,SQJGB,UOOUI,GVABQ,SRSWA,PLTHR,UMCCD,IBXIJ,DZFGQ,PWDAO,RRXVX,XSGMF,KLVJE,YWPRR,QZAHX,JGOZA,DCLEN,SKUJP,NLIES,HXCWI,HMDAV,OADJX,YVIXJ,XEFEN,NKBAV,IHXJT,ROADB,ZQMIW,QIQSN,WJNJW,VLMXI,CVKXF,HSRKA,ZUZJR,UUNUF,BHLNE,FDIWR,AQVNZ,EXEAH,RILRX,OEHXR,BMTJW,HBNGC,YSIGO,YZXOR,BMWFX,YVPVC,WEVQA,XXWBF,GWWIN,GHKOL,FGTTX,ZLPYS,JYSCS,YPGDQ,EGTLA,PSDQM,KBVPV,UDGFY,PHUUI,ZUMOQ,WLRIW,GVLXN,LVQTH,FSVJY,XENYK,NFOXP,FOPIU,GHQBS,IPCWS,WMNQR,YRRCO,HZXWU,VAAMW,GCXKI,YALLV,PBZLA,EEFOR,UWOFR,BCRCF,EVEUD,FKNBC,THTCZ,IDBCI,NVXBB,XDACK,DEGPP'
))) as len FROM dual;


       LEN
----------
      3599

4000 over

WITH sub AS (SELECT LEVEL AS lv FROM dual CONNECT BY LEVEL <= 700)
SELECT RTRIM(XMLAGG(XMLELEMENT(E,dbms_random.STRING('U',5),',').EXTRACT('//text()')).getclobval(),',') AS liz FROM sub;

SELECT rn,dbms_lob.getlength(clb) as len FROM test___clob___;

           RN           LEN
------------- -------------
            1          4199


実行結果

4000 below

select item_liz.clbliz(to_clob(
'BFYYC,MFWWD,NGNQV,UHTTZ,DURYJ,PMDBU,UQJIU,ZVFYE,TKVME,XMONH,IIVME,KULCD,GANGL,HFJVU,YADOM,ISEMW,WUMWG,ZBETX,CBFLK,ESAVA,SLOQW,UROQR,GQFPA,MZOKE,FIKBG,JEWPH,IKPBN,TPDLW,QIVZM,DOFAV,JYJGC,YUTNM,CZZGS,UJVSM,JPWFD,SMZGG,ENUYO,LJKUA,FRDKD,ICFUF,PXUWB,MQVFI,EZHIK,QLIRN,VPMWW,MYZKX,XCXBD,GAKUU,UOJCJ,PIOSU,WXUGB,MKTWL,KNXDK,IFITF,KFWAP,EFUKP,HUXEE,YOIUZ,SLYRX,EWPKC,VTTPV,LCZBM,BSNTQ,PMLLF,HXTRW,IJSQA,GANEE,DGZDG,KQAIT,IBKDI,KDMET,RBDIL,MIZPQ,GVTTU,WXBLN,BGUCZ,QUWDE,ARPIN,OSIDW,BWWCM,DXIFU,XFTYW,RSXFO,QJREZ,TKMBX,SZYIP,NJSKE,LRBWD,XJHGS,DIRUB,AQGON,BQHDC,NSDRH,WDPHK,TGAMF,MNVSS,TZOOE,BLISI,NFUZU,EFKVZ,OCXUN,GCUJG,TKHSR,RUOAZ,EQUBK,NKFWB,LCXQM,HGRIO,IFTZA,EXOOU,EKBQB,YQIAK,HOWHW,GRNJA,GCTTZ,FVSQY,IOQYR,NANYN,RNFFV,FLLNJ,EDRIO,DPXIR,XYXQI,ZBXUV,TJBOF,BHFEM,OAVHH,WSFVA,FYMVD,ZMHCP,AFUOX,KEYNS,PDXEZ,EVPVD,ZGVGR,XINVH,QHKYA,MKAFU,PHJTF,EDHLZ,DMNYW,SCDCT,RVGYR,QPIEF,ZKXFX,FLZOC,WMAJF,NZMBC,FCVMS,GYYCJ,JCRIE,ARZXK,VYPCC,XIGKS,SVKZR,SQVEZ,OVBFL,BNRIC,YVLRE,IZESD,ZRUMJ,BUHEI,AGUYQ,GZCUL,AHBVJ,XDKEK,VPMYM,YXWNQ,KNYDB,OCLTZ,TZLUZ,JOXXN,LQXRH,MERVD,XKQSP,BVDHD,XWKPE,VYBVJ,MINLH,RADVX,SZVKR,JBHXG,MLUHD,QMWSC,WRAJO,FBQVL,ZOCCN,OFLXB,BLNPR,PZZHA,UWUWG,HEWAN,UAVAI,AAATA,RNHGS,NHZIW,GVMPI,FVHYO,BAMSC,KMOPK,QQNPH,MKBWO,DLRMC,CGQIH,OCAFT,TYUVI,YXQVL,NHWQE,UZSQA,ECSJK,SBRQQ,JKLJC,VIYNK,OYZJT,HBQBY,PHDYC,EQVMZ,OHBHU,QDFPS,FORQK,YJUZL,QRYTO,MPCFO,UXGPS,JPCPJ,ZGESO,AFNSC,PGMKT,DURJZ,EXDWH,YXHTZ,HJADE,EREHH,PEIRZ,NABGC,JUASF,GFBZH,ANWTG,TLEOA,KJVSR,EKDXN,AHBTS,NPFFE,MSRDA,VYTWN,NLKCV,BYKWD,UFJBB,SBMZM,YXWGE,LEUMN,SYPIQ,VIZEO,RKLOF,NLTQZ,HWRSU,SWPWH,PQJLL,KBRAF,HSXKA,DLHZV,KLQDG,MQMXM,DXPNA,DVNGG,TSMNK,IACAU,NHBNL,VDZYJ,XFOQT,HWTYH,JEALI,UUQGH,HETYA,OTTNY,MTSVW,MAIGI,PJHAG,WCVER,DKERI,XUIJG,GIMQC,GRATP,DJANH,NAWTE,GWDRQ,URMDG,TGQTZ,SQYAA,MFFFE,KYEOC,CAENY,WDDDG,MKGKJ,CCQLI,OMCZJ,KGBVE,WTNHT,RDUWC,ZCIHL,VIHVQ,TMQQB,LLLPU,BAXQN,GCKFF,FBTTQ,ZHKGF,AAOMU,YFYSI,MEUTT,NIKSQ,BNJPJ,JMYAH,RMLDH,VYCPG,OOOZZ,ZOCEI,PIYPU,KKBKV,GWLEM,TATWJ,TXZHQ,ANXRO,SCGYX,LXVFS,QEYAK,BHOTW,IIADP,GJLNM,ZDERT,DIIZR,LQYVJ,EZGVJ,XVGSS,IIQKL,XWYJJ,KIGJK,TSFRR,SMZKL,PGGUE,RLMIE,PGFYB,VBHCT,PSZBB,OOSRC,UYWRF,COKWR,OYMOJ,UNGZT,LBRJN,RUVMV,TFKEV,VITJX,MQFOH,YNMIA,HZUJL,HPRMS,YIQGW,KUVHS,SAWEI,SAFVV,RCUHF,FNAWI,OGBZE,JAZKO,RFNEF,QSVOH,CHUHA,DNDHS,SIBJS,WJXES,HSKJA,WSMCT,MPHQP,OKPCL,CTIMY,JBXCJ,IZNYQ,CDNTD,XATOJ,HWMZU,IMQME,FBMGC,GBMQS,DSLDJ,RHQVA,MBFYX,EKJSW,NFPLX,EKOGT,PELZN,QOTPQ,IYRRD,DGWQU,CPSGD,AXVSP,LXUHA,ZRTSN,USIKN,RJVHN,ZNEYZ,TFJVF,GARKY,IXJLV,RBPYF,QGBCL,OXJWO,LQZRV,EYTJX,TBFUZ,NAXWL,EELAC,FTGFC,ULIKP,ECIFF,PUQEZ,QUCZI,ABNHP,NWTXA,GTFRI,IFICE,SPRNQ,DUFDB,NTISJ,KYEDM,EJBKX,XCVMB,UMSQV,QFIMS,OJMGT,QDEVL,VKRDO,PNUIC,FPRWS,JJOBW,CNNQT,YAEOS,IXGEE,JCFKE,SKYEP,GSWUU,NGRJB,OIRHD,IVRPG,JZTCD,NCYZB,SUGMN,TNKGC,KMOVM,NKBCT,ZUXYR,JGZTU,OYMPO,CCFSK,KFEEC,WMEDF,HCTWZ,JEMLG,NBJTS,YORBF,XVHGX,CQJCV,PNBMQ,NHRCZ,JQGCD,DESVD,BJZLM,FEJFB,EUAMT,EPRKU,YXHIH,AYLZT,GRTCG,CSFWW,PMLCM,CVWTN,XCQDL,JKYWM,YKBZE,KWSSP,BFDVW,LACEB,NWNPW,SBDBC,ORPXV,UWIVF,SWBWJ,UNQGQ,KXVKB,UCCCF,NLJIN,XZAES,QSZFX,WRBFD,ILBWN,QMSAY,AKTFN,FTAWN,MEIGW,HUNLH,YKODC,ABVAN,VZKXX,EXTGU,JEFOJ,ETVJZ,XKCOB,CLUBO,SPAHK,KIGZC,CERRV,HPHZM,AYGDB,WCVSY,SQJGB,UOOUI,GVABQ,SRSWA,PLTHR,UMCCD,IBXIJ,DZFGQ,PWDAO,RRXVX,XSGMF,KLVJE,YWPRR,QZAHX,JGOZA,DCLEN,SKUJP,NLIES,HXCWI,HMDAV,OADJX,YVIXJ,XEFEN,NKBAV,IHXJT,ROADB,ZQMIW,QIQSN,WJNJW,VLMXI,CVKXF,HSRKA,ZUZJR,UUNUF,BHLNE,FDIWR,AQVNZ,EXEAH,RILRX,OEHXR,BMTJW,HBNGC,YSIGO,YZXOR,BMWFX,YVPVC,WEVQA,XXWBF,GWWIN,GHKOL,FGTTX,ZLPYS,JYSCS,YPGDQ,EGTLA,PSDQM,KBVPV,UDGFY,PHUUI,ZUMOQ,WLRIW,GVLXN,LVQTH,FSVJY,XENYK,NFOXP,FOPIU,GHQBS,IPCWS,WMNQR,YRRCO,HZXWU,VAAMW,GCXKI,YALLV,PBZLA,EEFOR,UWOFR,BCRCF,EVEUD,FKNBC,THTCZ,IDBCI,NVXBB,XDACK,DEGPP'
)) as liz from dual;

LIZ(VAL)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(' '), ITEM('BFYYC'), ITEM('MFWWD'), ITEM('NGNQV'), ITEM('UHTTZ'), ITEM('DURYJ'), ITEM('PMDBU'), ITEM('UQJIU'), ITEM('ZVFYE'), ITEM('TKVME'), ITEM('XMONH'), ITEM('IIVME'), ITEM('KULCD'), ITEM('GANGL'), ITEM('HFJVU'), ITEM('YADOM'), ITEM('ISEMW'), ITEM('WUMWG'), ITEM('ZBETX'), ITEM('CBFLK'), ITEM('ESAVA'), ITEM('SLOQW'), ITEM('UROQR'), ITEM('GQFPA'), ITEM('MZOKE'), ITEM('FIKBG'), ITEM('JEWPH'), ITEM('IKPBN'), ITEM('TPDLW'), ITEM('QIVZM'), ITEM('DOFAV'), ITEM('JYJGC'), ITEM('YUTNM'), ITEM('CZZGS'), ITEM('UJVSM'), ITEM('JPWFD'), ITEM('SMZGG'), ITEM('ENUYO'), ITEM('LJKUA'), ITEM('FRDKD'), ITEM('ICFUF'), ITEM('PXUWB'), ITEM('MQVFI'), ITEM('EZHIK'), ITEM('QLIRN'), ITEM('VPMWW'), ITEM('MYZKX'), ITEM('XCXBD'), ITEM('GAKUU'), ITEM('UOJCJ'), ITEM('PIOSU'), ITEM('WXUGB'), ITEM('MKTWL'), ITEM('KNXDK'), ITEM('IFITF'), ITEM('KFWAP'), ITEM('EFUKP'), ITEM('HUXEE'), ITEM('YOIUZ'), ITEM('SLYRX'), ITEM('EWPKC'), ITEM('VTTPV'), ITEM('LCZBM'), ITEM('BSNTQ'), ITEM('PMLLF'), ITEM('HXTRW'), ITEM('IJSQA'), ITEM('GANEE'), ITEM('DGZDG'), ITEM('KQAIT'), ITEM('IBKDI'), ITEM('KDMET'), ITEM('RBDIL'), ITEM('MIZPQ'), ITEM('GVTTU'), ITEM('WXBLN'), ITEM('BGUCZ'), ITEM('QUWDE'), ITEM('ARPIN'), ITEM('OSIDW'), ITEM('BWWCM'), ITEM('DXIFU'), ITEM('XFTYW'), ITEM('RSXFO'), ITEM('QJREZ'), ITEM('TKMBX'), ITEM('SZYIP'), ITEM('NJSKE'), ITEM('LRBWD'), ITEM('XJHGS'), ITEM('DIRUB'), ITEM('AQGON'), ITEM('BQHDC'), ITEM('NSDRH'), ITEM('WDPHK'), ITEM('TGAMF'), ITEM('MNVSS'), ITEM('TZOOE'), ITEM('BLISI'), ITEM('NFUZU'), ITEM('EFKVZ'), ITEM('OCXUN'), ITEM('GCUJG'), ITEM('TKHSR'), ITEM('RUOAZ'), ITEM('EQUBK'), ITEM('NKFWB'), ITEM('LCXQM'), ITEM('HGRIO'), ITEM('IFTZA'), ITEM('EXOOU'), ITEM('EKBQB'), ITEM('YQIAK'), ITEM('HOWHW'), ITEM('GRNJA'), ITEM('GCTTZ'), ITEM('FVSQY'), ITEM('IOQYR'), ITEM('NANYN'), ITEM('RNFFV'), ITEM('FLLNJ'), ITEM('EDRIO'), ITEM('DPXIR'), ITEM('XYXQI'), ITEM('ZBXUV'), ITEM('TJBOF'), ITEM('BHFEM'), ITEM('OAVHH'), ITEM('WSFVA'), ITEM('FYMVD'), ITEM('ZMHCP'), ITEM('AFUOX'), ITEM('KEYNS'), ITEM(
'PDXEZ'), ITEM('EVPVD'), ITEM('ZGVGR'), ITEM('XINVH'), ITEM('QHKYA'), ITEM('MKAFU'), ITEM('PHJTF'), ITEM('EDHLZ'), ITEM('DMNYW'), ITEM('SCDCT'), ITEM('RVGYR'), ITEM('QPIEF'), ITEM('ZKXFX'), ITEM('FLZOC'), ITEM('WMAJF'), ITEM('NZMBC'), ITEM('FCVMS'), ITEM('GYYCJ'), ITEM('JCRIE'), ITEM('ARZXK'), ITEM('VYPCC'), ITEM('XIGKS'), ITEM('SVKZR'), ITEM('SQVEZ'), ITEM('OVBFL'), ITEM('BNRIC'), ITEM('YVLRE'), ITEM('IZESD'), ITEM('ZRUMJ'), ITEM('BUHEI'), ITEM('AGUYQ'), ITEM('GZCUL'), ITEM('AHBVJ'), ITEM('XDKEK'), ITEM('VPMYM'), ITEM('YXWNQ'), ITEM('KNYDB'), ITEM('OCLTZ'), ITEM('TZLUZ'), ITEM('JOXXN'), ITEM('LQXRH'), ITEM('MERVD'), ITEM('XKQSP'), ITEM('BVDHD'), ITEM('XWKPE'), ITEM('VYBVJ'), ITEM('MINLH'), ITEM('RADVX'), ITEM('SZVKR'), ITEM('JBHXG'), ITEM('MLUHD'), ITEM('QMWSC'), ITEM('WRAJO'), ITEM('FBQVL'), ITEM('ZOCCN'), ITEM('OFLXB'), ITEM('BLNPR'), ITEM('PZZHA'), ITEM('UWUWG'), ITEM('HEWAN'), ITEM('UAVAI'), ITEM('AAATA'), ITEM('RNHGS'), ITEM('NHZIW'), ITEM('GVMPI'), ITEM('FVHYO'), ITEM('BAMSC'), ITEM('KMOPK'), ITEM('QQNPH'), ITEM('MKBWO'), ITEM('DLRMC'), ITEM('CGQIH'), ITEM('OCAFT'), ITEM('TYUVI'), ITEM('YXQVL'), ITEM('NHWQE'), ITEM('UZSQA'), ITEM('ECSJK'), ITEM('SBRQQ'), ITEM('JKLJC'), ITEM('VIYNK'), ITEM('OYZJT'), ITEM('HBQBY'), ITEM('PHDYC'), ITEM('EQVMZ'), ITEM('OHBHU'), ITEM('QDFPS'), ITEM('FORQK'), ITEM('YJUZL'), ITEM('QRYTO'), ITEM('MPCFO'), ITEM('UXGPS'), ITEM('JPCPJ'), ITEM('ZGESO'), ITEM('AFNSC'), ITEM('PGMKT'), ITEM('DURJZ'), ITEM('EXDWH'), ITEM('YXHTZ'), ITEM('HJADE'), ITEM('EREHH'), ITEM('PEIRZ'), ITEM('NABGC'), ITEM('JUASF'), ITEM('GFBZH'), ITEM('ANWTG'), ITEM('TLEOA'), ITEM('KJVSR'), ITEM('EKDXN'), ITEM('AHBTS'), ITEM('NPFFE'), ITEM('MSRDA'), ITEM('VYTWN'), ITEM('NLKCV'), ITEM('BYKWD'), ITEM('UFJBB'), ITEM('SBMZM'), ITEM('YXWGE'), ITEM('LEUMN'), ITEM('SYPIQ'), ITEM('VIZEO'), ITEM('RKLOF'), ITEM('NLTQZ'), ITEM('HWRSU'), ITEM('SWPWH'), ITEM('PQJLL'), ITEM('KBRAF'), ITEM('HSXKA'), ITEM('DLHZV'), ITEM('KLQDG'), ITEM('MQMXM'), ITEM('DXPNA'), ITEM('DVNGG'), ITEM('TSMN
K'), ITEM('IACAU'), ITEM('NHBNL'), ITEM('VDZYJ'), ITEM('XFOQT'), ITEM('HWTYH'), ITEM('JEALI'), ITEM('UUQGH'), ITEM('HETYA'), ITEM('OTTNY'), ITEM('MTSVW'), ITEM('MAIGI'), ITEM('PJHAG'), ITEM('WCVER'), ITEM('DKERI'), ITEM('XUIJG'), ITEM('GIMQC'), ITEM('GRATP'), ITEM('DJANH'), ITEM('NAWTE'), ITEM('GWDRQ'), ITEM('URMDG'), ITEM('TGQTZ'), ITEM('SQYAA'), ITEM('MFFFE'), ITEM('KYEOC'), ITEM('CAENY'), ITEM('WDDDG'), ITEM('MKGKJ'), ITEM('CCQLI'), ITEM('OMCZJ'), ITEM('KGBVE'), ITEM('WTNHT'), ITEM('RDUWC'), ITEM('ZCIHL'), ITEM('VIHVQ'), ITEM('TMQQB'), ITEM('LLLPU'), ITEM('BAXQN'), ITEM('GCKFF'), ITEM('FBTTQ'), ITEM('ZHKGF'), ITEM('AAOMU'), ITEM('YFYSI'), ITEM('MEUTT'), ITEM('NIKSQ'), ITEM('BNJPJ'), ITEM('JMYAH'), ITEM('RMLDH'), ITEM('VYCPG'), ITEM('OOOZZ'), ITEM('ZOCEI'), ITEM('PIYPU'), ITEM('KKBKV'), ITEM('GWLEM'), ITEM('TATWJ'), ITEM('TXZHQ'), ITEM('ANXRO'), ITEM('SCGYX'), ITEM('LXVFS'), ITEM('QEYAK'), ITEM('BHOTW'), ITEM('IIADP'), ITEM('GJLNM'), ITEM('ZDERT'), ITEM('DIIZR'), ITEM('LQYVJ'), ITEM('EZGVJ'), ITEM('XVGSS'), ITEM('IIQKL'), ITEM('XWYJJ'), ITEM('KIGJK'), ITEM('TSFRR'), ITEM('SMZKL'), ITEM('PGGUE'), ITEM('RLMIE'), ITEM('PGFYB'), ITEM('VBHCT'), ITEM('PSZBB'), ITEM('OOSRC'), ITEM('UYWRF'), ITEM('COKWR'), ITEM('OYMOJ'), ITEM('UNGZT'), ITEM('LBRJN'), ITEM('RUVMV'), ITEM('TFKEV'), ITEM('VITJX'), ITEM('MQFOH'), ITEM('YNMIA'), ITEM('HZUJL'), ITEM('HPRMS'), ITEM('YIQGW'), ITEM('KUVHS'), ITEM('SAWEI'), ITEM('SAFVV'), ITEM('RCUHF'), ITEM('FNAWI'), ITEM('OGBZE'), ITEM('JAZKO'), ITEM('RFNEF'), ITEM('QSVOH'), ITEM('CHUHA'), ITEM('DNDHS'), ITEM('SIBJS'), ITEM('WJXES'), ITEM('HSKJA'), ITEM('WSMCT'), ITEM('MPHQP'), ITEM('OKPCL'), ITEM('CTIMY'), ITEM('JBXCJ'), ITEM('IZNYQ'), ITEM('CDNTD'), ITEM('XATOJ'), ITEM('HWMZU'), ITEM('IMQME'), ITEM('FBMGC'), ITEM('GBMQS'), ITEM('DSLDJ'), ITEM('RHQVA'), ITEM('MBFYX'), ITEM('EKJSW'), ITEM('NFPLX'), ITEM('EKOGT'), ITEM('PELZN'), ITEM('QOTPQ'), ITEM('IYRRD'), ITEM('DGWQU'), ITEM('CPSGD'), ITEM('AXVSP'), ITEM('LXUHA'), ITEM('ZRTSN'), ITEM('USIKN'), 
ITEM('RJVHN'), ITEM('ZNEYZ'), ITEM('TFJVF'), ITEM('GARKY'), ITEM('IXJLV'), ITEM('RBPYF'), ITEM('QGBCL'), ITEM('OXJWO'), ITEM('LQZRV'), ITEM('EYTJX'), ITEM('TBFUZ'), ITEM('NAXWL'), ITEM('EELAC'), ITEM('FTGFC'), ITEM('ULIKP'), ITEM('ECIFF'), ITEM('PUQEZ'), ITEM('QUCZI'), ITEM('ABNHP'), ITEM('NWTXA'), ITEM('GTFRI'), ITEM('IFICE'), ITEM('SPRNQ'), ITEM('DUFDB'), ITEM('NTISJ'), ITEM('KYEDM'), ITEM('EJBKX'), ITEM('XCVMB'), ITEM('UMSQV'), ITEM('QFIMS'), ITEM('OJMGT'), ITEM('QDEVL'), ITEM('VKRDO'), ITEM('PNUIC'), ITEM('FPRWS'), ITEM('JJOBW'), ITEM('CNNQT'), ITEM('YAEOS'), ITEM('IXGEE'), ITEM('JCFKE'), ITEM('SKYEP'), ITEM('GSWUU'), ITEM('NGRJB'), ITEM('OIRHD'), ITEM('IVRPG'), ITEM('JZTCD'), ITEM('NCYZB'), ITEM('SUGMN'), ITEM('TNKGC'), ITEM('KMOVM'), ITEM('NKBCT'), ITEM('ZUXYR'), ITEM('JGZTU'), ITEM('OYMPO'), ITEM('CCFSK'), ITEM('KFEEC'), ITEM('WMEDF'), ITEM('HCTWZ'), ITEM('JEMLG'), ITEM('NBJTS'), ITEM('YORBF'), ITEM('XVHGX'), ITEM('CQJCV'), ITEM('PNBMQ'), ITEM('NHRCZ'), ITEM('JQGCD'), ITEM('DESVD'), ITEM('BJZLM'), ITEM('FEJFB'), ITEM('EUAMT'), ITEM('EPRKU'), ITEM('YXHIH'), ITEM('AYLZT'), ITEM('GRTCG'), ITEM('CSFWW'), ITEM('PMLCM'), ITEM('CVWTN'), ITEM('XCQDL'), ITEM('JKYWM'), ITEM('YKBZE'), ITEM('KWSSP'), ITEM('BFDVW'), ITEM('LACEB'), ITEM('NWNPW'), ITEM('SBDBC'), ITEM('ORPXV'), ITEM('UWIVF'), ITEM('SWBWJ'), ITEM('UNQGQ'), ITEM('KXVKB'), ITEM('UCCCF'), ITEM('NLJIN'), ITEM('XZAES'), ITEM('QSZFX'), ITEM('WRBFD'), ITEM('ILBWN'), ITEM('QMSAY'), ITEM('AKTFN'), ITEM('FTAWN'), ITEM('MEIGW'), ITEM('HUNLH'), ITEM(NULL), ITEM('YKODC'), ITEM('ABVAN'), ITEM('VZKXX'), ITEM('EXTGU'), ITEM('JEFOJ'), ITEM('ETVJZ'), ITEM('XKCOB'), ITEM('CLUBO'), ITEM('SPAHK'), ITEM('KIGZC'), ITEM('CERRV'), ITEM('HPHZM'), ITEM('AYGDB'), ITEM('WCVSY'), ITEM('SQJGB'), ITEM('UOOUI'), ITEM('GVABQ'), ITEM('SRSWA'), ITEM('PLTHR'), ITEM('UMCCD'), ITEM('IBXIJ'), ITEM('DZFGQ'), ITEM('PWDAO'), ITEM('RRXVX'), ITEM('XSGMF'), ITEM('KLVJE'), ITEM('YWPRR'), ITEM('QZAHX'), ITEM('JGOZA'), ITEM('DCLEN'), ITEM('SKUJP'), ITEM('NL
IES'), ITEM('HXCWI'), ITEM('HMDAV'), ITEM('OADJX'), ITEM('YVIXJ'), ITEM('XEFEN'), ITEM('NKBAV'), ITEM('IHXJT'), ITEM('ROADB'), ITEM('ZQMIW'), ITEM('QIQSN'), ITEM('WJNJW'), ITEM('VLMXI'), ITEM('CVKXF'), ITEM('HSRKA'), ITEM('ZUZJR'), ITEM('UUNUF'), ITEM('BHLNE'), ITEM('FDIWR'), ITEM('AQVNZ'), ITEM('EXEAH'), ITEM('RILRX'), ITEM('OEHXR'), ITEM('BMTJW'), ITEM('HBNGC'), ITEM('YSIGO'), ITEM('YZXOR'), ITEM('BMWFX'), ITEM('YVPVC'), ITEM('WEVQA'), ITEM('XXWBF'), ITEM('GWWIN'), ITEM('GHKOL'), ITEM('FGTTX'), ITEM('ZLPYS'), ITEM('JYSCS'), ITEM('YPGDQ'), ITEM('EGTLA'), ITEM('PSDQM'), ITEM('KBVPV'), ITEM('UDGFY'), ITEM('PHUUI'), ITEM('ZUMOQ'), ITEM('WLRIW'), ITEM('GVLXN'), ITEM('LVQTH'), ITEM('FSVJY'), ITEM('XENYK'), ITEM('NFOXP'), ITEM('FOPIU'), ITEM('GHQBS'), ITEM('IPCWS'), ITEM('WMNQR'), ITEM('YRRCO'), ITEM('HZXWU'), ITEM('VAAMW'), ITEM('GCXKI'), ITEM('YALLV'), ITEM('PBZLA'), ITEM('EEFOR'), ITEM('UWOFR'), ITEM('BCRCF'), ITEM('EVEUD'), ITEM('FKNBC'), ITEM('THTCZ'), ITEM('IDBCI'), ITEM('NVXBB'), ITEM('XDACK'), ITEM('DEGPP'))


BFYYC,MFWWD,NGNQV,UHTTZ,DURYJ,PMDBU,UQJIU,ZVFYE,TKVME,XMONH,IIVME,KULCD,GANGL,HFJVU,YADOM,ISEMW,WUMWG,ZBETX,CBFLK,ESAVA,SLOQW,UROQR,GQFPA,MZOKE,FIKBG,JEWPH,IKPBN,TPDLW,QIVZM,DOFAV,JYJGC,YUTNM,CZZGS,UJVSM,JPWFD,SMZGG,ENUYO,LJKUA,FRDKD,ICFUF,PXUWB,MQVFI,EZHIK,QLIRN,VPMWW,MYZKX,XCXBD,GAKUU,UOJCJ,PIOSU,WXUGB,MKTWL,KNXDK,IFITF,KFWAP,EFUKP,HUXEE,YOIUZ,SLYRX,EWPKC,VTTPV,LCZBM,BSNTQ,PMLLF,HXTRW,IJSQA,GANEE,DGZDG,KQAIT,IBKDI,KDMET,RBDIL,MIZPQ,GVTTU,WXBLN,BGUCZ,QUWDE,ARPIN,OSIDW,BWWCM,DXIFU,XFTYW,RSXFO,QJREZ,TKMBX,SZYIP,NJSKE,LRBWD,XJHGS,DIRUB,AQGON,BQHDC,NSDRH,WDPHK,TGAMF,MNVSS,TZOOE,BLISI,NFUZU,EFKVZ,OCXUN,GCUJG,TKHSR,RUOAZ,EQUBK,NKFWB,LCXQM,HGRIO,IFTZA,EXOOU,EKBQB,YQIAK,HOWHW,GRNJA,GCTTZ,FVSQY,IOQYR,NANYN,RNFFV,FLLNJ,EDRIO,DPXIR,XYXQI,ZBXUV,TJBOF,BHFEM,OAVHH,WSFVA,FYMVD,ZMHCP,AFUOX,KEYNS,PDXEZ,EVPVD,ZGVGR,XINVH,QHKYA,MKAFU,PHJTF,EDHLZ,DMNYW,SCDCT,RVGYR,QPIEF,ZKXFX,FLZOC,WMAJF,NZMBC,FCVMS,GYYCJ,JCRIE,ARZXK,VYPCC,XIGKS,SVKZR,SQVEZ,OVBFL,BNRIC,YVLRE,IZESD,ZRUMJ,BUHEI,AGUYQ,GZCUL,AHBVJ,XDKEK,VPMYM,YXWNQ,KNYDB,OCLTZ,TZLUZ,JOXXN,LQXRH,MERVD,XKQSP,BVDHD,XWKPE,VYBVJ,MINLH,RADVX,SZVKR,JBHXG,MLUHD,QMWSC,WRAJO,FBQVL,ZOCCN,OFLXB,BLNPR,PZZHA,UWUWG,HEWAN,UAVAI,AAATA,RNHGS,NHZIW,GVMPI,FVHYO,BAMSC,KMOPK,QQNPH,MKBWO,DLRMC,CGQIH,OCAFT,TYUVI,YXQVL,NHWQE,UZSQA,ECSJK,SBRQQ,JKLJC,VIYNK,OYZJT,HBQBY,PHDYC,EQVMZ,OHBHU,QDFPS,FORQK,YJUZL,QRYTO,MPCFO,UXGPS,JPCPJ,ZGESO,AFNSC,PGMKT,DURJZ,EXDWH,YXHTZ,HJADE,EREHH,PEIRZ,NABGC,JUASF,GFBZH,ANWTG,TLEOA,KJVSR,EKDXN,AHBTS,NPFFE,MSRDA,VYTWN,NLKCV,BYKWD,UFJBB,SBMZM,YXWGE,LEUMN,SYPIQ,VIZEO,RKLOF,NLTQZ,HWRSU,SWPWH,PQJLL,KBRAF,HSXKA,DLHZV,KLQDG,MQMXM,DXPNA,DVNGG,TSMNK,IACAU,NHBNL,VDZYJ,XFOQT,HWTYH,JEALI,UUQGH,HETYA,OTTNY,MTSVW,MAIGI,PJHAG,WCVER,DKERI,XUIJG,GIMQC,GRATP,DJANH,NAWTE,GWDRQ,URMDG,TGQTZ,SQYAA,MFFFE,KYEOC,CAENY,WDDDG,MKGKJ,CCQLI,OMCZJ,KGBVE,WTNHT,RDUWC,ZCIHL,VIHVQ,TMQQB,LLLPU,BAXQN,GCKFF,FBTTQ,ZHKGF,AAOMU,YFYSI,MEUTT,NIKSQ,BNJPJ,JMYAH,RMLDH,VYCPG,OOOZZ,ZOCEI,PIYPU,KKBKV,GWLEM,TATWJ,TXZHQ,ANXRO,SCGYX,LXVFS,QEYAK,BHOTW,IIADP,GJLNM,ZDERT,DIIZR,LQYVJ,EZGVJ,XVGSS,IIQKL,XWYJJ,KIGJK,TSFRR,SMZKL,PGGUE,RLMIE,PGFYB,VBHCT,PSZBB,OOSRC,UYWRF,COKWR,OYMOJ,UNGZT,LBRJN,RUVMV,TFKEV,VITJX,MQFOH,YNMIA,HZUJL,HPRMS,YIQGW,KUVHS,SAWEI,SAFVV,RCUHF,FNAWI,OGBZE,JAZKO,RFNEF,QSVOH,CHUHA,DNDHS,SIBJS,WJXES,HSKJA,WSMCT,MPHQP,OKPCL,CTIMY,JBXCJ,IZNYQ,CDNTD,XATOJ,HWMZU,IMQME,FBMGC,GBMQS,DSLDJ,RHQVA,MBFYX,EKJSW,NFPLX,EKOGT,PELZN,QOTPQ,IYRRD,DGWQU,CPSGD,AXVSP,LXUHA,ZRTSN,USIKN,RJVHN,ZNEYZ,TFJVF,GARKY,IXJLV,RBPYF,QGBCL,OXJWO,LQZRV,EYTJX,TBFUZ,NAXWL,EELAC,FTGFC,ULIKP,ECIFF,PUQEZ,QUCZI,ABNHP,NWTXA,GTFRI,IFICE,SPRNQ,DUFDB,NTISJ,KYEDM,EJBKX,XCVMB,UMSQV,QFIMS,OJMGT,QDEVL,VKRDO,PNUIC,FPRWS,JJOBW,CNNQT,YAEOS,IXGEE,JCFKE,SKYEP,GSWUU,NGRJB,OIRHD,IVRPG,JZTCD,NCYZB,SUGMN,TNKGC,KMOVM,NKBCT,ZUXYR,JGZTU,OYMPO,CCFSK,KFEEC,WMEDF,HCTWZ,JEMLG,NBJTS,YORBF,XVHGX,CQJCV,PNBMQ,NHRCZ,JQGCD,DESVD,BJZLM,FEJFB,EUAMT,EPRKU,YXHIH,AYLZT,GRTCG,CSFWW,PMLCM,CVWTN,XCQDL,JKYWM,YKBZE,KWSSP,BFDVW,LACEB,NWNPW,SBDBC,ORPXV,UWIVF,SWBWJ,UNQGQ,KXVKB,UCCCF,NLJIN,XZAES,QSZFX,WRBFD,ILBWN,QMSAY,AKTFN,FTAWN,MEIGW,HUNLH,
WITH sub AS ( SELECT :tmpclb as tmpclb FROM dual ) ,rec ( rn  ,srt  ,nxt  ,len  ,ele  ,liz ) AS ( SELECT 1 AS rn  ,1 AS srt  ,1 AS nxt  ,instr(tmpclb ,',' ,1 ,1) - 1 AS len  ,dbms_lob.getlength(tmpclb) - dbms_lob.getlength(REPLACE(tmpclb , ',','') ) + 1 AS ele  ,liz(item(to_clob(dbms_lob.substr(tmpclb ,instr(tmpclb ,',' ,1 ,1) - 1 ,1) ) ) ) AS liz FROM sub UNION ALL SELECT s1.rn + 1 AS rn  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) AS srt  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1 AS nxt  ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 AS len  ,s1.ele  ,s1.liz MULTISET UNION ALL liz(item(to_clob(dbms_lob.substr(s2.tmpclb ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1) ) ) ) AS liz FROM rec s1  ,sub s2 WHERE s1.rn + 1 <= s1.ele ) SELECT liz FROM ( SELECT s3.*  ,row_number() OVER( PARTITION BY s3.rn ORDER BY s3.tmpseq ) AS seq FROM ( SELECT s1.*  ,s2.*  ,ROWNUM AS tmpseq FROM rec s1  ,TABLE ( liz ) s2 ) s3 ) WHERE rn = ele AND ele = seq
YKODC,ABVAN,VZKXX,EXTGU,JEFOJ,ETVJZ,XKCOB,CLUBO,SPAHK,KIGZC,CERRV,HPHZM,AYGDB,WCVSY,SQJGB,UOOUI,GVABQ,SRSWA,PLTHR,UMCCD,IBXIJ,DZFGQ,PWDAO,RRXVX,XSGMF,KLVJE,YWPRR,QZAHX,JGOZA,DCLEN,SKUJP,NLIES,HXCWI,HMDAV,OADJX,YVIXJ,XEFEN,NKBAV,IHXJT,ROADB,ZQMIW,QIQSN,WJNJW,VLMXI,CVKXF,HSRKA,ZUZJR,UUNUF,BHLNE,FDIWR,AQVNZ,EXEAH,RILRX,OEHXR,BMTJW,HBNGC,YSIGO,YZXOR,BMWFX,YVPVC,WEVQA,XXWBF,GWWIN,GHKOL,FGTTX,ZLPYS,JYSCS,YPGDQ,EGTLA,PSDQM,KBVPV,UDGFY,PHUUI,ZUMOQ,WLRIW,GVLXN,LVQTH,FSVJY,XENYK,NFOXP,FOPIU,GHQBS,IPCWS,WMNQR,YRRCO,HZXWU,VAAMW,GCXKI,YALLV,PBZLA,EEFOR,UWOFR,BCRCF,EVEUD,FKNBC,THTCZ,IDBCI,NVXBB,XDACK,DEGPP
WITH sub AS ( SELECT :tmpclb as tmpclb FROM dual ) ,rec ( rn  ,srt  ,nxt  ,len  ,ele  ,liz ) AS ( SELECT 1 AS rn  ,1 AS srt  ,1 AS nxt  ,instr(tmpclb ,',' ,1 ,1) - 1 AS len  ,dbms_lob.getlength(tmpclb) - dbms_lob.getlength(REPLACE(tmpclb , ',','') ) + 1 AS ele  ,liz(item(to_clob(dbms_lob.substr(tmpclb ,instr(tmpclb ,',' ,1 ,1) - 1 ,1) ) ) ) AS liz FROM sub UNION ALL SELECT s1.rn + 1 AS rn  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) AS srt  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1 AS nxt  ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 AS len  ,s1.ele  ,s1.liz MULTISET UNION ALL liz(item(to_clob(dbms_lob.substr(s2.tmpclb ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1) ) ) ) AS liz FROM rec s1  ,sub s2 WHERE s1.rn + 1 <= s1.ele ) SELECT liz FROM ( SELECT s3.*  ,row_number() OVER( PARTITION BY s3.rn ORDER BY s3.tmpseq ) AS seq FROM ( SELECT s1.*  ,s2.*  ,ROWNUM AS tmpseq FROM rec s1  ,TABLE ( liz ) s2 ) s3 ) WHERE rn = ele AND ele = seq

経過時間: 00:00:37.601

4000 over

SELECT item_liz.clbliz(clb) AS liz FROM test___clob___;

LIZ(VAL)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(' '), ITEM('TANPG'), ITEM('GRXBG'), ITEM('OOFDS'), ITEM('EWLPP'), ITEM('QFOCB'), ITEM('UJMKH'), ITEM('GTVLB'), ITEM('TQKGQ'), ITEM('GDRKT'), ITEM('ZSYVL'), ITEM('MPJYT'), ITEM('UVXBQ'), ITEM('OXRWR'), ITEM('QHVOY'), ITEM('UDPHZ'), ITEM('JFGAX'), ITEM('GWBTG'), ITEM('ZOQID'), ITEM('JEIXM'), ITEM('YUZGH'), ITEM('YNBLT'), ITEM('MYOFT'), ITEM('SBUDK'), ITEM('UXFNA'), ITEM('URAWW'), ITEM('HBJGF'), ITEM('QDTBI'), ITEM('AJAHL'), ITEM('TGSCG'), ITEM('UZFDA'), ITEM('YOQGD'), ITEM('UVCVA'), ITEM('SPRYV'), ITEM('ACDDS'), ITEM('FQMFD'), ITEM('ALABA'), ITEM('HZXWM'), ITEM('UYOED'), ITEM('BBQZK'), ITEM('OHVGK'), ITEM('MFVQF'), ITEM('BPBGF'), ITEM('UCCTX'), ITEM('EKPNZ'), ITEM('EDETR'), ITEM('QEUAJ'), ITEM('LYFXQ'), ITEM('NBNYT'), ITEM('ASRGX'), ITEM('YGAPY'), ITEM('NMYKE'), ITEM('EVAKG'), ITEM('KAZMC'), ITEM('LQDGW'), ITEM('XKIFF'), ITEM('CCLUG'), ITEM('OSGZU'), ITEM('PCBYA'), ITEM('UMNYF'), ITEM('CEIJD'), ITEM('UELXD'), ITEM('TOAWY'), ITEM('LJTQG'), ITEM('FQCNB'), ITEM('LLPUU'), ITEM('DNMOP'), ITEM('FWPFE'), ITEM('RMVAQ'), ITEM('OOKRR'), ITEM('BREBT'), ITEM('EPUZX'), ITEM('PHYKS'), ITEM('IUXLD'), ITEM('QBFMG'), ITEM('VERYS'), ITEM('DNGYV'), ITEM('WRCIO'), ITEM('CMXDP'), ITEM('KVPXB'), ITEM('REMXD'), ITEM('IMISR'), ITEM('XTHGV'), ITEM('LEPNB'), ITEM('XWJJH'), ITEM('FLABE'), ITEM('XWWKQ'), ITEM('GVTGO'), ITEM('OOVQU'), ITEM('YXBTC'), ITEM('LSMHL'), ITEM('ZKYYE'), ITEM('MJEOB'), ITEM('NZCZC'), ITEM('ZSEJR'), ITEM('RVGKB'), ITEM('HXTNM'), ITEM('IWGIP'), ITEM('LIDLD'), ITEM('PCVTU'), ITEM('BXUYL'), ITEM('CKHOV'), ITEM('BRVRS'), ITEM('ZSAUK'), ITEM('CLLDO'), ITEM('DIGEK'), ITEM('MWDFZ'), ITEM('TADVA'), ITEM('EXYDH'), ITEM('EHVMY'), ITEM('AEGEX'), ITEM('PACEP'), ITEM('NWESB'), ITEM('ALVVN'), ITEM('ZWYTO'), ITEM('JPSYA'), ITEM('BJLLP'), ITEM('KMEIE'), ITEM('JNZZS'), ITEM('FYJZC'), ITEM('RHSKR'), ITEM('POWWW'), ITEM('QBMQB'), ITEM('CGQWJ'), ITEM('FUIVN'), ITEM('SCWCN'), ITEM('MHZQL'), ITEM('SYAHL'), ITEM('GCFUU'), ITEM('KQGPO'), ITEM('PDDHX'), ITEM('EZUHT'), ITEM('CADWN'), ITEM(
'CUKQI'), ITEM('ONTCE'), ITEM('ZEZBD'), ITEM('GRZGV'), ITEM('JLYLT'), ITEM('MABKI'), ITEM('TEAEK'), ITEM('SFTJQ'), ITEM('TCIHY'), ITEM('HGLGZ'), ITEM('XKOVY'), ITEM('VGKRS'), ITEM('XGXCJ'), ITEM('JXEUM'), ITEM('XLBOC'), ITEM('ITEWA'), ITEM('MYLZD'), ITEM('SAGPC'), ITEM('LCAGT'), ITEM('CMFMT'), ITEM('UEXIO'), ITEM('ASIAV'), ITEM('VTJCS'), ITEM('AZXJY'), ITEM('LIGVS'), ITEM('ROOUP'), ITEM('BNJUI'), ITEM('AZDHD'), ITEM('NVAZE'), ITEM('DCZEC'), ITEM('BYOTO'), ITEM('MVLWI'), ITEM('PTWJP'), ITEM('ETIGA'), ITEM('EMTJX'), ITEM('QMJUX'), ITEM('TQMII'), ITEM('QAEGF'), ITEM('JPXEN'), ITEM('CZUIL'), ITEM('DHLYL'), ITEM('ZPACJ'), ITEM('KOCJR'), ITEM('YKWNV'), ITEM('JDYDE'), ITEM('RPTDF'), ITEM('CPRGV'), ITEM('FPBJL'), ITEM('ALSIO'), ITEM('ANHBA'), ITEM('YJXGO'), ITEM('HZARP'), ITEM('FRQUQ'), ITEM('GTRKA'), ITEM('JKIGE'), ITEM('YDLLF'), ITEM('SNCAM'), ITEM('QXQHN'), ITEM('UUSAF'), ITEM('QRMZZ'), ITEM('BWSJG'), ITEM('CHBSA'), ITEM('NROUP'), ITEM('RIOKX'), ITEM('UNMCA'), ITEM('OBZSD'), ITEM('XFIEO'), ITEM('ZQKBE'), ITEM('RLHWI'), ITEM('JLAOP'), ITEM('NMZLB'), ITEM('BLUJY'), ITEM('GFGBF'), ITEM('CQFFR'), ITEM('VZAST'), ITEM('CXXDP'), ITEM('EPLRP'), ITEM('ZGTYY'), ITEM('YXQHF'), ITEM('WNYBN'), ITEM('BHAOI'), ITEM('HOWJF'), ITEM('RPJVQ'), ITEM('WENVD'), ITEM('AOCVZ'), ITEM('BWNRU'), ITEM('QYFDE'), ITEM('MWAOZ'), ITEM('EYJIT'), ITEM('OTVVA'), ITEM('AONDJ'), ITEM('AJWCZ'), ITEM('CFVPI'), ITEM('VCFVE'), ITEM('WJLEL'), ITEM('UDWQU'), ITEM('CXCEY'), ITEM('ZYOZG'), ITEM('MZFJP'), ITEM('NTMNP'), ITEM('TQEGF'), ITEM('MIQAA'), ITEM('ULTED'), ITEM('BFTDI'), ITEM('BOEBO'), ITEM('LWEQZ'), ITEM('JWMVA'), ITEM('HKKUZ'), ITEM('ATZTK'), ITEM('QAKDT'), ITEM('VVASQ'), ITEM('HBBLV'), ITEM('MRNMV'), ITEM('VSDOY'), ITEM('AFMDB'), ITEM('MEFMR'), ITEM('ESARS'), ITEM('AENWM'), ITEM('DWCIL'), ITEM('VOSXZ'), ITEM('OAQQW'), ITEM('XIFYD'), ITEM('YFTLN'), ITEM('FRRAR'), ITEM('HWAQG'), ITEM('MABWZ'), ITEM('MTGCH'), ITEM('BPIWR'), ITEM('DDTQY'), ITEM('QCTZM'), ITEM('NWOYQ'), ITEM('VNQRT'), ITEM('WUZEF'), ITEM('GHJT
K'), ITEM('WVUTI'), ITEM('FMDKG'), ITEM('YNYLY'), ITEM('DPTTZ'), ITEM('UMCWV'), ITEM('JJAZS'), ITEM('AZPUF'), ITEM('VCBBB'), ITEM('CLEQF'), ITEM('UYPXZ'), ITEM('FCVWG'), ITEM('SFEUH'), ITEM('XNCAA'), ITEM('EJBMZ'), ITEM('FSEXP'), ITEM('AHBLT'), ITEM('IPCVO'), ITEM('HSUYA'), ITEM('DAOEC'), ITEM('DPORR'), ITEM('UDHCW'), ITEM('VCCYR'), ITEM('LOTWC'), ITEM('MUUVZ'), ITEM('EMBBE'), ITEM('HVULH'), ITEM('RCESW'), ITEM('FKEYN'), ITEM('ZDISW'), ITEM('FNJZX'), ITEM('CUATT'), ITEM('ZKDXI'), ITEM('CUFCK'), ITEM('ITEBA'), ITEM('ZTYER'), ITEM('BSPKE'), ITEM('FYOMA'), ITEM('LBPVT'), ITEM('ONYDP'), ITEM('JMCWY'), ITEM('GNDYB'), ITEM('UVTIE'), ITEM('EQBMU'), ITEM('DFHSG'), ITEM('BHRAD'), ITEM('PDLGO'), ITEM('ZYCOD'), ITEM('GSKFI'), ITEM('PGFXF'), ITEM('IQEKI'), ITEM('QNJOY'), ITEM('KDGJI'), ITEM('IVSKS'), ITEM('IWTWA'), ITEM('MVNXD'), ITEM('HQIEO'), ITEM('YUYQC'), ITEM('YJFUN'), ITEM('PBFYT'), ITEM('IOCRB'), ITEM('JCZYG'), ITEM('UUAWC'), ITEM('YBBIY'), ITEM('LTBQN'), ITEM('VLVBY'), ITEM('FDCAU'), ITEM('JALDM'), ITEM('EOTRZ'), ITEM('AHGWW'), ITEM('NNYZJ'), ITEM('VKOMD'), ITEM('IICAG'), ITEM('PDAIG'), ITEM('LFPCP'), ITEM('LUJWJ'), ITEM('RZJAY'), ITEM('PZMOH'), ITEM('MIUFM'), ITEM('KDXRI'), ITEM('HTLLY'), ITEM('DZSJG'), ITEM('FBOVD'), ITEM('GYBMV'), ITEM('XQJCL'), ITEM('XQTAH'), ITEM('UBCIV'), ITEM('QDITH'), ITEM('WEODD'), ITEM('QPSHZ'), ITEM('GBOAK'), ITEM('TQCFY'), ITEM('LTULP'), ITEM('ASREW'), ITEM('OUFAY'), ITEM('AGYUT'), ITEM('XWUOI'), ITEM('EUTKA'), ITEM('PPBNT'), ITEM('LEGVH'), ITEM('NDMNA'), ITEM('EBUZF'), ITEM('MOYAM'), ITEM('TYOFA'), ITEM('BHGGK'), ITEM('HGKHT'), ITEM('ITBHG'), ITEM('XAWJH'), ITEM('KGIJB'), ITEM('CIACY'), ITEM('LGNBN'), ITEM('XKSXH'), ITEM('YJOBN'), ITEM('SMZWW'), ITEM('BIVNJ'), ITEM('CJPGN'), ITEM('FRRVV'), ITEM('WRMAE'), ITEM('FZGXJ'), ITEM('XZUIX'), ITEM('YKIYM'), ITEM('VJXRU'), ITEM('KQKPD'), ITEM('TGGNN'), ITEM('XRYCT'), ITEM('LGVHR'), ITEM('AHAPL'), ITEM('RMASN'), ITEM('QCDLT'), ITEM('ISMDX'), ITEM('KWMGA'), ITEM('SVPTT'), ITEM('MJFSJ'), ITEM('WIXPV'), 
ITEM('HWIRY'), ITEM('QARPX'), ITEM('IRSTY'), ITEM('AZWEF'), ITEM('LZJXF'), ITEM('ZYLII'), ITEM('EZILO'), ITEM('JNMXP'), ITEM('PEGMN'), ITEM('KKEPO'), ITEM('BUXYT'), ITEM('MWGCH'), ITEM('ZFQXJ'), ITEM('WAFGV'), ITEM('QPAKR'), ITEM('ZKTCV'), ITEM('NAGFH'), ITEM('YMFSR'), ITEM('RMSON'), ITEM('ZAGSU'), ITEM('GBUPY'), ITEM('TTHSW'), ITEM('IKGJN'), ITEM('GDDDC'), ITEM('NRSYK'), ITEM('EOARJ'), ITEM('TQVXK'), ITEM('MTZNA'), ITEM('UUPZA'), ITEM('ETVRQ'), ITEM('BNYKT'), ITEM('QGJQQ'), ITEM('DNYNT'), ITEM('SXAJA'), ITEM('GXYSB'), ITEM('NWLUB'), ITEM('VQOQU'), ITEM('MHBGB'), ITEM('CXNLO'), ITEM('NNMAJ'), ITEM('FZSQJ'), ITEM('DBUVN'), ITEM('SBZFG'), ITEM('XAFPZ'), ITEM('UZYWM'), ITEM('UKMEC'), ITEM('XBLMS'), ITEM('FTQLP'), ITEM('AZCGH'), ITEM('JUNQD'), ITEM('NIMYG'), ITEM('RUDCO'), ITEM('FZWGA'), ITEM('KGTVR'), ITEM('MAFRF'), ITEM('BJTJC'), ITEM('XYURB'), ITEM('DSFQU'), ITEM('TZPHV'), ITEM('AKJZC'), ITEM('BHNVV'), ITEM('TKVRQ'), ITEM('USBWF'), ITEM('HDPLQ'), ITEM('FATLZ'), ITEM('IAPBF'), ITEM('EKAXY'), ITEM('SRENT'), ITEM('UMXSR'), ITEM('YGSWH'), ITEM('QPJSN'), ITEM('AQNLW'), ITEM('YOGSN'), ITEM('SLTBS'), ITEM('AXBJJ'), ITEM('WYZDV'), ITEM('QRFLX'), ITEM('SLRLJ'), ITEM('PPTTL'), ITEM('HFJLS'), ITEM('RGPYR'), ITEM('ZMISR'), ITEM('WHFQI'), ITEM('WHRKY'), ITEM('CBJUM'), ITEM('LEHSU'), ITEM('OQGSU'), ITEM('IRRTQ'), ITEM('KHYQA'), ITEM('XMAKV'), ITEM('GIKUM'), ITEM('DSUGK'), ITEM('UBYYK'), ITEM('RQWIC'), ITEM('MRYIP'), ITEM('CYVUX'), ITEM('HLPCP'), ITEM('BTYNA'), ITEM('SLSSO'), ITEM('UYOMI'), ITEM('AYRJE'), ITEM(NULL), ITEM('VLIQH'), ITEM('AAFVM'), ITEM('KCSXX'), ITEM('RIIAU'), ITEM('ALXXB'), ITEM('LYTMG'), ITEM('MGALF'), ITEM('IUYSW'), ITEM('FVVLL'), ITEM('RQXUJ'), ITEM('UZDHC'), ITEM('XWHKU'), ITEM('HMMWY'), ITEM('PLMWP'), ITEM('OODWG'), ITEM('MDNVW'), ITEM('UTTQN'), ITEM('OEXTP'), ITEM('DBLET'), ITEM('ELHYI'), ITEM('HFMYG'), ITEM('BMGUY'), ITEM('URBDK'), ITEM('VBQTR'), ITEM('FOOHU'), ITEM('ISPEE'), ITEM('VKSHV'), ITEM('BVFXI'), ITEM('NYOUT'), ITEM('NXMVN'), ITEM('WQWNQ'), ITEM('CN
EOK'), ITEM('GHRNG'), ITEM('PSWJH'), ITEM('EOOIM'), ITEM('YCLTQ'), ITEM('VOGAR'), ITEM('LNGMJ'), ITEM('LBNOW'), ITEM('TOGRC'), ITEM('VBAKV'), ITEM('IPZZJ'), ITEM('SITUK'), ITEM('YSETT'), ITEM('ZDYWW'), ITEM('AHCOE'), ITEM('AYNUA'), ITEM('QXVZQ'), ITEM('VGOGE'), ITEM('WZGTQ'), ITEM('MNKQY'), ITEM('RBHNK'), ITEM('NPXME'), ITEM('TZRQK'), ITEM('OOKIZ'), ITEM('DZXCP'), ITEM('QTQZV'), ITEM('ZQPBO'), ITEM('ALLXD'), ITEM('ZZNYU'), ITEM('GOUDY'), ITEM('MRKOA'), ITEM('HSBDJ'), ITEM('IONBF'), ITEM('HZDBH'), ITEM('SNKWY'), ITEM('YGMWS'), ITEM('OFIJJ'), ITEM('AXHCR'), ITEM('KUAKE'), ITEM('FHNBV'), ITEM('NGHNU'), ITEM('KPQAX'), ITEM('AHGLS'), ITEM('RPLJI'), ITEM('ZKXBS'), ITEM('XSRKZ'), ITEM('UUSEG'), ITEM('JPXZJ'), ITEM('XXOJD'), ITEM('DMPWO'), ITEM('NHXZX'), ITEM('GDAFE'), ITEM('JKKSC'), ITEM('GRWJS'), ITEM('BMIYS'), ITEM('DNKRO'), ITEM('LGZHZ'), ITEM('RAWFM'), ITEM('OZIJB'), ITEM('ZEFGN'), ITEM('VNCEM'), ITEM('GKKJO'), ITEM('UOHFM'), ITEM('IBKOH'), ITEM('TFWRB'), ITEM('CMMEZ'), ITEM('RIXTT'), ITEM('XNKSI'), ITEM('GVKMR'), ITEM('BIJTQ'), ITEM('HXLDF'), ITEM('XQORQ'), ITEM('FBTGI'), ITEM('OMUPF'), ITEM('RIFZT'), ITEM('KHFBV'), ITEM('TTJPC'), ITEM('WFNHK'), ITEM('NSLLP'), ITEM('OJXGG'), ITEM('GASZQ'), ITEM('AHTIH'), ITEM('SJIBG'), ITEM('IYYUP'), ITEM('YAMBR'), ITEM('GWPWZ'), ITEM('ROFTV'), ITEM('JTRJL'), ITEM('DYXPL'), ITEM('UAKKJ'), ITEM('EIXTH'), ITEM('XGPOM'), ITEM('ZZHOA'), ITEM('OTBTP'), ITEM('TBXRF'), ITEM('KUNLB'), ITEM('QBXUS'), ITEM('ZMMJK'), ITEM('WTRQZ'), ITEM('EMQRF'), ITEM('JULHB'), ITEM('JSSGT'), ITEM('ZWQMH'), ITEM('SYTAK'), ITEM('YPVKG'), ITEM('SGJIX'), ITEM('PEHXJ'), ITEM('RLQMZ'), ITEM('SLIZW'), ITEM('DMQKZ'), ITEM('OLGNP'), ITEM('QMRRK'), ITEM('HIWOQ'), ITEM('JOBCT'), ITEM('SRFJJ'), ITEM('VSIAQ'), ITEM('RUVOK'), ITEM('FVUPB'), ITEM('CSYJO'), ITEM('JVXBY'), ITEM('WFHPI'), ITEM('YGYVQ'), ITEM('HDMNZ'), ITEM('RORJF'), ITEM('LRTAC'), ITEM('HAAGL'), ITEM('TPNIG'), ITEM('ZTBNG'), ITEM('WCYBO'), ITEM('BJNBX'), ITEM('OGOVZ'), ITEM('ZMIQP'), ITEM('JAOJY'), ITEM('ODFOM')
, ITEM('XKUHH'), ITEM('ASDNE'), ITEM('EPHVG'), ITEM('TPZQX'), ITEM('OCCCP'), ITEM('UQBHD'), ITEM('OHXVF'), ITEM('WGHYN'), ITEM('ERXXK'), ITEM('PUEIO'), ITEM('LSIQP'), ITEM('NFLJO'), ITEM('EGLLA'), ITEM('KMODE'), ITEM('HTQNV'), ITEM('ZSWGX'), ITEM('JCUPU'), ITEM('DVDHO'), ITEM('LLOKZ'), ITEM('EBJMN'), ITEM('UBXYC'), ITEM('GRANV'), ITEM('LPODE'), ITEM('ZKGOH'), ITEM('ZXZRO'), ITEM('GXRXH'), ITEM('NMXEW'), ITEM('MJLPI'), ITEM('YGSVR'), ITEM('PKYQN'), ITEM('LAHLF'), ITEM('JIVQA'), ITEM('OEMKA'), ITEM('HBXOU'), ITEM('HJMHD'))


TANPG,GRXBG,OOFDS,EWLPP,QFOCB,UJMKH,GTVLB,TQKGQ,GDRKT,ZSYVL,MPJYT,UVXBQ,OXRWR,QHVOY,UDPHZ,JFGAX,GWBTG,ZOQID,JEIXM,YUZGH,YNBLT,MYOFT,SBUDK,UXFNA,URAWW,HBJGF,QDTBI,AJAHL,TGSCG,UZFDA,YOQGD,UVCVA,SPRYV,ACDDS,FQMFD,ALABA,HZXWM,UYOED,BBQZK,OHVGK,MFVQF,BPBGF,UCCTX,EKPNZ,EDETR,QEUAJ,LYFXQ,NBNYT,ASRGX,YGAPY,NMYKE,EVAKG,KAZMC,LQDGW,XKIFF,CCLUG,OSGZU,PCBYA,UMNYF,CEIJD,UELXD,TOAWY,LJTQG,FQCNB,LLPUU,DNMOP,FWPFE,RMVAQ,OOKRR,BREBT,EPUZX,PHYKS,IUXLD,QBFMG,VERYS,DNGYV,WRCIO,CMXDP,KVPXB,REMXD,IMISR,XTHGV,LEPNB,XWJJH,FLABE,XWWKQ,GVTGO,OOVQU,YXBTC,LSMHL,ZKYYE,MJEOB,NZCZC,ZSEJR,RVGKB,HXTNM,IWGIP,LIDLD,PCVTU,BXUYL,CKHOV,BRVRS,ZSAUK,CLLDO,DIGEK,MWDFZ,TADVA,EXYDH,EHVMY,AEGEX,PACEP,NWESB,ALVVN,ZWYTO,JPSYA,BJLLP,KMEIE,JNZZS,FYJZC,RHSKR,POWWW,QBMQB,CGQWJ,FUIVN,SCWCN,MHZQL,SYAHL,GCFUU,KQGPO,PDDHX,EZUHT,CADWN,CUKQI,ONTCE,ZEZBD,GRZGV,JLYLT,MABKI,TEAEK,SFTJQ,TCIHY,HGLGZ,XKOVY,VGKRS,XGXCJ,JXEUM,XLBOC,ITEWA,MYLZD,SAGPC,LCAGT,CMFMT,UEXIO,ASIAV,VTJCS,AZXJY,LIGVS,ROOUP,BNJUI,AZDHD,NVAZE,DCZEC,BYOTO,MVLWI,PTWJP,ETIGA,EMTJX,QMJUX,TQMII,QAEGF,JPXEN,CZUIL,DHLYL,ZPACJ,KOCJR,YKWNV,JDYDE,RPTDF,CPRGV,FPBJL,ALSIO,ANHBA,YJXGO,HZARP,FRQUQ,GTRKA,JKIGE,YDLLF,SNCAM,QXQHN,UUSAF,QRMZZ,BWSJG,CHBSA,NROUP,RIOKX,UNMCA,OBZSD,XFIEO,ZQKBE,RLHWI,JLAOP,NMZLB,BLUJY,GFGBF,CQFFR,VZAST,CXXDP,EPLRP,ZGTYY,YXQHF,WNYBN,BHAOI,HOWJF,RPJVQ,WENVD,AOCVZ,BWNRU,QYFDE,MWAOZ,EYJIT,OTVVA,AONDJ,AJWCZ,CFVPI,VCFVE,WJLEL,UDWQU,CXCEY,ZYOZG,MZFJP,NTMNP,TQEGF,MIQAA,ULTED,BFTDI,BOEBO,LWEQZ,JWMVA,HKKUZ,ATZTK,QAKDT,VVASQ,HBBLV,MRNMV,VSDOY,AFMDB,MEFMR,ESARS,AENWM,DWCIL,VOSXZ,OAQQW,XIFYD,YFTLN,FRRAR,HWAQG,MABWZ,MTGCH,BPIWR,DDTQY,QCTZM,NWOYQ,VNQRT,WUZEF,GHJTK,WVUTI,FMDKG,YNYLY,DPTTZ,UMCWV,JJAZS,AZPUF,VCBBB,CLEQF,UYPXZ,FCVWG,SFEUH,XNCAA,EJBMZ,FSEXP,AHBLT,IPCVO,HSUYA,DAOEC,DPORR,UDHCW,VCCYR,LOTWC,MUUVZ,EMBBE,HVULH,RCESW,FKEYN,ZDISW,FNJZX,CUATT,ZKDXI,CUFCK,ITEBA,ZTYER,BSPKE,FYOMA,LBPVT,ONYDP,JMCWY,GNDYB,UVTIE,EQBMU,DFHSG,BHRAD,PDLGO,ZYCOD,GSKFI,PGFXF,IQEKI,QNJOY,KDGJI,IVSKS,IWTWA,MVNXD,HQIEO,YUYQC,YJFUN,PBFYT,IOCRB,JCZYG,UUAWC,YBBIY,LTBQN,VLVBY,FDCAU,JALDM,EOTRZ,AHGWW,NNYZJ,VKOMD,IICAG,PDAIG,LFPCP,LUJWJ,RZJAY,PZMOH,MIUFM,KDXRI,HTLLY,DZSJG,FBOVD,GYBMV,XQJCL,XQTAH,UBCIV,QDITH,WEODD,QPSHZ,GBOAK,TQCFY,LTULP,ASREW,OUFAY,AGYUT,XWUOI,EUTKA,PPBNT,LEGVH,NDMNA,EBUZF,MOYAM,TYOFA,BHGGK,HGKHT,ITBHG,XAWJH,KGIJB,CIACY,LGNBN,XKSXH,YJOBN,SMZWW,BIVNJ,CJPGN,FRRVV,WRMAE,FZGXJ,XZUIX,YKIYM,VJXRU,KQKPD,TGGNN,XRYCT,LGVHR,AHAPL,RMASN,QCDLT,ISMDX,KWMGA,SVPTT,MJFSJ,WIXPV,HWIRY,QARPX,IRSTY,AZWEF,LZJXF,ZYLII,EZILO,JNMXP,PEGMN,KKEPO,BUXYT,MWGCH,ZFQXJ,WAFGV,QPAKR,ZKTCV,NAGFH,YMFSR,RMSON,ZAGSU,GBUPY,TTHSW,IKGJN,GDDDC,NRSYK,EOARJ,TQVXK,MTZNA,UUPZA,ETVRQ,BNYKT,QGJQQ,DNYNT,SXAJA,GXYSB,NWLUB,VQOQU,MHBGB,CXNLO,NNMAJ,FZSQJ,DBUVN,SBZFG,XAFPZ,UZYWM,UKMEC,XBLMS,FTQLP,AZCGH,JUNQD,NIMYG,RUDCO,FZWGA,KGTVR,MAFRF,BJTJC,XYURB,DSFQU,TZPHV,AKJZC,BHNVV,TKVRQ,USBWF,HDPLQ,FATLZ,IAPBF,EKAXY,SRENT,UMXSR,YGSWH,QPJSN,AQNLW,YOGSN,SLTBS,AXBJJ,WYZDV,QRFLX,SLRLJ,PPTTL,HFJLS,RGPYR,ZMISR,WHFQI,WHRKY,CBJUM,LEHSU,OQGSU,IRRTQ,KHYQA,XMAKV,GIKUM,DSUGK,UBYYK,RQWIC,MRYIP,CYVUX,HLPCP,BTYNA,SLSSO,UYOMI,AYRJE,
WITH sub AS ( SELECT :tmpclb as tmpclb FROM dual ) ,rec ( rn  ,srt  ,nxt  ,len  ,ele  ,liz ) AS ( SELECT 1 AS rn  ,1 AS srt  ,1 AS nxt  ,instr(tmpclb ,',' ,1 ,1) - 1 AS len  ,dbms_lob.getlength(tmpclb) - dbms_lob.getlength(REPLACE(tmpclb , ',','') ) + 1 AS ele  ,liz(item(to_clob(dbms_lob.substr(tmpclb ,instr(tmpclb ,',' ,1 ,1) - 1 ,1) ) ) ) AS liz FROM sub UNION ALL SELECT s1.rn + 1 AS rn  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) AS srt  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1 AS nxt  ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 AS len  ,s1.ele  ,s1.liz MULTISET UNION ALL liz(item(to_clob(dbms_lob.substr(s2.tmpclb ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1) ) ) ) AS liz FROM rec s1  ,sub s2 WHERE s1.rn + 1 <= s1.ele ) SELECT liz FROM ( SELECT s3.*  ,row_number() OVER( PARTITION BY s3.rn ORDER BY s3.tmpseq ) AS seq FROM ( SELECT s1.*  ,s2.*  ,ROWNUM AS tmpseq FROM rec s1  ,TABLE ( liz ) s2 ) s3 ) WHERE rn = ele AND ele = seq
VLIQH,AAFVM,KCSXX,RIIAU,ALXXB,LYTMG,MGALF,IUYSW,FVVLL,RQXUJ,UZDHC,XWHKU,HMMWY,PLMWP,OODWG,MDNVW,UTTQN,OEXTP,DBLET,ELHYI,HFMYG,BMGUY,URBDK,VBQTR,FOOHU,ISPEE,VKSHV,BVFXI,NYOUT,NXMVN,WQWNQ,CNEOK,GHRNG,PSWJH,EOOIM,YCLTQ,VOGAR,LNGMJ,LBNOW,TOGRC,VBAKV,IPZZJ,SITUK,YSETT,ZDYWW,AHCOE,AYNUA,QXVZQ,VGOGE,WZGTQ,MNKQY,RBHNK,NPXME,TZRQK,OOKIZ,DZXCP,QTQZV,ZQPBO,ALLXD,ZZNYU,GOUDY,MRKOA,HSBDJ,IONBF,HZDBH,SNKWY,YGMWS,OFIJJ,AXHCR,KUAKE,FHNBV,NGHNU,KPQAX,AHGLS,RPLJI,ZKXBS,XSRKZ,UUSEG,JPXZJ,XXOJD,DMPWO,NHXZX,GDAFE,JKKSC,GRWJS,BMIYS,DNKRO,LGZHZ,RAWFM,OZIJB,ZEFGN,VNCEM,GKKJO,UOHFM,IBKOH,TFWRB,CMMEZ,RIXTT,XNKSI,GVKMR,BIJTQ,HXLDF,XQORQ,FBTGI,OMUPF,RIFZT,KHFBV,TTJPC,WFNHK,NSLLP,OJXGG,GASZQ,AHTIH,SJIBG,IYYUP,YAMBR,GWPWZ,ROFTV,JTRJL,DYXPL,UAKKJ,EIXTH,XGPOM,ZZHOA,OTBTP,TBXRF,KUNLB,QBXUS,ZMMJK,WTRQZ,EMQRF,JULHB,JSSGT,ZWQMH,SYTAK,YPVKG,SGJIX,PEHXJ,RLQMZ,SLIZW,DMQKZ,OLGNP,QMRRK,HIWOQ,JOBCT,SRFJJ,VSIAQ,RUVOK,FVUPB,CSYJO,JVXBY,WFHPI,YGYVQ,HDMNZ,RORJF,LRTAC,HAAGL,TPNIG,ZTBNG,WCYBO,BJNBX,OGOVZ,ZMIQP,JAOJY,ODFOM,XKUHH,ASDNE,EPHVG,TPZQX,OCCCP,UQBHD,OHXVF,WGHYN,ERXXK,PUEIO,LSIQP,NFLJO,EGLLA,KMODE,HTQNV,ZSWGX,JCUPU,DVDHO,LLOKZ,EBJMN,UBXYC,GRANV,LPODE,ZKGOH,ZXZRO,GXRXH,NMXEW,MJLPI,YGSVR,PKYQN,LAHLF,JIVQA,OEMKA,HBXOU,HJMHD
WITH sub AS ( SELECT :tmpclb as tmpclb FROM dual ) ,rec ( rn  ,srt  ,nxt  ,len  ,ele  ,liz ) AS ( SELECT 1 AS rn  ,1 AS srt  ,1 AS nxt  ,instr(tmpclb ,',' ,1 ,1) - 1 AS len  ,dbms_lob.getlength(tmpclb) - dbms_lob.getlength(REPLACE(tmpclb , ',','') ) + 1 AS ele  ,liz(item(to_clob(dbms_lob.substr(tmpclb ,instr(tmpclb ,',' ,1 ,1) - 1 ,1) ) ) ) AS liz FROM sub UNION ALL SELECT s1.rn + 1 AS rn  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) AS srt  ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1 AS nxt  ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 AS len  ,s1.ele  ,s1.liz MULTISET UNION ALL liz(item(to_clob(dbms_lob.substr(s2.tmpclb ,nvl(nullif(instr(s2.tmpclb ,',' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,',' ,1 ,s1.rn) - 1 ,instr(s2.tmpclb ,',' ,1 ,s1.rn) + 1) ) ) ) AS liz FROM rec s1  ,sub s2 WHERE s1.rn + 1 <= s1.ele ) SELECT liz FROM ( SELECT s3.*  ,row_number() OVER( PARTITION BY s3.rn ORDER BY s3.tmpseq ) AS seq FROM ( SELECT s1.*  ,s2.*  ,ROWNUM AS tmpseq FROM rec s1  ,TABLE ( liz ) s2 ) s3 ) WHERE rn = ele AND ele = seq

経過時間: 00:00:42.257

あとがき

速度もちょろちょろだったぜ!pipelineにしたらいいかんじになるかな??こんど書き換えてみよう。packageはじめて書いたけど、練習になった!

ちなみにこれは帰ってこなかった。^^

with sub as(
SELECT item_liz.clbliz(clb) AS liz FROM test___clob___
)select * from sub,table(liz);

以上、ありがとうございました。

ベンチマークした

事前準備

結構本気のデータ用意した。

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ cd /mnt/18cr3/share/dump/ORCL/aine/in
[oracle@f285aba0589a in]$ ll
total 60
-rw-r--r--. 1 1000 1001 59999 Mar 23 23:49 clob.txt
[oracle@f285aba0589a in]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 23 23:51:32 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';

Directory created.

Elapsed: 00:00:00.02
SYS@pdb1> GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;

Grant succeeded.

Elapsed: 00:00:00.02
SYS@pdb1> GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

Grant succeeded.

Elapsed: 00:00:00.02
SYS@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@f285aba0589a in]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 23 23:52:37 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 23 2019 12:45:59 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> SELECT rn,dbms_lob.getlength(clb) as len FROM test___clob___;

           RN           LEN
------------- -------------
            1         59999

1 row selected.

Elapsed: 00:00:00.00

non-pipelinedのパッケージファンクション

ファンクションの引数にstream量を追加した。デバッグ用のdbms_output.put_lineは消した。

CREATE OR REPLACE TYPE item IS OBJECT (val CLOB);
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
CREATE OR REPLACE PACKAGE item_liz
IS
    FUNCTION clbliz(p_clb IN CLOB,p_srm IN INTEGER) RETURN liz;
END;
/

CREATE OR REPLACE PACKAGE BODY item_liz
IS
    FUNCTION clbliz(p_clb IN CLOB,p_srm IN INTEGER) RETURN liz
    IS
        rt liz;
        tmpliz liz;
        tmpclb CLOB;
        build_sql CLOB;
        len INTEGER;
        oft INTEGER;
    BEGIN
        rt := liz(item(to_clob(' ')));
        len := dbms_lob.getlength(p_clb);
        oft := 1;
        build_sql := to_clob('WITH sub AS ( SELECT :tmpclb as tmpclb FROM dual ) ,rec ( rn  ,srt  ,nxt  ,len  ,ele  ,liz ) AS ( SELECT 1 AS rn  ,1 AS srt  ,1 AS nxt  ,instr(tmpclb ,'||''','''||' ,1 ,1) - 1 AS len  ,dbms_lob.getlength(tmpclb) - dbms_lob.getlength(REPLACE(tmpclb , ' || ''',''' || ','''') ) + 1 AS ele  ,liz(item(to_clob(dbms_lob.substr(tmpclb ,instr(tmpclb ,' || ''','''||' ,1 ,1) - 1 ,1) ) ) ) AS liz FROM sub UNION ALL SELECT s1.rn + 1 AS rn  ,instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) AS srt  ,instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) + 1 AS nxt  ,nvl(nullif(instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) - 1 AS len  ,s1.ele  ,s1.liz MULTISET UNION ALL liz(item(to_clob(dbms_lob.substr(s2.tmpclb ,nvl(nullif(instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn + 1) ,0) ,dbms_lob.getlength(tmpclb) + 1) - instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) - 1 ,instr(s2.tmpclb ,'||''','''||' ,1 ,s1.rn) + 1) ) ) ) AS liz FROM rec s1  ,sub s2 WHERE s1.rn + 1 <= s1.ele ) SELECT liz FROM ( SELECT s3.*  ,row_number() OVER( PARTITION BY s3.rn ORDER BY s3.tmpseq ) AS seq FROM ( SELECT s1.*  ,s2.*  ,ROWNUM AS tmpseq FROM rec s1  ,TABLE ( liz ) s2 ) s3 ) WHERE rn = ele AND ele = seq');
        LOOP
            tmpliz := liz(item(to_clob(' ')));
            tmpclb := to_clob(dbms_lob.substr(p_clb, nvl(nullif(instr(p_clb,',',1,p_srm),0),p_srm), oft));
            EXECUTE IMMEDIATE build_sql INTO tmpliz USING IN tmpclb;
            rt := rt MULTISET UNION ALL tmpliz;
            oft := oft + nvl(nullif(instr(p_clb,',',1,p_srm),0),p_srm);
            EXIT WHEN oft > len;
        END LOOP;
        RETURN rt;
    END;
END;
/

で、やった。なおalter system flush shared_pool;とするとコンパイルされたプロシージャが毎回ハードパース解析されて、その時間は経過時間に含めたくない都合、バッファキャッシュのみパージ。100以外全滅したw。同時にセッション複製しまくって処理させたからかな。。むずかしい。それとdockerにもデバッグ起動で入れなくなったw。大変だこれは。100がいいんですね。

SET TIMING ON;
SET SERVEROUTPUT ON;

ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT item_liz.clbliz(clb,100) AS liz FROM test___clob___;
SELECT item_liz.clbliz(clb,200) AS liz FROM test___clob___;
SELECT item_liz.clbliz(clb,200) AS liz FROM test___clob___
SELECT item_liz.clbliz(clb,300) AS liz FROM test___clob___;
SELECT item_liz.clbliz(clb,400) AS liz FROM test___clob___;
SELECT item_liz.clbliz(clb,500) AS liz FROM test___clob___;
SELECT item_liz.clbliz(clb,1000) AS liz FROM test___clob___;
SELECT item_liz.clbliz(clb,2000) AS liz FROM test___clob___;
SELECT item_liz.clbliz(clb,3000) AS liz FROM test___clob___;

--below, common error is occurred on all parttern exclude streaming args 100.
--------------------------------------------------------------------------------
       *
ERROR at line 1:
ORA-01114: IO error writing block to file 203 (block # 598919)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 598919
Additional information: 4294967295
ORA-01114: IO error writing block to file 203 (block # 599012)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 599012
Additional information: 4294967295
ORA-01114: IO error writing block to file 203 (block # 598981)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 598981
Additional information: 4294967295
ORA-01114: IO error writing block to file 203 (block # 598950)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 598950
Additional information: 4294967295
ORA-01114: IO error writing block to file 203 (block # 598919)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 598919
Additional information: 4294967295
ORA-06512: at "AINE.ITEM_LIZ", line 19

20190917追記

仕事でCLOB型のテキストを捌く局面に出くわしたので、以下の参考文献を参照しながら、こそっとCLOB型用のファンクション作成。
CLOB型の自作集計関数に興味でてきた。カンマ区切りのテキストをいいかんじにハンドリングする技術が大事だと痛感。
またいろいろなこと知れて楽しい。初めて触るDBって分からないことだらけだから、規則性見出して、グルーピングしたいと思った時カンマ区切りは本当に重宝する。カンマ区切りにサマる時は重複排除ははずせない機能だなとしみじみ。明日さっそく使ってみよう。

ユーザ定義「集計」関数でDISTINCTや分析関数のウインドウが使えるLISTAGGを作る
ユーザー定義集計関数の使用

listagg4

CREATE OR REPLACE TYPE listagg4_typ IS OBJECT
(
s CLOB,

STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg4_typ) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate    (SELF IN OUT listagg4_typ, value IN CLOB) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate  (SELF IN     listagg4_typ, result OUT CLOB, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge      (SELF IN OUT listagg4_typ, another IN listagg4_typ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY listagg4_typ
IS

-- 初期化の処理(一番最初に実行される)
STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg4_typ) 
RETURN NUMBER IS
BEGIN
    init := listagg4_typ(''); -- タイプを初期化して返す
    return ODCIConst.Success;
END;

-- 集合化での繰り返し時の処理(新しい値と共に呼び出される)
MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT listagg4_typ, value IN CLOB)
RETURN NUMBER
IS
BEGIN
    SELF.s := NULLIF(SELF.s || ',', ',') || value; -- カンマ区切りで文字列を追加していく
    RETURN ODCIConst.Success;
END;

-- タイプの結合時の処理(多分パラレル後の結合とかで使われる)
MEMBER FUNCTION ODCIAggregateMerge (SELF IN OUT listagg4_typ, another IN listagg4_typ) RETURN NUMBER
IS
BEGIN
    SELF.s := NULLIF(SELF.s || ',', ',') || another.s; -- 2つのタイプの結合
    RETURN ODCIConst.Success;
END;

-- 集合化結果の作成(最後に呼び出される)
MEMBER FUNCTION ODCIAggregateTerminate (SELF IN listagg4_typ, result OUT CLOB, flags IN NUMBER) RETURN NUMBER
IS
BEGIN
    result := SELF.s;  -- カンマ区切り文字列は既に完成しているので値を返すだけ
    RETURN ODCIConst.Success;
END;

END;
/

CREATE OR REPLACE FUNCTION listagg4 (s CLOB)
    RETURN CLOB
    AUTHID CURRENT_USER
    PARALLEL_ENABLE
    AGGREGATE USING listagg4_typ;
/
drop table test_data purge;
create table test_data as
select
    to_clob(dbms_random.STRING('U',SYS.dbms_random.value(1,3))) as val
from
    dual
connect by
    level <=10000;

drop table test_tbl purge;
create table test_tbl as
SELECT
    listagg4(DISTINCT val) as cnm
    ,length(listagg4(DISTINCT val)) as cnm_len
    ,replace(listagg4(DISTINCT val),',','')  as non_cnm
    ,length(replace(listagg4(DISTINCT val),',',''))  as non_cnm_len
    ,length(listagg4(DISTINCT val))-length(replace(listagg4(DISTINCT val),',','')) as diff
FROM   test_data
;

select * from test_tbl;

Screenshot from 2019-09-17 22-40-46.png

listagg5

CREATE OR REPLACE TYPE listagg5_typ IS OBJECT
(
s sys.ODCIVARCHAR2LIST, -- VARCHAR2のVARRAYコレクション

STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg5_typ) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate    (SELF IN OUT listagg5_typ, value IN CLOB) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate  (SELF IN     listagg5_typ, result OUT CLOB, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge      (SELF IN OUT listagg5_typ, another IN listagg5_typ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY listagg5_typ
IS

STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg5_typ)
RETURN NUMBER IS
BEGIN
    init := listagg5_typ(sys.ODCIVARCHAR2LIST('')); -- コレクションの初期化
    return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT listagg5_typ, value IN CLOB)
RETURN NUMBER IS
BEGIN
    SELF.s.extend();               -- コレクションの拡張
    SELF.s(SELF.s.COUNT) := value; -- コレクションへ追加
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge (SELF IN OUT listagg5_typ, another IN listagg5_typ) RETURN NUMBER
IS
BEGIN
    -- 連結。VARRRYではMULTISET UNIONが使えないのでTABLEしてUNION
    SELECT CAST(COLLECT(column_value) AS sys.ODCIVARCHAR2LIST) INTO SELF.s
    FROM  (SELECT column_value FROM TABLE(SELF.s)
           UNION ALL
           SELECT column_value FROM TABLE(another.s));
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate (SELF IN listagg5_typ, result OUT CLOB, flags IN NUMBER)
RETURN NUMBER IS
BEGIN
    -- 重複を除去してLISTAGGで連結文字列を作成
    SELECT LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY column_value)
    INTO   result
    FROM  (SELECT DISTINCT column_value FROM TABLE(SELF.s));
    RETURN ODCIConst.Success;
END;

END;
/

CREATE OR REPLACE FUNCTION listagg5 (s CLOB)
    RETURN CLOB
    AUTHID CURRENT_USER
    PARALLEL_ENABLE
    AGGREGATE USING listagg5_typ;
/

20190918追記

昨日のやつは重複が排除できていなかったので、もう少し調べた。Ask TOMのコーナーで質問していた方がいた。その質問に対する解答が解決になりそうだったので、参考にした。

Convert clob to char to get distinct

テストデータ

drop table t purge;

create table t (
  c1 clob
);

insert into t values ( 'xxx' );
insert into t values ( 'xxx' );
insert into t values ( 'yyy' );
commit;

名前 Nullかどうか タイプ  
-- -------- ---- 
C1          CLOB 

USER01@ORCLPDB01> select * from t;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxx
xxx
yyy

3行が選択されました。

経過: 00:00:00.01

解決策としては3つある模様。

Substr the lob

4000バイト以下の文字列に切り取ってdistinctするやり方。

USER01@ORCLPDB01> select distinct dbms_lob.substr ( c1 , 4000, 1 ) from   t;

DBMS_LOB.SUBSTR(C1,4000,1)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
yyy
xxx

2行が選択されました。

経過: 00:00:00.00

Hash the lob

実行ユーザーにSYSユーザーから権限を付与しておくひつようがあるぽい。CLOB型文字列をハッシュ値でグルーピングして番号振り、同一グループの先頭1件を取得するようにしている。

grant execute on dbms_crypto to user01;
with rws as (
  select row_number () over ( 
           partition by sys.dbms_crypto.hash ( c1, 1 ) order by rowid
         ) rn,
         t.*
  from   t
)
  select c1 from rws
  where  rn = 1;
C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxx
yyy

2行が選択されました。

経過: 00:00:00.01

DBMS_lob.compare

番号を各行に振った後、not existsを使用して、「自身のなかに同一のCLOB型文字列を含み、自身より小さいものが存在しない行」を取得するようにしている。同一グループ内で自身が最小のCLOB文字列を取得するようにしている。グルーピングの仕方が上記のものとは切り口が違うだけで、考え方は同じ。

with rws as (
   select row_number () over ( 
            order by rowid
          ) rn,
          t.*
  from   t
)
  select * from rws r1
  where  not exists (
    select * from rws r2
    where  dbms_lob.compare ( r1.c1, r2.c1 ) = 0
    and    r1.rn > r2.rn
  );

 RN C1
--- ----------
  1 xxx
  3 yyy

2行が選択されました。

仕事現場では権限は与えられていなかったような気がするので、compareで突破しようとおもう。

20190919追記

collect関数を模倣してclob文字列に対応したcollect2関数を作成したい。TODO。
COLLECT

ukijumotahaneniarukenia
sqlが大好き。(oracle) WordPressはじめました。sql oracleやそれ以外について調べたこととか記していきます。 https://github.com/ukijumotahaneniarukenia
https://ukijumotahaneniarukenia.site/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした