1

I got this Oracle stored procedure code calling another stored procedure:

PROCEDURE xyz_main (
  i_param_a IN VARCHAR2 DEFAULT 'Y',
  i_param_b IN PERSON.NAME%TYPE
  i_param_c IN VARCHAR2 DEFAULT 'BLA'
  o_return_msg OUT VARCHAR2
)
IS
  ...
BEGIN
  ...
END xyz_main;

PROCEDURE xyz_example (
  i_param_b IN PERSON.NAME%TYPE DEFAULT 'Ben'
  o_return_msg OUT VARCHAR2
)
IS
BEGIN
  xyz_main(
    i_param_b => i_param_b,
    i_param_c => 'Great',
    o_return_msg => o_return_msg
  );
END xyz_example;

Now my task is to call xyz_main by Java. I wrote this code:

// Using question mark because later on the values will be provided by function parameters
// Question: is "o_return_msg => ?" correct, even if I don't provide an input value?
String statement = "BEGIN xyz_main(i_param_b => ?, i_param_c => ?, o_return_msg => ?); END;";  

try (Connection conn = getOraConnection();) {
  CallableStatement cs = conn.prepareCall(statement);

  // cs.setString("i_param_a", "Y"); -- omitted because the default value should be used always
  cs.setString("i_param_b", "Ben");
  cs.setString("i_param_c", "Great"); // should overwrite the default value
  cs.registerOutParameter("o_return_msg", Types.VARCHAR);

  cs.execute();
} catch (Exception e) {
  ...
}

Running the code I get this Exception:

ORA-06550: line 1, column 47:
PLS-00103: Fand das Symbol ">" als eines der folgenden erwartet wurde: ..

In English:

PLS-00103: Found Symbol ">" but expected one of this: ..

What's wrong with this code?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Rainer
  • 1,067
  • 2
  • 14
  • 29
  • Does `BEGIN xyz_main(?, ?); END` work? – wolφi May 07 '20 at 21:06
  • That's what my colleague recommended. I tried e.g. "{CALL xyz_main()}" as well .. same error. – Rainer May 07 '20 at 21:10
  • Maybe your question was whether it works with indexed parameters .. yes this works. But then I cannot ommit parameters. – Rainer May 07 '20 at 21:11
  • Does [this](https://stackoverflow.com/questions/3699891/java-named-parameters-name-for-oracle-jdbc-function-result/3700334#3700334) answer your question? He seems to use names for the prepareCall, but indexes for setting the values. [Christoph](https://www.akadia.com/services/ora_jdbc_parameter.html) too. – wolφi May 07 '20 at 21:33

1 Answers1

1

I was assuming when using named parameters I always have to use the syntax 'par_name => ?'. This was the mistake.

It's no problem to write code like this:

//* don't work:
// String statement = "BEGIN xyz_main(i_param_b => ?, i_param_c => ?, o_return_msg => ?); END;";  // don't work
//* works:
String statement = "BEGIN xyz_main(?, ?, ?); END;"; 

try (Connection conn = getOraConnection();) {
  CallableStatement cs = conn.prepareCall(statement);
  // cs.setString("i_param_a", "Y"); -- omitted because the default value should be used always
  cs.setString("i_param_b", "Ben");
  cs.setString("i_param_c", "Great"); // should overwrite the default value
  cs.registerOutParameter("o_return_msg", Types.VARCHAR);
  cs.execute();
  System.out.println(cs.getString("o_return_msg"));
  cs.close();
} catch (Exception e) {
  ...
}
Rainer
  • 1,067
  • 2
  • 14
  • 29
  • Thanks! That is very confusing indeed. So, either you have the names in `prepareCall` or in `setString`, but not in both? – wolφi May 08 '20 at 09:13
  • To me it seems so. I found examples in some blogs with other options, but for now I didn't investigate if these examples were old or for other databases or whatever. The most important fact: it works :) I added I line in my answer to make it more clear in case somebody else has the same issue. – Rainer May 08 '20 at 09:55
  • Very well. BTW, it is ok to [accept](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) your own answer to show other users that it is a good solution to the question. – wolφi May 08 '20 at 10:12