Forum Discussion

skareemulla's avatar
skareemulla
New Contributor
8 years ago

Unable to receive response after calling PL/SQL stored procedure or function from JDBC request.

I want to call the store procedure and PL/SQL function using Ready API 1.8/1.6 version.
I am using Oracle database.
I tried to call the procedure through Groovy script and JDBC also but I am not able to call store procedure using both .
My store procedure name TEST1 .It consists two parameters . One input parameter and output parameter.

The procedure and function returning response in REFCURSOR

procedure:

{call packarename.TEST1(1222122,?)}

 

 

Fucntion: 

 

{call packagename.Funtion1('112331',null,'2',null,null,null,'2')}

 

 

Please suggest.

 

Thanks

Kareem

 

 

7 Replies

Sort By
    • skareemulla's avatar
      skareemulla
      New Contributor
      8 years ago

      Hi Tanya Gorbunova,

       

      I am not able to access the given link receiving an error "The page you are trying to access was not found. Please check your URL for typos and try again."

       

      please help

       

      Thanks

      kareem

    • nmrao's avatar
      nmrao
      Champion Level 3
      8 years ago
      Yes, thread is not accessible.
  • skareemulla's avatar
    skareemulla
    New Contributor
    8 years ago

    Hi,

     

    I got the solution for it, now I can able read the response which is returned by calling  PL/SQL stored procedure or function using below code

     

    Prerequisite:

    Add JDBC driver in Lib folder of  Ready API workspace.

     

    Calling Procedure which is having out parameter as REF CURSOR

     

    Import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("{call PackageName.Procedurename(InputPar1,?)"); // '?' is for out parameter

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a normal ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));}

     

    Calling PL/SQL function which return REF CURSOR in response:

     

    Import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("{ ? = call Packagename.Funcitonname(Par1,Par2)}");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a normal ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));}

    Regards

    Kareem