Home » Developer & Programmer » JDeveloper, Java & XML » calling procedure in java (AIX Server)
calling procedure in java [message #282510] Thu, 22 November 2007 03:50 Go to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Dear Sir,
I want to call a procedure which contain ref cursor with bulk collect. Can you tell me how to call that oracle procedure in java.
Looking forward for explaination.

Regards
Rohit
Re: calling procedure in java [message #287174 is a reply to message #282510] Tue, 11 December 2007 05:32 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
have you tried with CallableStatement
Re: calling procedure in java [message #319872 is a reply to message #287174] Tue, 13 May 2008 05:15 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Respected sir,

can you guide me with small example.
Re: calling procedure in java [message #319876 is a reply to message #282510] Tue, 13 May 2008 05:45 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
have a look at this link

http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html

Re: calling procedure in java [message #319886 is a reply to message #319876] Tue, 13 May 2008 06:07 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Respected Sir,

Thanks for the example but my proble is as follows

I have a stored procedure in oracle which i am calling in java through callable statement ..declaration for the procedure is as follows...

create or replace procedure abc(,,,,cursor_my in out ref cursor)
is
begin
open cursor_my for
select * from some_table;
end;

later calling this proc as

CallableStatement stmt=connection.prepareCall("BEGIN abc(?,?,?,?); END;");
stmt.setInt(1,3)
...
stmt.registerOutParameter(4, OracleTypes.CURSOR); //REF CURSOR
stmt.execute();
resultSetPage2= ((OracleCallableStatement)stmt).getCursor(4);

but this way it will fetch all records in one short and put in resultset.This table is huge and i want to restrict number of rows to be fetched.
kindly help me with exapmple.
Re: calling procedure in java [message #319898 is a reply to message #319886] Tue, 13 May 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Open does not fetch anything.
You fetch when you call the fetch/next procedure on the result set.

Regards
Michel
Re: calling procedure in java [message #319903 is a reply to message #319898] Tue, 13 May 2008 06:37 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Sir,

after the
resultSetPage2= ((OracleCallableStatement)stmt[i_trv]).getCursor(4);

i m calling
while(resultSetPage2.next()) {

....
}

but by this way resultset contains all the row of table.

i want bulk collect to implement in that

kindly help me with example.
Re: calling procedure in java [message #319912 is a reply to message #319903] Tue, 13 May 2008 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but by this way resultset contains all the row of table.


No.
It contains the number of rows you defined with setRowPrefetch procedure.

Regards
Michel
Re: calling procedure in java [message #319916 is a reply to message #319912] Tue, 13 May 2008 07:00 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Sir,

so what I have to do to implement bulk collect.

setRowPrefetch if i set for callable statement will it fetch only that number of row? if yes what about the remaining row??
Re: calling procedure in java [message #319930 is a reply to message #282510] Tue, 13 May 2008 07:25 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
HAVE A LOOK AT THE LINK

http://java.sun.com/developer/Books/JDBCTutorial/chapter5.html
Re: calling procedure in java [message #319934 is a reply to message #319916] Tue, 13 May 2008 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if yes what about the remaining row??

They are in the database.

Regards
Michel
Re: calling procedure in java [message #328004 is a reply to message #319916] Wed, 18 June 2008 10:29 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reported By: rk51704
Reported By: rk51704 On: Wed, 18 June 2008 16:33 In: Developer & Programmer » JDeveloper, Java & XML » calling procedure in java
Reason Hi, I am new to OAF. I have to do a similar thing. Like I have to call a procedure in which a record variable(of %rowtype)or a refcursor has to be passed as one of the parameter(it is of IN/OUT type). I am using JDeveloper 9.0.3. Can you please tell me how to do that as I am totally stuck there. Thanks

Don't report your question, post it in the topic.

Regards
Michel
Previous Topic: nesting xmlelement within xmlforest
Next Topic: JDeveloper version 10.3 is hanging up all the time
Goto Forum:
  


Current Time: Fri Mar 29 01:29:19 CDT 2024