2 Replies Latest reply: Feb 28, 2013 7:06 AM by BluShadow RSS

    Ref cursor

    9876564
      I have a proc

      where i am having a ref cursor as an output parameter

      e.g.
      e;
      Procedure p_example
      ( A_ID   IN   NUMBER,
        A_Number   IN   Vacrhar2,
       po_cur_error_msg    OUT REFCURSOR) IS
      
      begin
      
      IF A_ID = 1
       lv_vc_success_msg error_message := "Success" 
      OPEN po_cur_error_msg FOR
                   SELECT lv_vc_success_msg error_message, '00' ERROR_CODE, 0 flag
                    FROM DUAL; 
      end if ;
      
      exception 
          <Exception Block>
      END p_example;
      My problem is when the value of A_ID =1 the it is coming out successfully and the message "Success" is showing up in Front end (.Net) , But if the value is other than 1 it is throwing the error

      "ORA-24338: Statement is not handled"

      If i open the cursor again when the value of A_ID is not equal to 1 then a message is going to front unnecessarily.

      Kindly suggest me the solution to handle this situaltion.

      Abhishek

      Edited by: 9999999 on Feb 28, 2013 4:40 AM
        • 1. Re: Ref cursor
          SomeoneElse
          In that case, you didn't populate the OUT refcursor.

          So if the calling environment tries to use it, it's not valid.
          • 2. Re: Ref cursor
            BluShadow
            In such a case you probably want something like:
            Procedure p_example ( A_ID   IN   NUMBER,  A_Number   IN   Vacrhar2,  po_cur_error_msg    OUT REFCURSOR) IS
            begin
              lv_vc_success_msg error_message := "Success" 
              OPEN po_cur_error_msg FOR
                         SELECT lv_vc_success_msg error_message, '00' ERROR_CODE, 0 flag
                         FROM DUAL
                         WHERE A_ID=1;
            exception 
                <Exception Block>
            END p_example;
            This way, the cursor is opened regardless, but if A_ID does not equal 1 then the cursor will return no rows.