LOB型の削除に関して

参考文献

How to drop Oracle LOB  

今の状況

拙作スキーマクリーニングスクリプトですとLOBオブジェクトが残っている。。消さなくてもいいと思うけど、気になったから、調べた。

EXECUTE IMMEDIATE をはじめて書いた。スキーマ内クリーニングスクリプト  

結論

パージすればいい

コード表示

purge recyclebin;

動的にファンクションの呼び出しを制御する話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

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

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

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

ファンクション2つ適当に作る

クエリ

コード表示

create or replace function func___01___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 10;
end;
/

create or replace function func___02___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 100;
end;
/

select FUNC___01___(10) as rt from dual;
select FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.00

ファンクション呼び出しファンクション

クエリ

コード表示


create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.01

パッケージにくるんでみると、パッケージ内での処理を気にせず、呼び出し側はcallできる。

クエリ

コード表示

create or replace package func___pkg___ is
	function func___01___(p_num number) return number;
	function func___02___(p_num number) return number;
end;
/
create or replace package body func___pkg___ is
	procedure func___fmt___(mtd varchar2) as
	begin
		DBMS_OUTPUT.PUT_LINE(mtd||'is called');
		return;
	end;
	function func___01___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___01___');
		return rt * 10;
	end;
	function func___02___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___02___');
		return rt * 100;
	end;
end;
/
select FUNC___PKG___.FUNC___01___(10) as rt from dual;
select FUNC___PKG___.FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___PKG___.FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___PKG___.FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

パッケージの文言を追加しただけ。

クエリ

コード表示

create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '|| 'func___pkg___.' ||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/
select func___invocker___(1,10) as rt from dual;
select func___invocker___(2,10) as rt from dual;

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

collect関数でmapが作成できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

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

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

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

タイプ作る

2つ今回は。

コード表示

drop type mp force;
/
drop type liz force;
/

create or replace type mp is object(ky clob,vl clob);
/
create or replace type liz is table of mp;
/


はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select * from sub;

           RN KY           VL
------------- ------------ ----
            0 001          A
            0 002          B
            0 003          C
            1 001          A
            1 002          B
            1 003          C
            1 004          D

7 rows selected.

Elapsed: 00:00:00.00

マップオブジェクトできた

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select rn,mp(ky,vl) as mp from sub;

実行例

コード表示

AINE@pdb1> col mp for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select rn,mp(ky,vl) as mp from sub;

           RN MP(KY, VL)
------------- ----------------------------------------------------------------------------------------------------
            0 MP('001', 'A')
            0 MP('002', 'B')
            0 MP('003', 'C')
            1 MP('001', 'A')
            1 MP('002', 'B')
            1 MP('003', 'C')
            1 MP('004', 'D')

7 rows selected.

Elapsed: 00:00:00.01

さまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select rn,cast(collect(mp(ky,vl)) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select rn,cast(collect(mp(ky,vl)) as liz) as liz from sub group by rn;

           RN LIZ(KY, VL)
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(MP('001', 'A'), MP('003', 'C'), MP('002', 'B'))
            1 LIZ(MP('001', 'A'), MP('004', 'D'), MP('003', 'C'), MP('002', 'B'))

2 rows selected.

Elapsed: 00:00:00.00

collect関数で入れ子できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

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

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

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

タイプ作る

3つ今回は。

コード表示

drop type val force;
/
drop type item force;
/
drop type liz force;
/

create or replace type val is object(val clob);
/
create or replace type item is object(rn number,vl val);
/
create or replace type liz is table of item;
/

はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select * from sub;

           RN STR
------------- ------------
            0 001
            0 002
            0 003
            1 001
            1 002
            1 003
            1 004

7 rows selected.

Elapsed: 00:00:00.00

strをオブジェクト型に変換

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,val(str) as val from sub;

実行例

コード表示

AINE@pdb1> col val for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,val(str) as val from sub;

           RN VAL(VAL)
------------- --------------------------------------------------
            0 VAL('001')
            0 VAL('002')
            0 VAL('003')
            1 VAL('001')
            1 VAL('002')
            1 VAL('003')
            1 VAL('004')

7 rows selected.

Elapsed: 00:00:00.01

mapみたいにitemでラップ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select item(rn,val(str)) as item from sub;

実行例

コード表示

AINE@pdb1> col item for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select item(rn,val(str)) as item from sub;

ITEM(RN, VL(VAL))
--------------------------------------------------
ITEM(0, VAL('001'))
ITEM(0, VAL('002'))
ITEM(0, VAL('003'))
ITEM(1, VAL('001'))
ITEM(1, VAL('002'))
ITEM(1, VAL('003'))
ITEM(1, VAL('004'))

7 rows selected.

Elapsed: 00:00:00.01

最後はさまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

           RN LIZ(RN, VL(VAL))
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('003')), ITEM(0, VAL('002')))
            1 LIZ(ITEM(1, VAL('001')), ITEM(1, VAL('004')), ITEM(1, VAL('003')), ITEM(1, VAL('002')))

2 rows selected.

Elapsed: 00:00:00.01

サマル単位を指定しないと

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select cast(collect(item(rn,val(str))) as liz)  as liz from sub;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select cast(collect(item(rn,val(str))) as liz) as liz from sub;

LIZ(RN, VL(VAL))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('002')), ITEM(0, VAL('003')), ITEM(1, VAL('001')), ITEM(1, VAL('002')), ITEM(1, VAL('003')), ITEM(1, VAL('004')))

1 row selected.

Elapsed: 00:00:00.01

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