Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Posts
    20

    Talking Unanswered: "java.sql.SQLException: Ref cursor is invalid" - Please help

    Hi everyone,

    I'm really having problem. I'm surrently calling a PL/SQL stored procedure from Java but I'm getting the following error:


    java.sql.SQLException: Ref cursor is invalid


    The procedure has 2 normal IN parameters and 1 ref cursor OUT parameter. Depending upon Parameter 1 a ref cursor may or may not be initialised.

    If Parameter 1 is "yes" then a ref cursor is called to retrieve some data and passed into the OUT. If Parameter 1 is set to "no" then the ref cursor is not initialised. It is here where the error is being hrown. Does anyone know why? Microsoft ADO doesn't seem to complain. Please help is needed. Thanks in advance.


    John

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    It is always proper to identify your Oracle Version and OS. In this case the code would be nice also. Give me/us more to go on and "we" will see what we can do.
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Mar 2003
    Posts
    20
    The Oracle version is 9.0.1.0.1 (Oracle 9i) and it's running on Windows 2000. Gving the code is tricky because we've made wrapper objects to call SQL and stored procedures; the code isn't stored in one place. But the problem is basically that the proecdures expects an OUT parameter as a ref cusor. When parameter 1 is set to "yes" then the ref cursor is initialised using code such as:


    OPEN curs FOR SELECT * FROM table;


    When parameter 1 is set to "no" the "curs" ref cursor isn't initialised and at this point the invalid ref cursor is thrown. Could it be that this happens because JDBC expects something to come back in the OUT parameter but nothing is coming?

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I don't understand. You said "When parameter 1 is set to "no" the "curs" ref cursor isn't initialised " and you are getting an error "Ref cursor is invalid". Seems like a 1 to 1 relationship to me. I don't think JDBC will care if you return nothing. I do think JDBC cares that there be somebody home to return the value (the function must exist). If you know that the cursor does not exist (parameter = no) don't call the cursor!

    (Am I missing something here?)
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Mar 2003
    Posts
    20
    The function takes the form:


    PROCEDURE retrieve_data(param1 IN VARCHAR2, param2 IN VARCHAR2, results OUT REF CURSOR)


    Depending upon what value param1 is a ref cursor will be passed to OUT. So the could looks like:


    TYPE curs IS REF CURSOR

    IF param1 = "no"

    /* do something */

    ELSE IF param1 = "yes"

    /* Open REF CURSOR for return parameter */
    OPEN curs FOR SELECT * FROM table;

    END IF;


    Now when param1="no" then JDBC complains. As you can see "curs" isn't initialised in this case. If it were initialised (using param2="yes") and "curs" bought back no records then I assume JDBC would not complain because at least something was being passed back (i.e. an empty resultset).

    Therefore, what you're basically saying is that when param1="no" the ref cursor isn't initialised and thus Java will throw the "ref cursor is invalid" because the Java resultset can't retrieve any "legitimate" value from the PL/SQL procedure. Am I right?

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Yes. Do you now know what you need to do? (You basically gave me the answer).
    NOTE: Please disregard the label "Senior Member".

  7. #7
    Join Date
    Mar 2003
    Posts
    20
    Yes, I believe I do now. Would you agree that the solution to this problem would be to pass back a dummy ref cursor when param1="no"? Something like:


    IF param1 = "no"

    OPEN curs FOR SELECT * FROM dual WHERE 1=0;

    ELSE IF param1 = "yes"

    /* Open REF CURSOR for return parameter */
    OPEN curs FOR SELECT * FROM table;

    END IF;


    Or if need be, redesign the procedure so that the procedure is only called when I know there will definitely be a ref cursor passed back (even if it's empty)?

  8. #8
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    My thoughts exactly. [Sorry for the delay. I had a client meeting].
    NOTE: Please disregard the label "Senior Member".

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I just read your last line. My initial solution was the select where 1 = 0 approach. The "know when to call" puts the intelligence in the caller which is not real moduler and not good design.
    NOTE: Please disregard the label "Senior Member".

  10. #10
    Join Date
    Mar 2003
    Posts
    20
    By restructuring the procedure I mean the original 3 parameters will be changed to 2 parameters (the "yes/"no" parameter will be removed) and a ref cursor will always be passed back even if empty. This way an invalid ref cursor will not be passed back. Or do you think it's best to just go for the dummy ref cursor (i.e. OPEN curs FOR SELECT * FROM dual WHERE 1=0) as you had initially had though of also? I suppose it's basically down to personal choice and which best suits my needs.

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Simpler is always better (that is my "choice" anyway :-) ).

    If you can remove some code and still get it to work the way you need it to I am all for removal. That is good design and it sounds like you have it.
    NOTE: Please disregard the label "Senior Member".

  12. #12
    Join Date
    Mar 2003
    Posts
    20
    Great. Your help has been much appreciated. Thanks Todd.

    Regards,

    John

Posting Permissions

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