spring-bootでOracleのIDENTITY列値の取得

たとえばoracleでは下記で連番を割り振れる。

CREATE TABLE sample_gen( 
    id number GENERATED ALWAYS AS IDENTITY
    , value VARCHAR2(100) NOT NULL
    , CONSTRAINT sample_gen_pk PRIMARY KEY (id)
);

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Data
@Entity(name = "sample_gen")
public class SampleGenEntity {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  int id;

  @Column
  String value;

}

これをJPA経由でinsertするとその連番の値も取得できる。sequence値を別途nextvalで取得してないのに連番が取れてしまう。旧来のoracleユーザからすると不思議だったので少し調べた。

  @PersistenceContext
  EntityManager em;

  @Transactional
  public void execute() {
    SampleGenEntity e = new SampleGenEntity();
    e.setValue("value");
    em.persist(e);

    System.out.println(e); 

Hibernate: insert into sample_gen (value,id) values (?,default)
SampleGenEntity(id=23, value=value)

調べた事とか

環境

• Oracle Database 23ai Free Release 23.0.0.0.0

plugins {
    id 'java'
    id 'org.springframework.boot' version '3.4.2'
    id 'io.spring.dependency-management' version '1.1.7'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'

java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(17)
    }
}

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    runtimeOnly 'com.oracle.database.jdbc:ojdbc11'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
    useJUnitPlatform()
}

prepareStatementで自動生成列名を指定

Connection#prepareStatement(String sql, String[] columnNames))という自動生成列名を指定するメソッドが存在する。これを使用するとinsertと同時に連番の値も取得できる。おそらくは内部的にこのようなロジックになっているのだと思われる。

    try (Connection c = ds.getConnection();
        PreparedStatement ps = c
            .prepareStatement(
                "insert into sample_gen(id, value) values (default, ?)",
                new String[]{"id"});) {
      ps.setString(1, "asdfasdfa111");

      ResultSet resultSet = ps.executeQuery();
      resultSet.next();
      System.out.println(resultSet.getInt(1));
    }

JdbcTemplateも同様

JdbcTemplateの場合もほぼ同様。

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

    JdbcTemplate jdbc = new JdbcTemplate(ds);
    KeyHolder keyHolder = new GeneratedKeyHolder();

    jdbc.update(connection -> {
      PreparedStatement ps = connection
          .prepareStatement(
            "insert into sample_gen(id, value) values (default, ?)",
            new String[]{"id"});
      ps.setString(1, "asdfasdsdff");
      return ps;
    }, keyHolder);

    System.out.println(keyHolder.getKey());

  }

参考；https://www.baeldung.com/spring-jdbc-autogenerated-keys

はまった点

The generated key type is not supported. Unable to cast [oracle.sql.ROWID] to [java.lang.Number].

Connection#prepareStatementにはautoGeneratedKeysというint型を渡すメソッドもあり、たとえば.prepareStatement("insert ...", Statement.RETURN_GENERATED_KEYS);のように使う。ただ、このケースでは使えないようだ。下記の通りROWIDを返そうとする。おそらくは、RDBMSだとかJDBCだとかバージョンだとかによって異なってくるのだろう（これ以上は調べてない）。

org.springframework.dao.DataRetrievalFailureException: The generated key type is not supported. Unable to cast [oracle.sql.ROWID] to [java.lang.Number].
    at org.springframework.jdbc.support.GeneratedKeyHolder.getKeyAs(GeneratedKeyHolder.java:86) ~[spring-jdbc-6.2.2.jar:6.2.2]
    at org.springframework.jdbc.support.GeneratedKeyHolder.getKey(GeneratedKeyHolder.java:65) ~[spring-jdbc-6.2.2.jar:6.2.2]

Oracle Database Advanced Queuingをさわる

https://docs.oracle.com/cd/E57425_01/121/ADQUE/aq_intro.htm

環境

• Oracle Database 23ai Free Release 23.0.0.0.0
• A5:SQL Mk-2 Version 2.20.0

