-
Hi
I have got a problem. I have java program which calls a oracle stored procedure and it works fine if output is single row. We can use cursor in procedures to display output on SQL> but this does not work for Java program.
In Java program I am using PrepareCall() to execute the stored procedure but the output is still single row where as I am expecting the multiple row output. This may be happening becaues the cursor variables in stored procedures keep only last row values.
What I want is my stored procedure returns a output which can be stored into the resultset in java. Do I have to use Types for this.
If anyone can give me a simple program in stored procedure (e.g. even on SCOTT user EMP table like 'SELECT EMPNO, ENAME FROM EMP' which will return result set that can be stored into the java resultset)
I ll be very much thankful.
create or replace procedure jdbc_3 (var1 out number,var2 out varchar2) is
cursor c1 is select empno, ename from emp;
begin
for c2 in c1 loop
var1:=c2.empno;
var2:=c2.ename;
end loop;
end;
/
The above procedure called from java just invokes last record's empno and name. Can anyone help me out in this regard
Amol
-
-
Hi Jeff
your help was really invaluable at this point of time.
I got the program worked on my desktop. If you have
more good links then please let me know.
Thank you very much
Amol
-
HI
I have gone thru the above link and it worked but there were no in parameters were passed. I therefore modified the function following way
create or replace function sp_ListEmp(eno in number) return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp
where empno > = eno
order by ename;
return l_cursor;
end;
/
Now If I want to Call this from Java program I have to add question mark in function parameter and Set the parameter
class curvar
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String driver_class = "oracle.jdbc.driver.OracleDriver";
String connect_string = "jdbcracle:thin:@slackdog:1521racle8";
int num1 = Interger.parseInt(argv[0]);
String query = "begin :1 := sp_listEmp(?); end;";
Connection conn;
Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "scott", "tiger");
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.setInt(1, num1)
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
while (rset.next ())
System.out.println( rset.getString (1) );
cstmt.close();
}
}
The Code written above is not working .Can you let me know the solution on how to call a parameterized function on Java Platform
Amol
-
Hi,
you didn't write why it doesn't work.
This could help ...
...
String query = "begin ? := sp_listEmp(?); end;";
...
cstmt.setInt(2, num1);
...
Regards,
Ales
-
Hi
My procedure is
create or replace package types
as
type cursorType is ref cursor;
end;
/
create or replace function sp_ListEmp(eno number) return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp
where empno >= eno order by ename;
return l_cursor;
end;
/
----------------------------------------------------------------
This is my Java Code
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class jdbc_2 {
public static void main (String args[]) throws Exception
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbcracle:thin:@geo:1521:geodb" , "scott" , "tiger");
int var1 = Integer.parseInt(args[0]);
String query = "begin ? := sp_ListEmp(?); end;";
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.setInt(2,var1);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
while(rset.next())
{
System.out.print(" "+rset.getString("ename"));
System.out.print(" "+rset.getInt("empno"));
System.out.print(" "+rset.getString("job")+"\n");
}
cstmt.close();
}
}
and this is giving error after displaying first row as Invalid Column name
Can anyone help me out
Amol
-
create or replace function sp_ListEmp(eno number) return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno, job from emp
where empno >= eno order by ename;
return l_cursor;
end;
/
Hope this helps.
Regards,
Ales
-
Hi Ales
That really worked . Thanks a lot for your help.
Amol
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|