clob型をcollectionで返却する

参考文献

現在のところ、PL/SQLのブロック、サブプログラムまたはパッケージ内ではオブジェクト型を定義できません。

PL/SQLのオブジェクト型の使用  
 

LOB属性を含むオブジェクト型を作成してから、そのオブジェクト型を基にネストした表を作成する必要があります。

LOBに対するDDL文とDML文  
 

PLS-00642: SQL文ではローカル・コレクション型は使用できません。

コレクション型をスキーマ・レベルで定義している(CREATE TYPEで定義している)場合。コレクション型をローカル・レベルで定義している(PL/SQLブロック内で定義している)場合。基本的にスキーマレベルで宣言しておくと、エラーとか出ずに済むはず。スキーマがタイプだらけになるかもだけど。。

単一sqlで使う場合

クエリ

コード表示

SET SERVEROUTPUT ON

CREATE OR REPLACE TYPE item IS OBJECT (val CLOB);
/

CREATE OR REPLACE TYPE liz IS TABLE OF item;
/

SELECT CAST(COLLECT(item('CREATE SESSION,CREATE TABLE,')) AS liz) as liz FROM dual;

実行例

コード表示

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

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

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

Last Successful login time: Thu Mar 21 2019 13:17:23 +09:00

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

AINE@pdb1> col liz for a170
AINE@pdb1> SELECT CAST(COLLECT(item('CREATE SESSION,CREATE TABLE,')) AS liz) as liz FROM dual;

LIZ(VAL)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM('CREATE SESSION,CREATE TABLE,'))

1 row selected.

Elapsed: 00:00:00.02

パッケージでファンクションくるんで使用する場合

クエリ

コード表示

SET SERVEROUTPUT ON

CREATE OR REPLACE TYPE item IS OBJECT (val CLOB);
/

CREATE OR REPLACE TYPE liz IS TABLE OF item;
/

CREATE OR REPLACE PACKAGE test___clbliz
IS
	FUNCTION clbliz(p_clb IN CLOB) RETURN liz;
END;
/
CREATE OR REPLACE PACKAGE BODY test___clbliz
IS
	FUNCTION clbliz(p_clb IN CLOB) RETURN liz
	IS
		rt liz;
	BEGIN
		SELECT CAST(COLLECT(item(p_clb)) AS liz) INTO rt FROM dual;
		RETURN rt;
	END;
END;
/

SELECT test___clbliz.clbliz(to_clob('CREATE SESSION,CREATE TABLE,')) as liz FROM dual;

実行例

コード表示

AINE@pdb1> SELECT test___clbliz.clbliz(to_clob('CREATE SESSION,CREATE TABLE,')) as liz FROM dual;

LIZ(VAL)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM('CREATE SESSION,CREATE TABLE,'))

1 row selected.

Elapsed: 00:00:00.01