Home » Developer & Programmer » JDeveloper, Java & XML » get the resultset using oracle stored procedures
get the resultset using oracle stored procedures [message #92262] Tue, 11 May 2004 02:31 Go to next message
srinivas reddy
Messages: 3
Registered: September 2000
Junior Member
Hi All

am trying to get the resultset from oracle procedure  but i get the lot of error at compile time am not getting any idea how to solve this error   i send the my code

 

 

CREATE OR REPLACE PACKAGE cursors_pkg
IS
 TYPE refcursortype IS REF CURSOR;
 FUNCTION getusers RETURN refcursortype;
END;
/

CREATE OR REPLACE PACKAGE BODY cursors_pkg
IS
 FUNCTION getusers RETURN refcursortype  IS alluserscursor refcursortype;
  BEGIN
   OPEN alluserscursor
   FOR SELECT ename,empno FROM emp;
   RETURN alluserscursor;
  END;
END;
/

java programme
---------------------------------

import java.sql.*;
import java.io.*;
class refcursor{
 public static void main(String[[]] args){
  try{
   Connection _connection=null;
   String name=null;
   int userId=0;
   Class.forName("oracle.jdbc.driver.OracleDriver");
   _connection= DriverManager.getConnection("Jdbc:Oracle:thin:@192.168.0.23:1521:magic","scott","tiger");
   String usersSql = "{?:= cursors_pkg.getUsers;}";
   CallableStatement stmt = _connection.prepareCall(usersSql);
   stmt.registerOutParameter(1,OracleTypes.CURSOR);
   stmt.execute();
   ResultSet rset =(OracleCallableStatement)stmt.getCursor(1);
   while (rset.next()){
    name = rset.getString(1);
    userId = rset.getInt(2);
    System.out.println("n the values are------[>]"+name+"-----"+userId);
   }
   rset.close();
   stmt.close();
  }catch(SQLException se){
   System.out.println("t Hello World!----[>]"+se);
  }catch(Exception e){
   System.out.println("t Hello World!----[>]"+e);
  }
 }
}

----------------------------------

i get the error from these lines
   stmt.registerOutParameter(1,OracleTypes.CURSOR);
   ResultSet rset =(OracleCallableStatement)stmt.getCursor(1);

 

like cannot resolve symbol OracleTypes etc...

please help me .....

 

 

 
Re: get the resultset using oracle stored procedures [message #92273 is a reply to message #92262] Thu, 13 May 2004 22:01 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Please try the following and let us know if it works:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class refcursor
{
public static void main (String args [[]])
throws SQLException, ClassNotFoundException
{
String driver_class = "oracle.jdbc.driver.OracleDriver";
String connect_string = "jdbc:oracle:thin:@192.168.0.23:1521:magic";
String usersSql = "begin :1 := cursors_pkg.getusers; end;";
Connection conn;
Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "scott", "tiger");
CallableStatement stmt = conn.prepareCall(usersSql);
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
ResultSet rset = (ResultSet)stmt.getObject(1);
while (rset.next ())
System.out.println( rset.getString (1) );
stmt.close();
}
}
Previous Topic: how to use jdbc drivers effeceintly
Next Topic: JDeveloper-Pls Help
Goto Forum:
  


Current Time: Tue Jul 27 16:49:50 CDT 2021