Is it possible to receive Oracle Object Type as an output from stored procedure? (java.sql.Types STRUCT)
Hello everyone!
I am writing a SQL CALL (using JDBC) to a stored procedure that outputs a structured object (Oracle Object).
However, the adapter method is not accepting the corresponding JDBC Data Type STRUCT for the output parameter, returning the following error:
ERRO #5023: Erro no Gateway Remoto: JDBC Gateway SP execute(0) error 0: Remote JDBC error: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'AGUARDAR_EVENTO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Declaration of Output parameter:
Set tParams(1, "SqlType") = $$$SqlJStruct
Set tParams(1, "IOType") = $$$SQLPARAMOUTPUT
According to the documentation, STRUCT Interface has not been implemented yet:
.png)
To retrieve a parameter typed as an Oracle Object, would I have to implement this interface as well?
Comments
It is not possible based on the available information to receive an Oracle Object output parameter as java.sql.Types.STRUCT through this interface, because the material you cited indicates that the STRUCT interface has not been implemented, and none of the retrieved documents provide a supported way to implement or enable Oracle Object output handling for that type. [1][2]
The stored procedure adapter documentation only describes output handling for scalar output and input/output parameters through pOutputParms As %ListOfDataTypes, and it states that large object output parameters are returned as streams when using JDBC through the Java Gateway. It does not describe support for Oracle Object / STRUCT output parameters. [3][4][5][6]
Important: The name of the stored procedure is case-sensitive. Also, make sure that the pQueryStatement statement supplies an argument for every input or input/output parameter that the SQL query requires. [3][4][5][6]
From the available documents, there is no documented method showing that you can implement the missing STRUCT interface yourself in order to retrieve an Oracle Object output parameter through the adapter. [1][2][3]
Sources: