Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    India
    Posts
    4

    Unanswered: Cursor as IN parameter for Stored procedure

    I have following stored procedures..

    create procedure sp_selectP2 (result IN TYPES.REF_CURSOR)
    AS
    var1 NUMBER(10);
    var2 NUMBER(10);
    BEGIN
    LOOP
    FETCH result INTO var1, var2;
    insert into scott.t2 values (var1, var2);
    commit;
    EXIT WHEN result%NOTFOUND;
    END LOOP;
    CLOSE result;

    END sp_selectP2;

    When I am running the following JDBC sample

    Statement st = con.createStatement();
    CallableStatement cstmt = null;
    cstmt = con.prepareCall("call scott.sp_selectP2(?)");


    ResultSet set = st.executeQuery("select * from scott.t1");
    if(set == null)
    {
    System.out.println("hello");
    }
    cstmt.setObject(1,set, Types.OTHER);

    cstmt.execute();

    I am getting the following Exception..

    java.sql.SQLException: Invalid column type

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:269)

    at oracle.jdbc.driver.OraclePreparedStatement.setObje ct(OraclePreparedStatement.java:2886)GOT Connection

    SQLExcepion received.null



    at oracle.jdbc.driver.OraclePreparedStatement.setObje ct(OraclePreparedStatement.java:2905)

    Please someone....... help me Out.......

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    I'm missing the declaration of your ref_cursor. Presumably it's declared as kind of a rowtype of the scott.t1 table.
    Try selecting the columns by name in your select in your java code and compare them with the order in the ref_cursor declaration.
    I think you're problem is there.

    Good luck
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Location
    India
    Posts
    4
    Originally posted by evanhattem
    Hi,

    I'm missing the declaration of your ref_cursor. Presumably it's declared as kind of a rowtype of the scott.t1 table.
    Try selecting the columns by name in your select in your java code and compare them with the order in the ref_cursor declaration.
    I think you're problem is there.

    Good luck

    Hi,
    Thanks for reply, but am getting same problem yet.
    My cursor declaration is:

    Drop package types;

    create package TYPES
    AS
    TYPE ref_cursor IS REF CURSOR;
    END;

    I am getting the problem in the call
    cstmt.setObect(1, (ResultSet)set, Types.OTHER);
    Please see my first post. I hope you got the problem, Please try
    testing it and let me know if I am still doing anything wrong
    Over here..

    Regards
    David..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •