Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: refcursor issues with 9i and JDBC connection

    We have been returning data successfully form 8.1.6 via JDBC (Silverstream JDBC driver).
    Now we are testing an upgrade to 9i and the only JDBC available are ORACLE OCI or Thin client. Our functions with refcursors no longer work. We are now getting the following errors when running the app.

    call new.Pkg_Users1.Fn_GetDetails(?,?)} Mon Dec 15 11:10:55 GMT 2003
    ..(1,N)
    /************************************************** ***************************\
    Program Error: java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00221: 'FN_GETDETAILS' is not a procedure or is undefined
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    \************************************************* ****************************/
    DB >> {call new.Pkg_Modules.Fn_GetModulesAndFunctions(?)} Mon Dec 15 11:10:57 GMT 2003
    ..(1)
    /************************************************** ***************************\
    Program Error: java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00221: 'FN_GETMODULESANDFUNCTIONS' is not a procedure or is undefined
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored.

    Is anyone aware of changes to the JDBC drivers between 8i and 9i.

    I have attached both the Java and Oracle code in a file.

    Thanks in advance for any suggestions.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Try replacing you piece of your code as below. Not sure of the differences in two approaches, but this worked for me:
    -------------------------------------------------
    String sQuery = "{?=call NEW.Pkg_Trades1.Fn_GetDetails(?,?)}";
    CallableStatement MyCall;
    MyCall = con.prepareCall(sQuery);

    MyCall.registerOutParameter(1, OracleTypes.CURSOR);
    MyCall.setString(2,"1");
    MyCall.setBigDecimal(3,new BigDecimal(26209));

    MyCall.executeQuery();

    rs = ((OracleCallableStatement) MyCall).getCursor(1);

    -------------------------------------------------

    Regards,
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Posts
    3
    thanks,

    we tried the following as suggested,

    String sQuery = "{?=call asadis_new.Pkg_Trades1.Fn_GetDetails(?,?)}";
    CallableStatement MyCall;
    MyCall = con.prepareCall(sQuery);

    MyCall.registerOutParameter(1, OracleTypes.CURSOR);
    MyCall.setString(2,"1");
    MyCall.setBigDecimal(3,new BigDecimal(26209));

    MyCall.executeQuery();

    rs = ((OracleCallableStatement) MyCall).getCursor(1);



    but get the following error now.........

    java.lang.ClassCastException: com.sssw.srv.sql.AgCallableStatement


    Any ideas on that one.

    Thanks again for the help so far.

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Have you imported the following?
    oracle.jdbc.driver.OracleCallableStatement
    oracle.jdbc.driver.OracleTypes
    Oracle can do wonders !

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Re: refcursor issues with 9i and JDBC connection

    Hi,

    This problem is raised while referencing the function 'FN_GETDETAILS' .

    Try this

    String squery := "{?=call asadis_new.Pkg_Trades1.Fn_GetDetails(?,?)}";




    Originally posted by padraigf
    We have been returning data successfully form 8.1.6 via JDBC (Silverstream JDBC driver).
    Now we are testing an upgrade to 9i and the only JDBC available are ORACLE OCI or Thin client. Our functions with refcursors no longer work. We are now getting the following errors when running the app.

    call new.Pkg_Users1.Fn_GetDetails(?,?)} Mon Dec 15 11:10:55 GMT 2003
    ..(1,N)
    /************************************************** ***************************\
    Program Error: java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00221: 'FN_GETDETAILS' is not a procedure or is undefined
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    \************************************************* ****************************/
    DB >> {call new.Pkg_Modules.Fn_GetModulesAndFunctions(?)} Mon Dec 15 11:10:57 GMT 2003
    ..(1)
    /************************************************** ***************************\
    Program Error: java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00221: 'FN_GETMODULESANDFUNCTIONS' is not a procedure or is undefined
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored.

    Is anyone aware of changes to the JDBC drivers between 8i and 9i.

    I have attached both the Java and Oracle code in a file.

    Thanks in advance for any suggestions.
    SATHISH .

Posting Permissions

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