• 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

getting error for fetching result from oarcle with outparameter

 
Ranch Hand
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 129
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:
 
Vijay Kumar
Ranch Hand
Posts: 260
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 129
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
reply
    Bookmark Topic Watch Topic
  • New Topic