まえがき
タイトルどおり不毛な遊びです。4000バイト越えのカラム文字に遭遇したらどうしようとかなぞの思いつきをしたために、春分の日が終わった話です。
以下の記事で紹介されているファンクションのほうが実用性はあると思います。
参考文献
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がなかなか大変だったけど、楽しかった！！基本的なロジックの考え方は以下の記事を参考にしました。
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って分からないことだらけだから、規則性見出して、グルーピングしたいと思った時カンマ区切りは本当に重宝する。カンマ区切りにサマる時は重複排除ははずせない機能だなとしみじみ。明日さっそく使ってみよう。
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;
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