returning multiple rows from stored procedures
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: returning multiple rows from stored procedures

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2000
    Posts
    255
    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

  4. #4
    Join Date
    Dec 2000
    Posts
    255
    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

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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


  6. #6
    Join Date
    Dec 2000
    Posts
    255
    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

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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


  8. #8
    Join Date
    Dec 2000
    Posts
    255
    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
  •  


Click Here to Expand Forum to Full Width