docker run --name oracle_queue -p 11521:1521 -e ORACLE_PWD="Oracle23" container-registry.oracle.com/database/free:23.5.0.0-lite

説明

設定

以下の記事を参考にサンプル用のキューなどを作成していく。基本的に、各種設定用のSQLなどは以下の記事をコピペさせていただいている。

https://qiita.com/kjmtgm/items/bb31794a7e780c853e29

以下のSQLは説明のための手抜きでSYSTEMで作成する。実際には特定のユーザに権限付与したりなどをすると思われる。

メッセージ送受信用の構造型を作成する。

CREATE TYPE MYMESG AS OBJECT (
  ID NUMBER(10,0),
  MESG  VARCHAR2(30)
);

キューのためのテーブルを作成する。

begin
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'MYMESG_TAB',
    queue_payload_type => 'MYMESG',
    multiple_consumers => TRUE
  );
end;

上で作成したキュー表に対してキューを作成する。

begin
  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'MYMESG_Q',
    queue_table => 'MYMESG_TAB'
  );
end;

キューを開始する。

begin
  DBMS_AQADM.START_QUEUE (
    queue_name  => 'MYMESG_Q'
  );
end;

サブスクライバを追加する。

begin
  DBMS_AQADM.ADD_SUBSCRIBER(
    queue_name  => 'MYMESG_Q',
    subscriber  => SYS.AQ$_AGENT('SUBSCR',NULL,NULL),
    rule        => NULL
  );
END;

事前準備はこれで完了。

キューに対する操作はストアドで行う。

エンキュー（メッセージ送信）する。

DECLARE
    mesg MYMESG;
    enqueue_options     dbms_aq.enqueue_options_t;
    message_properties  dbms_aq.message_properties_t;
    message_handle      RAW(16);
BEGIN
    mesg := MYMESG( 1 ,'こんにちは' );
    dbms_aq.enqueue(queue_name         => 'MYMESG_Q',
                    enqueue_options    => enqueue_options,
                    message_properties => message_properties,
                    payload            => mesg,
                    msgid              => message_handle
    );

    mesg := MYMESG( 3 ,'今晩は' );
    dbms_aq.enqueue(queue_name         => 'MYMESG_Q',
                    enqueue_options    => enqueue_options,
                    message_properties => message_properties,
                    payload            => mesg,
                    msgid              => message_handle
    );
    COMMIT;
END;

デキュー（メッセージ受信）する。

declare
  mesg MYMESG;
  dequeue_options     DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties  dbms_aq.message_properties_t;
  message_handle      RAW(16);
begin
  DBMS_OUTPUT.ENABLE(buffer_size => NULL); 
  
  dequeue_options.CONSUMER_NAME := 'SUBSCR';
  DBMS_AQ.DEQUEUE(
    queue_name         => 'MYMESG_Q',
    dequeue_options    => dequeue_options,
    message_properties => message_properties,
    payload            => mesg,
    msgid              => message_handle
  );
  
  DBMS_OUTPUT.PUT_LINE(mesg.ID || mesg.MESG);
end;

Javaからアクセス

spring-bootアプリケーションからこのキューに対する送受信を行う。

plugins {
    id 'java'
    id 'org.springframework.boot' version '3.4.1'
    id 'io.spring.dependency-management' version '1.1.7'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'

java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(17)
    }
}

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    runtimeOnly 'com.oracle.database.jdbc:ojdbc11'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
    useJUnitPlatform()
}

以下は送信側のサンプルコード。

import java.sql.Types;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.CallableStatementCreatorFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@RequiredArgsConstructor
public class OracleStoreadQueue {

  final DataSource ds;

