私はデータ分析ツールを使っていますが、ユーザーから値を受け取り、それをパラメータとして渡し、テーブルに保存することが要件でした。かなり単純なので、私はこれを書くために座っていました。
create or replace
procedure complex(datainput in VARCHAR2)
is
begin
insert into dumtab values (datainput);
end complex;
これをSQL Developerで、次のようなステートメントを使って実行しました。
begin
complex('SomeValue');
end;
問題なく動作し、テーブルに値が挿入されました。しかし、上記のステートメントはデータ分析ツールではサポートされていないため、代わりに関数を使用することに頼りました。以下はその関数のコードですが、コンパイルはできています。
create or replace
function supercomplex(datainput in VARCHAR2)
return varchar2
is
begin
insert into dumtab values (datainput);
return 'done';
end supercomplex;
もう一度、SQL Developerで実行してみましたが、以下のコードを実行すると、cannot perform a DML operation inside a queryと表示されました。
select supercomplex('somevalue') from dual;
私の質問は
P.S. これがC++かJavaだったらどんなによかったか :(
EDIT
DMine(ツール)で実行する前に、SQL Developer上で関数を実行して、それが有効かどうかをテストする必要があるからです。SQLで無効なものはDMineでも無効ですが、その逆はありません。
ありがとうございました。状況を理解し、なぜそれが違法/推奨されないのかがわかりました。
pragma autonomous_transaction` というディレクティブを使用することができます。これは、ORA-14551 を発生させずに DML を実行することができる独立したトランザクションの中で関数を実行します。
自律トランザクション]1は独立しているので、DMLの結果は親トランザクションの範囲外でコミットされることに注意してください。ほとんどの場合、これは許容できる回避策ではありません。
SQL> CREATE OR REPLACE FUNCTION supercomplex(datainput IN VARCHAR2)
2 RETURN VARCHAR2 IS
3 PRAGMA AUTONOMOUS_TRANSACTION;
4 BEGIN
5 INSERT INTO dumtab VALUES (datainput);
6 COMMIT;
7 RETURN 'done';
8 END supercomplex;
9 /
Function created
SQL> SELECT supercomplex('somevalue') FROM dual;
SUPERCOMPLEX('SOMEVALUE')
--------------------------------------------------------------------------------
done
SQL> select * from dumtab;
A
--------------------------------------------------------------------------------
somevalue
そもそもなぜこのエラーが発生するのかについては、Tom Kyte has a nice explanation があります。行の処理順序に依存する可能性があるため、安全とは言えません。さらに、Oracle はこの関数が行ごとに少なくとも1回、多くても1回実行されることを保証していません。
例えば戻り値を受け取るための変数を宣言するだけです。
declare
retvar varchar2(4);
begin
retvar := supercomplex('somevalue');
end;
selectが機能しないのは、関数が挿入を実行しているからです。