Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to call stored procedures

Re: How to call stored procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 21 Apr 1999 12:31:45 GMT
Message-ID: <371ec4f6.5931809@192.86.155.100>


A copy of this was sent to dhinakar_at_my-dejanews.com (if that email address didn't require changing) On Tue, 20 Apr 1999 20:44:48 GMT, you wrote:

>Hi,
>I have a problem in calling a stored procedure.
>I ahve to pass 2 parameters and i ahve to get
>a cursor as return.
>
>What is the syntax for calling the stored procedure in this
>situation.
>
>I am using
>conn.prepareCall("begin incmgr.p_great_deal.sp_get_title_rvlg(?,?,?);end;");
>
> call.registerOutParameter(1, OracleTypes.CURSOR);
> call.setString(2,"ROBERT");
> call.setString(3,"18976");
>
>Am I doing right?
>

Here is an example of what the plsql might look like:

create or replace package types
as

    type cursorType is ref cursor;
end;
/  

create or replace function sp_ListEmp return types.cursortype as

    l_cursor types.cursorType;
begin

    open l_cursor for select ename, empno from emp order by ename;  

    return l_cursor;
end;
/    

And the java to use this could be:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class curvar
{
  public static void main (String args [])

                     throws SQLException, ClassNotFoundException
  {
      String driver_class = "oracle.jdbc.driver.OracleDriver";
      String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";

      String query = "begin :1 := sp_listEmp; end;";
      Connection conn;

      Class.forName(driver_class);
      conn = DriverManager.getConnection(connect_string, "scott", "tiger");

      CallableStatement cstmt = conn.prepareCall(query);
      cstmt.registerOutParameter(1,OracleTypes.CURSOR);
      cstmt.execute();
      ResultSet rset = (ResultSet)cstmt.getObject(1);

      while (rset.next ())
        System.out.println( rset.getString (1) );
      cstmt.close();

  }
}

>Any suggestion?
>
>
>Thanks.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Apr 21 1999 - 07:31:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US