  @Transactional
  public void enqueue() {
    List<SqlParameter> params = List.of(
        new SqlParameter("id", Types.INTEGER),
        new SqlParameter("message", Types.VARCHAR));
    CallableStatementCreatorFactory factory = new CallableStatementCreatorFactory("""
        DECLARE
            mesg MYMESG;
            enqueue_options     dbms_aq.enqueue_options_t;
            message_properties  dbms_aq.message_properties_t;
            message_handle      RAW(16);
        BEGIN
            mesg := MYMESG(? ,?);
            dbms_aq.enqueue(queue_name         => 'MYMESG_Q',
                            enqueue_options    => enqueue_options,
                            message_properties => message_properties,
                            payload            => mesg,
                            msgid              => message_handle
            );
        END;
        """, params);
    CallableStatementCreator creator = factory.newCallableStatementCreator(
        Map.of("id", 1, "message", "Javaから送信"));
    JdbcTemplate jdbc = new JdbcTemplate(ds);

    Map<String, Object> result = jdbc.call(
        creator,
        params);
    System.out.println(result);

    jdbc.execute("insert into sample_table(sample_id, sample_value) values ('aa', 1)");
  }
}

Javaからストアドを呼び出すのがちょっとややこしい。

興味深いのは、トランザクションにメッセージ送信を含められる点。上記サンプルコードでは別テーブルへのinsertを含めておりこれは意図通りに動作する。つまり、commitが正常終了した時のみメッセージ送信とinsertの両方が成功する。rollbackすれば両方とも取り消される。

以下は受信側。

まず、ややこしいストアド呼出をラップするストアドを作成する。

CREATE OR REPLACE function dequeue_sample1
return VARCHAR2
IS
  mesg MYMESG; 
  dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T; 
  message_properties dbms_aq.message_properties_t; 
  message_handle     RAW(16); 
BEGIN
  dequeue_options.CONSUMER_NAME := 'SUBSCR'; 
  DBMS_AQ.DEQUEUE(
    queue_name         => 'MYMESG_Q',
    dequeue_options    => dequeue_options,
    message_properties => message_properties,
    payload            => mesg,
    msgid              => message_handle);
  return mesg.ID || mesg.MESG;
END dequeue_sample1;

適当なスケジューラを作成する。

import lombok.RequiredArgsConstructor;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

@Component
@RequiredArgsConstructor
public class SampleScheduledComponent {

  final DequeueSample service;

  @Scheduled(fixedRate = 1000)
  public void execute() {
    service.dequeue();
  }
}

受信の本体部分。

package expr.app.oraclequeur;

import javax.sql.DataSource;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

@Component
@RequiredArgsConstructor
public class DequeueSample {

  final DataSource ds;

  @Transactional
  public void dequeue() {
    SimpleJdbcCall stored = new SimpleJdbcCall(ds)
        .withSchemaName("SYSTEM")
        .withFunctionName("dequeue_sample1");

    String message = stored.executeFunction(String.class);
    System.out.println("de-queue:" + message);

    JdbcTemplate jdbc = new JdbcTemplate(ds);
    jdbc.execute("insert into sample_table(sample_id, sample_value) values ('aa', 1)");

  }
}

上で作成したラップのストアドを呼び出してメッセージを受信する。こちらにもトランザクションのサンプルコードとしてinsertを含めている。これも意図通りの挙動になる。

その他

感想とか

アプリケーション観点から見て特筆すべきは、やはり、単一トランザクションにDB更新とメッセージ送信を含められる点だろう。何故かというとoutboxパターン（transactional-outbox）とかいうのがある程度にはRDB + MQはそれなりに頭の痛い課題である。この問題をOracleに丸投げ出来るのであればそれなりに魅力的である。

ただ、何もかもOracleに集中するのもまた問題のように思われる。すべての負荷がOracleに集中するのは問題になりそうだとか、専用のメッセージングミドルウェアの方がおそらく性能は出るだろうなぁとか。

あと、インターネットにはあんま情報が無い……いまどきストアドに馴染みのある開発者も少ないだろうし、若干の敷居の高さが無いでは無い。

とはいえ、メッセージングのあれこれをOracleだけで完結＆丸投げ出来るのは良い。

