We are the moment trying to use a stored procedure from our Java code through Toplink but we seem to have an issue there I haven't been able to find an answer to. It might be that my Google-Fu is failing me in this instance.
Our problem is that while the procedure seems to be invoked correctly we get nothing back to the Java code. The procedure in question has been tested from SQLDeveloper with the same input parameters and the procedure returns values as it should.
The procedure signature is
create or replace PACKAGE FORM_METADATA AS
PROCEDURE FORM_LIST_GET (
P_USER_ID IN VARCHAR2,
P_CHANNEL_NO IN NUMBER,
P_START_FROM IN NUMBER, --pagination
P_COUNT_TO_RETURN IN NUMBER, --pagination
P_GET_TOTAL_COUNT IN NUMBER, -- if total count should be returned then 1, other wise 0
P_RESULT_SET OUT SYS_REFCURSOR,
P_TOTAL_COUNT OUT NUMBER);
end FORM_METADATA;
The Java code invoking this procedure looks like this: BigDecimal count = new BigDecimal(-1);
Session session = getSession();
session.setLog(new OutputStreamWriter(System.out));
session.setLogLevel(SessionLog.FINEST);
StoredProcedureCall procedureCall = new StoredProcedureCall();
procedureCall.setProcedureName("FORM_METADATA.FORM_LIST_GET");
procedureCall.addNamedArgumentValue("P_USER_ID", "UserID-1");
procedureCall.addNamedArgumentValue("P_CHANNEL_NO", new BigDecimal(1));
procedureCall.addNamedArgumentValue("P_START_FROM", new BigDecimal(1));
procedureCall.addNamedArgumentValue("P_COUNT_TO_RETURN", new BigDecimal(1000));
procedureCall.addNamedArgumentValue("P_GET_TOTAL_COUNT", new BigDecimal(1));
procedureCall.useNamedCursorOutputAsResultSet("P_RESULT_SET");
procedureCall.addNamedOutputArgument("P_TOTAL_COUNT", "count", BigDecimal.class);
DataReadQuery dq = new DataReadQuery();
dq.setCall(procedureCall);
dq.prepareForExecution();
Object result = session.executeQuery(dq);
System.out.println("Result:: " + result);
System.out.println("Result from StoredProcedreCall: " + procedureCall.getResult());
The output we are receiving looks like this:
[TopLink Warning]: 2015.08.31 16:11:30.781--Failed to get InitialContext for MBean registration: javax.naming.NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial
[TopLink Warning]: 2015.08.31 16:11:30.797--Failed to get InitialContext for MBean registration: javax.naming.NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial
[TopLink Info]: 2015.08.31 16:11:31.685--DatabaseSessionImpl(1827000661)--TopLink, version: Oracle TopLink - 11g Release 1 (11.1.1.5.0) (Build 110305)
[TopLink Info]: 2015.08.31 16:11:34.147--DatabaseSessionImpl(1827000661)--dev login successful
[TopLink Finest]: 2015.08.31 16:11:34.147--DatabaseSessionImpl(1827000661)--Thread(Thread[main,5,main])--Execute query DataReadQuery()
[TopLink Finest]: 2015.08.31 16:11:34.163--DatabaseSessionImpl(1827000661)--Thread(Thread[main,5,main])--reconnecting to external connection pool
[TopLink Fine]: 2015.08.31 16:11:34.227--DatabaseSessionImpl(1827000661)--Connection(1635575430)--Thread(Thread[main,5,main])--BEGIN FORM_METADATA.FORM_LIST_GET(P_USER_ID=>?, P_CHANNEL_NO=>?, P_START_FROM=>?, P_COUNT_TO_RETURN=>?, P_GET_TOTAL_COUNT=>?, P_RESULT_SET=>?, P_TOTAL_COUNT=>?); END;
bind => [UserID-1, 1, 1, 1000, 1, => P_RESULT_SET, => P_TOTAL_COUNT]
Result:: []
Result from StoredProcedreCall: null
Process exited with exit code 0.
I'm fairly sure our problem is with the Java code invoking the procedure but as I said, we haven't been able to find the correct solution yet. Would anyone have a pointer as to where we should start looking next?
Thank you in advance,
Mika
Update:
A colleague of mine managed to find the reason why the procedure didn't seem to return anything. For brevity I had redacted a couple of parameters which we were not used in this instance and which we did not believe to be the reason for the perceived behaviour. However, as the parameters were mandatory these were set to NULL. My colleague thought to try initializing the parameters without value and after that the query returned a set of data that was expected.
So our code change (in Java) was to go from
procedureCall.addNamedArgumentValue("P_ORDER_BY", OracleTypes.NULL);
to
procedureCall.addNamedArgument("P_ORDER_BY");
Now we get the data from the cursor as expected but the second out parameter's value still eludes us.
After a more focused search for accessing the second out parameter we found this thread in Oracle's forums which lead to the solution we needed.