I am trying to call a function in a package(Oracle Stored Procedure) which takes one integer as argument and returns an integer. I am using StoredProcedureCall class in Toplink API. I am getting an error. Could some please send me some sample code which shows how to call a stored procedure from toplink.

Thanks
Rajesh,

Hi Rajesh ,
Here is a sample code ....hope that it helps

Example Stored procedure call with an output parameter
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("POSTAL_CODE");
call.addNamedOutputArgument("IS_VALID");
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
query.addArgument("POSTAL_CODE");
Vector parameters = new Vector();
parameters.addElement("L5J1H5");
Number isValid = (Number)
                 session.executeQuery(query,parameters);


Cheers
Brijesh

Hi Brijesh ,

Thanks for your reply. I am using the same example from TOPlink documentation.
I have a package by name PACKAGE_ONE, in that there is a function FUNCTION_ONE in the stored procedure, which takes one integer as argument and returns another integer.

When I called like shown below..

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("PACKAGE_ONE.FUNCTION_ONE");
call.addNamedArgument("ARG_INTEGER");
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
query.addArgument("ARG_INTEGER");
Vector parameters = new Vector();
parameters.addElement(new Integer(1234));
session.executeQuery(query,parameters);

It is giving the following exception


INTERNAL EXCEPTION: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'FUNCTION_ONE' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

ERROR CODE: 6550
        at java.lang.Throwable.fillInStackTrace(Native Method)
        at java.lang.Throwable.fillInStackTrace(Compiled Code)
        at java.lang.Throwable.<init>(Compiled Code)
        at java.lang.Exception.<init>(Compiled Code)
        at java.lang.RuntimeException.<init>(RuntimeException.java:47)
  at TOPLink.Public.Exceptions.TOPLinkException.<init>(TOPLinkException.java:24)
        at TOPLink.Public.Exceptions.TOPLinkException.<init>(TOPLinkException.java:35)
        at TOPLink.Public.Exceptions.DatabaseException.<init>(DatabaseException.java:38)
        at TOPLink.Public.Exceptions.DatabaseException.sqlException(DatabaseException.java:112)
        at TOPLink.Private.DatabaseAccess.DatabaseAccessor.executeCall(Compiled Code)
        at TOPLink.Private.QueryFramework.CallQueryMechanism.executeCall(Compiled Code)
        at TOPLink.Private.QueryFramework.CallQueryMechanism.executeCall(Compiled Code)
        at TOPLink.Private.QueryFramework.CallQueryMechanism.executeSelectCall(Compiled Code)
        at TOPLink.Private.QueryFramework.CallQueryMechanism.executeSelect(Compiled Code)
        at TOPLink.Public.QueryFramework.DataReadQuery.execute(Compiled Code)
        at TOPLink.Public.QueryFramework.DirectReadQuery.execute(Compiled Code)
        at TOPLink.Public.QueryFramework.ValueReadQuery.execute(ValueReadQuery.java:44)
        at TOPLink.Public.QueryFramework.DatabaseQuery.execute(Compiled Code)
        at TOPLink.Public.QueryFramework.ReadQuery.execute(ReadQuery.java:98)
        at TOPLink.Public.PublicInterface.Session.internalExecuteQuery(Compiled Code)
        at TOPLink.Public.PublicInterface.Session.executeQuery(Compiled Code)
        at TOPLink.Public.PublicInterface.Session.executeQuery(Session.java:696)

Would you please share what was the solution?

using StoredFunctionCall