com.oracle.database.messaging:aqapiは使わなくて良い？

https://mvnrepository.com/artifact/com.oracle.database.messaging/aqapi

このキューにアクセスするためと思しきライブラリが存在するが、基本的にはストアド実行で十分と思われるので、使わなくても良いのではなかろうか。また、Oracle's implementation of JMS specification in compliance with JMS 1.1 という記述もあり、JMSの場合に使うものなのかな？　という気がする。

ハマった点

ORA-24033: no recipients for message

サブスクライバを登録してからでないとエンキューは出来ない。他のメッセージングミドルウェアと比較するとちょっと融通が利かないような気もする。Oracle内臓だから仕方ないのだろうか？

https://docs.oracle.com/cd/E16338_01/server.112/b61355/aq_trbl.htm#i1005947

参考URL

• https://qiita.com/kjmtgm/items/bb31794a7e780c853e29
• DBMS_AQADM https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_aqadm.htm
• DBMS_AQ https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_aq.htm#i1001648
• https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/advanced-queuing-AQ-types.html
• https://docs.oracle.com/cd/E16338_01/server.112/b61355/aq_trbl.htm

springのSimpleJdbcCallでストアドプロシージャを呼び出す

springのDBアクセスにはJdbcTemplateが従来から存在しており、現在では後継のJdbcClientも利用可能である。ストアドプロシージャにはSimpleJdbcCallを使用する。

本エントリの注意点として、私はあまりOracleのストアドプロシージャに詳しくない。そのため用語を正しく使えていない可能性がある。

環境

• Oracle Database 23ai Free Release 23.0.0.0.0

docker run --name oracle_queue -p 11521:1521 -e ORACLE_PWD="Oracle23" container-registry.oracle.com/database/free:23.5.0.0-lite

plugins {
    id 'java'
    id 'org.springframework.boot' version '3.4.1'
    id 'io.spring.dependency-management' version '1.1.7'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'

java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(17)
    }
}

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    runtimeOnly 'com.oracle.database.jdbc:ojdbc11'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
    useJUnitPlatform()
}

以降のJavaのサンプルコードにはimportやクラス宣言などは省略するが、おおよそ以下のようになっている。

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

@Service
@RequiredArgsConstructor
public class StoredSample {

  final DataSource ds;

また、ストアドプロシージャは説明のための手抜きでSYSTEMで作成する。

説明

PROCEDURE - 引数無し・戻り値無し

動作確認用に以下のPROCEDUREを作成する。

CREATE OR REPLACE PROCEDURE simple_proc
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('');
END;

  public void simpleProc() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("SYSTEM")
        .withProcedureName("simple_proc");

    Map<String, Object> result = jdbc.execute();
    System.out.println(result);
  }

実行結果は {} と出力される。

withSchemaNameでそのプロシージャが存在するスキーマを指定し、withProcedureNameでプロシージャの名前を指定し、executeで実行する。

SYSTEM.simple_procのようには指定出来ない。戻り値が何もない場合は単に空のmapが返される。

PROCEDURE - 引数有り・戻り値無し

以下は意味の無いコードだが、動作確認としてOracleのDBMS_OUTPUT.PUT_LINEを呼び出す。

  public void putLine() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("DBMS_OUTPUT")
        .withProcedureName("PUT_LINE")
        .withoutProcedureColumnMetaDataAccess()
        .declareParameters(
            new SqlParameter("inputString", Types.VARCHAR)
        );

    Map<String, Object> result = jdbc.execute("asdf");
    System.out.println(result);

    MapSqlParameterSource s = new MapSqlParameterSource();
    s.addValue("inputString", "asdfasdasd");

    String result1 = jdbc.executeFunction(String.class, s);
    System.out.println(result1);
  }

declareParametersで呼び出すストアドの引数を宣言する。SqlParameterのkeyはspring側での識別名なので必ずしもストアドと一致する必要は無いらしい。

