Discussions

General J2EE: Calling a Stored Procedure thru TOPLink

  1. Calling a Stored Procedure thru TOPLink (4 messages)

    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,
  2. 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
  3. 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)
  4. Would you please share what was the solution?
  5. StoredFunctionCall[ Go to top ]

    using StoredFunctionCall