WbCall fails because Oracle-driver does not support certain feature.

[Oliver Ehrenmüller]

Hi,

I have a procedure with 2 IN and 1 OUT-PArameters. I try to execute it with given oracle-driver. The command is as followed:

WbCall XYZ(0, '12345-12345', ?);

But this statement fails (using current build 121) with following log-entries:

2016-10-18 16:37:51 WARN  Could not get parameters from statement! Unsupported feature [SQL State=99999, DB Errorcode=17023] 

java.sql.SQLException: Unsupported feature

at oracle.jdbc.driver.OracleParameterMetaData.getParameterMode(OracleParameterMetaData.java:262)

at workbench.sql.wbcommands.WbCall.checkParametersFromStatement(WbCall.java:395)

at workbench.sql.wbcommands.WbCall.execute(WbCall.java:157)

at workbench.sql.StatementRunner.runStatement(StatementRunner.java:565)

at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3370)

at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2131)

at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:2069)

2016-10-18 16:37:51 ERROR Error calling stored procedure using: {call XYZ(0, '12345-12345', ?)} Missing IN or OUT parameter at index:: 1 [SQL State=99999, DB Errorcode=17041] 

java.sql.SQLException: Missing IN or OUT parameter at index:: 1

at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1821)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3579)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)

at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)

at workbench.sql.wbcommands.WbCall.execute(WbCall.java:239)

at workbench.sql.StatementRunner.runStatement(StatementRunner.java:565)

at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3370)

at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2131)

at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:2069)

And i can confirm, that the used oracle-driver (11.2.0.3.0) just throws the given exception. My question is can you provide any workarround for that, so that the execution still works? I know that you are missing infos on the parameter, but maybe you have any idea here. It would be awesome.

kind regards
Oliver

[Thomas Kellerer]

If the call to getParameterMetaData() fails, I'm reverting to a query that tries to identify the parameters by asking the driver for all parameters of the procedure (that's why the log message is only a warning, not an error).

Could you show me the DDL for the procedure in question?
Is that a packaged procedure?

Thomas

[Oliver Ehrenmüller]

Thank you for your Answer.

I do not have the precise DDL because it it a procedure on an other DB accessed via db-link. (Unfortunately I am not that experienced with db-links) It is a packaged procedure and we created a synonym to access it:

CREATE OR REPLACE SYNONYM F_KAPA_PKG_TKDETAILS_ANFRAGE

   FOR TKP_OWN."PA_IF_T...@TTKP.WORLD";

WbCall F_KAPA_PKG_TKDETAILS_ANFRAGE.INSERTANFRAGE(0, '12345:12345', ?);

The synonym itselfs works. Within the application I can call it successfully. But it fails with the sql workbench.

kind regards

Oliver

[Thomas Kellerer]

> I do not have the precise DDL because it it a procedure on an other
> DB accessed via db-link. (Unfortunately I am not that experienced
> with db-links) It is a packaged procedure and we created a synonym to
> access it:

OK, that explains it.

The part that tries to find the parameters for the procedure, does not work with remote procedures (because the driver doesn't handle them)

As I am completely relying on the JDBC driver for this, I don't think there is an easy workaround.

Thomas

[Oliver Ehrenmüller]

A pity. Thank you anyway.

Am Mittwoch, 19. Oktober 2016 09:36:32 UTC+2 schrieb Thomas Kellerer:

> I do not have the precise DDL because it it a procedure on an other
> DB accessed via db-link. (Unfortunately I am not that experienced
> with db-links) It is a packaged procedure and we created a synonym to
> access it:

OK, that explains it.

The part that tries to find the parameters for the procedure, does not work with remote procedures (because the driver doesn't handle them)

As I am completely relying on the JDBC driver for this, I don't think there is an easy workaround.

Thomas

Oliver Ehrenmüller schrieb am 19.10.2016 um 09:25:
> Thank you for your Answer.
>
> I do not have the precise DDL because it it a procedure on an other
> DB accessed via db-link. (Unfortunately I am not that experienced
> with db-links) It is a packaged procedure and we created a synonym to
> access it:
>
> CREATE OR REPLACE SYNONYM F_KAPA_PKG_TKDETAILS_ANFRAGE

>    FOR TKP_OWN."PA_IF_TKDETAILS@TTKP.WORLD";

[Thomas Kellerer]

But you can still use it in SQL Workbench/J using an anonymous PL/SQL block

set serveroutput on;

declare
l_result integer; -- whatever data type that is
begin
F_KAPA_PKG_TKDETAILS_ANFRAGE.INSERTANFRAGE(0, '12345:12345', l_result);
dbms_output.put_line('Result: '||l_result);
end;
/