executeで引数を指定して実行する。

または、executeFunctionとSqlParameterSourceの組み合わせも可能。こちらの場合keyはSqlParameterのと一致の必要がある。

実行結果は以下の通り。executeFunctionは戻り値型を指定可能だが、存在しなければnullになるようだ。

{}
null

FUNCTION - 引数有り・戻り値有り・複数の出力パラメータ

サンプルとして以下のfunctionを使用する。

CREATE OR REPLACE function sample_func3( 
    inStr1 IN nvarchar2
    , outStr OUT VARCHAR2
    , outInt OUT NUMBER
)
return VARCHAR2
AS
BEGIN
  outStr := inStr1 || 'hoge';
  outInt := 123;
  return 'returnStr';
END sample_func3;

ここでは「戻り値」はreturnの値、「出力パラメータ」はOUTを指すものとする（oracleの正確な用語かどうかは自身無し）。

  public void sampleFunc3() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("SYSTEM")
        .withFunctionName("sample_func3")
        .declareParameters(
            new SqlParameter("inputString", Types.VARCHAR));

    Map<String, Object> result = jdbc.execute("asdf");
    System.out.println(result);

    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("INSTR1", "asdfasdasd");
    String result1 = jdbc.executeFunction(String.class, param);
    System.out.println(result1);
  }

実行結果は以下の通り。

{return=returnStr, OUTSTR=asdfhoge, OUTINT=123}
returnStr

functionなのでwithFunctionNameを使用する。

実行結果の通り、executeの戻り値Mapにはストアドの戻り値プラス出力パラメータの両方を含む。対してexecuteFunctionの戻り値はストアドの戻り値になるようだ。

execute

このメソッドのパラメータの型の違いによる違いはほぼ見たまま。

  public void sampleFunc3() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("SYSTEM")
        .withFunctionName("sample_func3");

    jdbc.execute("asdf");
    jdbc.execute(Map.of("INSTR1", "asdf") );

    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("INSTR1", "asdfasdasd");
    jdbc.execute(param);

• Object...はthe same order as the parameters are defined for the stored procedureとの事なのでストアドの定義順。
• Map<String,?>はthe parameter values to be used in the callとの事なのでkey名で指定。
• SqlParameterSourceは専用クラスなだけでMapとほぼ同等と思われる。

補足

自ユーザ以外所有のストアド実行にはwithoutProcedureColumnMetaDataAccessが必要

https://vkuzel.com/calling-oracle-function-with-spring-5-3-simple-jdbc-tools に解説がある。

If a function is located in another schema and only a synonym is available in local schema, we have to disable Spring's metadata resolution via the SimpleJdbcCall.withoutProcedureColumnMetaDataAccess() method.

The reason is, Spring resolves metadata of a called objects from all_procedures and all_arguments tables. While doing so, it checks whether owner of procedure or argument is equal to the current schema which is resolved from connection, e.g. all_arguments.owner = 'local_schema_name' AND all_procedures.owner = 'local_schema_name'.

Because the real owner is located elsewhere, the metadata resolution fails on the SQLException: Missing IN or OUT parameter at index:: 1 error.

https://vkuzel.com/calling-oracle-function-with-spring-5-3-simple-jdbc-tools Calling a function synonym より抜粋

springはRDBMSのメタデータを確認してストアド呼出を組み立てる。具体的にはOracleではall_argumentsã‚„ all_proceduresを参照するらしい。それで、自分がオーナーでは無いストアドだとメタデータ参照に失敗してしまい、エラーになるらしい。したがって、それを無視する設定withoutProcedureColumnMetaDataAccessが必要となる、とのこと。

declareParametersは必ずしも必要では無い？

ちょっと調べ切れなかったのだが、該当メソッドのjavadocには以下のような記述がある。多分だけど、メタデータだとか使い方だとか、その辺の兼ね合いによっては明示的なパラメータ宣言は不要なのだと思う。

