[cx_Oracle入門](第18回) PL/SQL特有型のハンドリング

連載目次

連載：cx_Oracle入門　目次

検証環境

• Oracle Cloud利用
• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.8
• cx_Oracle 8.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

はじめに

本記事はJPOUG Advent Calendar 2020の12/3の記事となります。
PL/SQLはコレクション型やレコード型などの独自のデータ型が存在します。これらを引数や戻り値で使用するPL/SQLアプリケーションとPythonアプリケーションのやり取りをどのように行うかを解説します。

PL/SQL表のハンドリング

サンプルPL/SQLアプリケーション

sample18a.sql

create or replace package sample18a as
  type vc2_type_tbl is table of varchar2(200) index by pls_integer;
end;
/

create or replace procedure sample18b
  (tbl in out nocopy sample18a.vc2_type_tbl) is
begin
  for i in tbl.first..tbl.last loop
    tbl(i) := tbl(i) || 'は素晴らしい製品です';
  end loop;
end;
/

VARCHAR2のPL/SQL表をインプットとし、文字列を付け足して同じPL/SQL表を返すだけのシンプルな内容です。
cx_Oracleに限った話ではありませんが、PL/SQL特有型を各言語のSQLアクセス・ドライバを通じてやり取りするためには、パッケージ定義部でPL/SQL特有型を定義する必要があります。
サンプルのように、プロシージャやファンクションは必ずしも同一パッケージの本体部で定義せねばならないわけではありません。
更に、このサンプルでは敢えて定義していますが、DBMS_SQL.VARCHAR2_TABLE表など、既存で組み込まれているデータ型を流用できる場合は、それらを利用することで、パッケージ定義も不要にすることができます。

サンプルPythonアプリケーション

sample18c.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"

inout_list = ["Oracle Database", "Oracle WebLogic Server", "Oracle GoldenGate"]
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as conn:
    with conn.cursor() as cur:
        plsql_buf = cur.arrayvar(str, 10, 200)
        plsql_buf.setvalue(0, inout_list)
        cur.callproc("sample18b", [plsql_buf])
[print(s) for s in plsql_buf.getvalue()]

実行結果

$ python sample18c.py
Oracle Databaseは素晴らしい製品です
Oracle WebLogic Serverは素晴らしい製品です
Oracle GoldenGateは素晴らしい製品です

下から4行目の
cur.arrayvar(str, 10, 200)
は、cx_OracleがPL/SQL表とやり取りするための配列オブジェクトを作成するメソッドとなります。
1番目の引数はPythonサイドのデータ型を、2番目の引数は想定される件数の最大値、3番目の引数は想定される1行の長さを指定します。
指定を超えるサイズのやり取りを行おうとするとエラーになります。
下から3行目の
plsql_buf.setvalue(0, inout_list)
で、インプットとなるリストと配列オブジェクトの紐づけを行っています。今回は変数が一つだけなので、変数位置を指定する第一引数は0になります。
あとは普通にプロシージャを呼び出し、戻り値を取得するだけです。

PL/SQLレコードのハンドリング

サンプルPL/SQLアプリケーション

sample18d.sql

create or replace package sample18d as
  type sample18d_rec is record (
    col1 number(5),
    col2 varchar2(100),
    col3 date
  );
end;
/

create or replace procedure sample18e
  (rec in out nocopy sample18d.sample18d_rec) is
begin
  rec.col1 := rec.col1 * 3;
  rec.col2 := concat('Oracle ', rec.col2);
  rec.col3 := rec.col3 + 1;
end;
/

レコード型を使用するPL/SQLアプリケーションも、コレクション型を使用する場合と同じく、パッケージ定義部による型定義が必要です。

サンプルPythonアプリケーション

sample18f.py

from datetime import datetime
import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as conn:
    with conn.cursor() as cur:
        record_type = conn.gettype("SAMPLE18D.SAMPLE18D_REC")
        record = record_type.newobject()
        record.COL1 = 3
        record.COL2 = "Database"
        record.COL3 = datetime(2020, 1, 1)
        print(f"プロシージャ実行前 : {record.COL1}, {record.COL2}, {record.COL3}")
        cur.callproc("sample18e", [record])
        print(f"プロシージャ実行後 : {record.COL1}, {record.COL2}, {record.COL3}")

実行結果

$ python sample18f.py
プロシージャ実行前 : 3, Database, 2020-01-01 00:00:00
プロシージャ実行後 : 9, Oracle Database, 2020-01-02 00:00:00

Connection.gettype()でレコード型の情報を取得
→レコード型のオブジェクトを作成
→各列の値を設定
という流れになります。
気を付けないといけないのは、レコード型関連の名称は大文字、小文字を合わせる必要がある点です。意図して(""で囲んで)小文字のテーブルや大文字、小文字混在の名称を使用しない限りは大文字で指定することになります。