method Name execute This statement does not declare an OUT parameter. Use { ?= call ... } to declare one

Question

I had migrated database from Oracle to AWS Aurora PostgreSQL. I saw that all the packages are migrated as Function in PostgreSQL. I used AWS SCT for the Oracle schema conversion to postgreSQL. Java is the application middleware.

for example, A package and associated stored proc in Oracle pk_audit.sp_get_audit converted to postgreSQL as pk_audit$sp_get_audit with a $ symbol.

When I run the web application, I'm getting an error like method Name execute This statement does not declare an OUT parameter. Use { ?= call ... } to declare one .

I don't have access to the application, but App team provided weblogic log. It says,

Method Name execute org.postgresql.util.PSQLException: This statement does not declare an OUT parameter.Use { ?= call ... } to declare one. org.postgresql.jdbc.PgCallableStatement.registerOutParameter(PgCallableStatement.java:205) weblogic.jdbc.wrapper.CallableStatement_org_postgresql_jdbc_PgCallableStatement.registerOutParameter(Unknown Source

package name specified in the Java code is pk_audit.sp_get_audit Renamed the Postgres function pk_audit$sp_get_audit to pk_audit.sp_get_audit still facing the issue.

Is there anything I need to do in PostgreSQL DB ? I need advise and help,Thanks.

Then your app team has to solve the problem alone. Without the code that causes the error, we cannot help you. — Laurenz Albe, Apr 02 '20 at 07:33
@LaurenzAlbe. Okay. how can I update pk_audit$sp_get_audit to pk_audit.sp_get_audit this format in a single shot ? — aan anna philip, Apr 02 '20 at 14:27
`ALTER FUNCTION` or `ALTER PROCEDURE`, depending on what it is. — Laurenz Albe, Apr 02 '20 at 14:38
I want to keep it as function, but the problem is `$` in the procedure. I need to replace $ to '.' (dot) in all the user defined functions. like pk_audit.sp_get_audit — aan anna philip, Apr 02 '20 at 14:45
`update pg_proc pr set proname = replace(pr.proname, '$', '.') From pg_proc p left join pg_namespace n on p.pronamespace = n.oid where n.nspname = 'schema_name' and p.proname = ' pk_audit.sp_get_audit' ;` which is giving permission denied for the relation pg_proc . — aan anna philip, Apr 02 '20 at 14:46
@LaurenzAlbe I've 1500 function in the database, Can i do it in a single update query ? — aan anna philip, Apr 02 '20 at 14:47
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/210806/discussion-between-aan-anna-philip-and-laurenz-albe). — aan anna philip, Apr 02 '20 at 14:50
No. And that has nothing to do with this question. Ask a new one. And have a look at `psql`'s `\gexec`. — Laurenz Albe, Apr 02 '20 at 14:59
@LaurenzAlbe Posted new question [new_qus](https://stackoverflow.com/questions/60996449/naming-issues-on-oracle-packages-conversion-to-postgresql-using-aws-sct-tool) — aan anna philip, Apr 02 '20 at 17:32

score 0 · Accepted Answer · answered Apr 02 '20 at 07:36

As documented in CallableStatement, the JDBC syntax for calling stored procedures is one of these

{call ProcedureName(?, ...)}

{? = call FunctionName(?, ...)}

Any of the parameters can be OUT parameters. The return value is of course a type of OUT parameter.

So, if you had a stored procedure with 2 parameters and the second parameter was an OUT parameter, you would code it as:

String sql = "{call MyProcedure(?, ?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    stmt.setInt(1, p1);
    stmt.registerOutParameter(2, Types.VARCHAR);
    ...
}

If that same procedure is converted into a function, you would code it as:

String sql = "{? = call MyFunction(?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    stmt.registerOutParameter(1, Types.VARCHAR);
    stmt.setInt(2, p1);
    ...
}

If you cannot change the Java code making the call, you need to convert your functions back to procedures.

Note that Postgres supports procedures only from [version 11](https://www.postgresql.org/docs/11/sql-createprocedure.html) onwards, so you need at least Aurora [version 3.0](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html), or stick to functions. — Kayaman, Apr 02 '20 at 07:47

method Name execute This statement does not declare an OUT parameter. Use { ?= call ... } to declare one

1 Answers1