This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes getting error for fetching result from oarcle with outparameter Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "getting error for fetching result from oarcle with outparameter" Watch "getting error for fetching result from oarcle with outparameter" New topic
Author

getting error for fetching result from oarcle with outparameter

Vijay Kumar
Ranch Hand

Joined: Jul 24, 2003
Posts: 260
hi.. all
I am indulge in a project in which I have written a
oracle procedure with one out parameter of PLSQLTABLE TYLE
i just wanto fetch data using java.
procedure written under oracle is working fine if we exececuet it in oracle client SQL Plus 8.0
but using java it does't work.
if we diclare this out parameter in procedure as REFCURSOR type & we diclare this outparameter in java
as call.registerOutParameter(1, OracleTypes.CURSOR); it give me result.

so please tell me how can I get result in java from procedure in which out parameter is delare as PLSQLTABLE.

My env is Oracle 8.0, jdk1.4 I am using thin driver

Thanks
Jherald Lacambra
Ranch Hand

Joined: Feb 02, 2005
Posts: 129
try this:

try{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection(driver,username,password);
conn.setAutoCommit (true);
CallableStatement cs = conn.prepareCall("{call get_user(?,?,?)}");
cs.setString(1,string1);
cs.setString(2,string2);
cs.registerOutParameter(3,Types.INTEGER);
cs.execute();
counter = "" + cs.getInt(3);
System.out.println("counter: "+counter);
conn.close();
cs.close();
}catch(SQLException e){
e.printStackTrace();
}

String1 and string2 were the input parameters for the procedure in PLSQL Table and cs gets the output parameter from the procedure.

Hope this helps :roll: :roll: :roll:


jherald
Vijay Kumar
Ranch Hand

Joined: Jul 24, 2003
Posts: 260
Thanks Jherald Lacambra
but I have to fetch maultiple Rows with multipleColumn which has been done using call.registerOutParameter(1, OracleTypes.CURSOR);
My code which I have written



class RefCursorExample{
public static void main(String args[]) throws SQLException {
//Load the driver.
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc racle:thin:scott/tiger@80.0.0.12:1526 RACLE");
// Prepare a PL/SQL call. line 20
CallableStatement call =
conn.prepareCall("{ call utype_pkg.utype_ucur3(?,?,?) }");
// Find out who all the sales people are. line 24
call.registerOutParameter(1, OracleTypes.CURSOR);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.setObject(3, "emp");
System.out.println("befor exec");
call.execute();
System.out.println("after exec");
ResultSet rset = (ResultSet)call.getObject(1);
ResultSet aset = (ResultSet)call.getObject(2);
System.out.println("rset:"+rset);
System.out.println("aset:"+aset);
// Output the information in the cursor. line 30
while (rset.next()){
System.out.print(rset.getString(1));
System.out.print(rset.getString(2));
}
while (aset.next()){
System.out.print(aset.getString(1));
System.out.println("--------");
}

}

this is working fine....
I have to pick data from PLSQLindex table
Jherald Lacambra
Ranch Hand

Joined: Feb 02, 2005
Posts: 129
may be you can recode your procedure to make array out of the procedure.. Ask a DBA to do that for you.. I dont know much of how to make arrays in an sql procedure
D JQ
Greenhorn

Joined: Sep 08, 2005
Posts: 2
public String ProcedureSQLDB(Vector sqlvct)
{
String recid = "0";
try
{
String sql="{CALL "+(String)sqlvct.get(0)+"}";
CallableStatement cstmt = conn.prepareCall(sql);
for (int i=1;i< sqlvct.size();i++){
cstmt.setString(i,(String)sqlvct.get(i));
}
rs = cstmt.executeQuery();
if(rs.next())
{
recid = rs.getString(1);
System.out.println(recid);
}
}
catch(SQLException sqle)
{
System.out.println("Procedure ERROR: " + (String)sqlvct.get(0) + " ;" + sqle);
}
return recid;
}

This is my function,and when i use it in my main void,it was successful,and i got the record. But when i uses it in javaBean, it reported an error.
Help me please.
D JQ
Greenhorn

Joined: Sep 08, 2005
Posts: 2
I sended the same parameters in the JSP file,I cought an error:
java.sql.SQLException: [Microsoft][SQLServer JDBC Driver]No ResultSet set was produced.
It was no error in the main void when i uses the same parameters .
My database is sql server2000.

this is my procedure:
CREATE PROCEDURE InsertRecord
@tn varchar(20), -- TableName
@zn varchar(20), -- ID FieldName
@sn varchar(6000), -- FieldName Array
@sm varchar(6000) -- FieldValue Array
AS

begin transaction

declare @s varchar (8000)
if @tn='join' set @tn='[join]'

if (@sn='()')and(@sm='('''')') set @s='Insert Into '+@tn+' DEFAULT VALUES' else
set @s='Insert Into '+@tn+@sn+' values '+@sm
EXEC (@s)

set @s='Select Max('+@zn+') as maxid,xlid,gxrq from '+@tn
EXEC (@s)

commit transaction
GO
[ September 08, 2005: Message edited by: D JQ ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: getting error for fetching result from oarcle with outparameter