Pl / sql% RowType in JDBC

Question

I am trying to execute an Oracle Stored Procedure that returns% ROWTYPE using JDBC, as I mentioned below

OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("begin <PackageName>.procedureName(?,?,?,?); end; "); ocs.setString(1, "001"); ocs.registerOutParameter(2,java.sql.Types.VARCHAR); ocs.registerOutParameter(3,java.sql.Types.VARCHAR); ***ocs.registerOutParameter(4,java.sql.Types.OTHER);*** /*here What do i need to put, i didn't find Wrapperclass which is compatible for %ROWTYPE*/ ocs.executeUpdate(); String newerrorcode = ocs.getString(2); System.out.println("\n ErrorCode = " + newerrorcode ); String newErrorDesc = ocs.getString(3); System.out.println("\n ErrorDesc = " + newErrorDesc ); ResultSet rs = (ResultSet) ocs.getObject(4); 

Exception: we get the following

 Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PR_APP_DATE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530) at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4735) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350) at testSysBusinessDts.main(testSysBusinessDts.java:62) 

Answer 1

The simple answer is: use JPublisher and map the types created by JPublisher to the JDBC connection type mapping.

The long answer: neither the JDBC specification nor the Oracle JDBC driver can automatically convert user-defined (user-defined) types into Java objects and vice versa. To do this, you need to establish a type mapping so that the driver can read and write the connection correctly. Although the Oracle JDBC driver allows you to read and write specific SQL types using the oracle.sql.* Classes, it cannot be applied to UDTs because the UDT structure cannot be known in advance. Using JPublisher solves this problem by creating classes that reflect the structure of the UDT; once these classes are taken into account by the Oracle JDBC driver, it can read the corresponding types from the result set, or it can write objects of these types to the connection stream.

Answer 2

I did something similar to what you ask. The trick was to declare a row-type refcursor and declare the return as a cursor. I found the same result elsewhere, hope it helps you .