• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Rahul's Argument !! Function/Procedure retrun Cursor itself to a Java Program?!!

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear Friends
In fact this code discusssion really created curiosity.
pisal rahul advises that u can fetch cursor to java program.
as follows. I would like to know then how to write a procedure
for that. I want fetch 3 feilds from a citymaster table and return to java Program . Can anyone help out. Rahul r u there somewhere near by
Rahul Argues as follows :
do the following
CallableStatement stmt = con.prepareCall
("{? = call procedurename(?)}");
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.setString(2,"Rahul");
stmt.executeUpdate();
ResultSet rs = (ResultSet)stmt.getObject(1);

 
Sheriff
Posts: 7001
6
Eclipse IDE Python C++ Debian Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"nabhilash",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please log in with a new name which meets the requirements.
Thanks.
 
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please see following example which I borrow now from one
of my previous assignments:
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class retrefcur_samp
{
public static void main (String args [])
// throws SQLException
{
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connection URL.
Connection conn =
DriverManager.getConnection ("jdbc:oracle:thin:@pavilion03:1529:ora8i", "you", "me");
init (conn);
CallableStatement call =
conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");

call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "*");
call.execute ();
OracleResultSet rset = (OracleResultSet)call.getObject (1);

while (rset.next ()){
System.out.println (rset.getString ("JOB"));
}
rset.close();
call.close();
} catch(SQLException esql) {
System.out.println("Trouble in river city ");
System.out.println(esql.toString());
}
}
static void init (Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement ();
stmt.execute ("create or replace package java_refcursor as " +
" type myrctype is ref cursor return EMP%ROWTYPE; " +
" function job_listing (j varchar2) return myrctype; " +
"end java_refcursor;");
stmt.execute ("create or replace package body java_refcursor as " +
" function job_listing (j varchar2) return myrctype is " +
" rc myrctype; " +
" begin " +
" open rc for select * from emp; " +
" return rc; " +
" end; " +
"end java_refcursor;");
stmt.execute ("create or replace package java_ref_clean as " +
" type myrctype is ref cursor; " +
" function cleanup (cc myrctype) return number; " +
"end java_ref_clean;");
stmt.execute ("create or replace package body java_ref_clean as " +
" function cleanup (cc myrctype) return number is "+
" result number; " +
" begin " +
" close cc; " +
" end; " +
"end java_ref_clean;");
}
}

------------------
andre van winssen
awinssen@xs4all.nl
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear Andre,
Thanks a lot for that advice. It is of really great help
Regards
Abhilash
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I saw Topic: Rahul's Argument !! Function/Procedure retrun Cursor itself to a Java Program?!!
I have similar problem. I am selecting 7 fields from 3 tables and creating a cursor in a function. It returns all rows in one step.
I want only 10 rows at a time (As in this topic getting all row at a time).
For this I have to fetch in loop for 10 times, but gives compilation error in oracle when I write fetch into ref_cursor.
Should I take all values in arrays or is there a way to modify andre van winssen's suggested way.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic