JDBC/PLSQL: How can I get a return value of a anonymous block-CodePudding

I have an anonymous code-block.

String plsql = "DECLARE\n";
plsql  = "\tret NUMBER;\n";
plsql  = "\tretsum NUMBER := 0;\n";
plsql  = "BEGIN\n";
plsql  = "\tret := 1;\n";
plsql  = "\tretsum := ret   retsum;\n";
plsql  = "END;\n";

I call it by JDBC.

CallableStatement cs = con.prepareCall (plsql);
boolean ret = cs.execute ();
cs.close ();

All if working so far.

How can I make my anonymous block to return a value (e.g. retsum) into my Java-code?

I am aware of stored functions and to handle the return there - but I can not do that here.

CallableStatement cst = con.prepareCall ("{? = call foo ();}");
cst.registerOutParameter (1, Types.INTEGER);
cst.execute ();
int ret = cst.getInt (1);

CodePudding user response：

Use a bind variable (either ?, for an anonymous variable, or :something, for a named variable) in the anonymous block:

String plsql = "DECLARE
  ret NUMBER;
  retsum NUMBER := 0;
BEGIN
  ret := 1;
  retsum := ret   retsum;
  ? := retsum;
END;";

CallableStatement cst = con.prepareCall (plsql);
cst.registerOutParameter (1, Types.INTEGER);
cst.execute ();
int ret = cst.getInt (1);

CodePudding user response：

Another possible way available since Oracle 12c is to declare inline function and use general select statement:

with function f
return number
as
  ret NUMBER;
  retsum NUMBER := 0;
BEGIN
  ret := 1;
  retsum := ret   retsum;
  return retsum;
END;

select f() as res
from dual

| RES |
| --: |
|   1 |

db<>fiddle here