public SimpleJdbcCall declareParameters(SqlParameter... sqlParameters)

Specify one or more parameters if desired. These parameters will be supplemented with any parameter information retrieved from the database meta-data. https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/simple/SimpleJdbcCall.html#declareParameters(org.springframework.jdbc.core.SqlParameter...)

OBJECT型とかは使えない？

調べた限りでは以下のようなOBJECT型をパラメータや戻り値に使用するストアドはSimpleJdbcCallからは呼び出せないように見える。呼び出し方も分からなかったし、OBJECT型とJavaのマッピング方法も分からなかった。

CREATE TYPE MYMESG AS OBJECT (
  ID    NUMBER(10,0),
  MESG  VARCHAR2(30)
);

CREATE OR REPLACE function sample_func5 return SYSTEM.MYMESG
IS
  msg SYSTEM.MYMESG;
BEGIN
 msg := SYSTEM.MYMESG(1, 'asd');
 return msg;
END sample_func5;

JdbcTemplateでは以下のように呼び出すらしい。ただ、呼び出せる事は出来たが構造体のマッピング方法は分からなかった。なので、以下のサンプルコードは構造体を直接ではなくその中身を取り出してから、にしている。

import java.sql.Types;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.CallableStatementCreatorFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class StoreadSample2 {

  final DataSource ds;

  public void sampleFunc5() {
    CallableStatementCreatorFactory factory = new CallableStatementCreatorFactory("""
        declare
         msg SYSTEM.MYMESG;
        begin
         ? := SYSTEM.sample_func5().MESG;
        end;
        """, List.of(new SqlOutParameter("result", Types.VARCHAR)));
    CallableStatementCreator creator = factory.newCallableStatementCreator(Map.of());
    JdbcTemplate jdbc = new JdbcTemplate(ds);

    Map<String, Object> result = jdbc.call(
        creator,
        List.of(new SqlOutParameter("result", Types.VARCHAR)));
    System.out.println(result);
  }
}

ハマった点

Unable to determine the correct call signature for 'SYSTEM.SIMPLE_PROC'

.withProcedureName("SYSTEM.simple_proc") のようにスキーマとプロシージャ名を一緒に指定するとこのエラーになる。

org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature for 'SYSTEM.SIMPLE_PROC' - package name should be specified separately using '.withCatalogName("SYSTEM")'

Missing IN or OUT parameter at index: 1とかwrong number or types of arguments in call to

withoutProcedureColumnMetaDataAccessが必要。詳細な理由は補足の項を参照。

以下のエラーログはDBMS_OUTPUT.PUT_LINEã‚’withProcedureNameで呼び出した場合。

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call DBMS_OUTPUT.PUT_LINE()}]
...
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

以下のエラーログはwithFunctionNameで呼び出した場合。おそらく、メタデータ参照に失敗し、さらにfunctionなので存在しない戻り値にプレースホルダを自動的に割り当ててしまっている。そのため、withProcedureNameとは異なるエラーになる、と推測する。

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call DBMS_OUTPUT.PUT_LINE()}]; SQL state [99999]; error code [17041]; ORA-17041: Missing IN or OUT parameter at index: 1
https://docs.oracle.com/error-help/db/ora-17041/
...
Caused by: java.sql.SQLException: ORA-17041: Missing IN or OUT parameter at index: 1

BadSqlGrammarException (ç•¥) bad SQL grammar [{call SYSTEM.SAMPLE_FUNC3(?, ?, ?, ?)}]

withFunctionNameとすべきところをwithProcedureNameにしてしまうと以下のようなエラーになる。

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call SYSTEM.SAMPLE_FUNC3(?, ?, ?, ?)}]
..
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SAMPLE_FUNC3'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

参考URL

• https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/simple/SimpleJdbcCall.html
• https://docs.spring.io/spring-framework/reference/data-access/jdbc/simple.html
• https://vkuzel.com/calling-oracle-function-with-spring-5-3-simple-jdbc-tools