6 Replies Latest reply: Feb 4, 2013 7:58 AM by cad0227 RSS

    Initialize Ref Cursor to Avoid ORA-01001: invalid cursor

    cad0227
      Hi,

      I'm writing a stored procedure that will return a REF CURSOR. However, there are times when the cursor will not be opened if certain conditions are not met, so I'm wondering if there's a way to initialize the REF CURSOR so that the caller doesn't receive the "ORA-01001: invalid cursor" error when trying to work with the cursor if it has not been opened.

      Any help is greatly appreciated...

      Thanks,
      Christine
        • 1. Re: Initialize Ref Cursor to Avoid ORA-01001: invalid cursor
          Frank Kulash
          Hi, Christine,

          Perhaps you should add another OUT argument to the procedure, and set it to 1 or 0, to indicate if the cursor has or has not been opened. The calling procedure can check this value, to decide if it will reference the cursor or not.
          • 2. Re: Initialize Ref Cursor to Avoid ORA-01001: invalid cursor
            cad0227
            Thanks Frank. I actually do have another OUT argument that indicates whether or not certain conditions were not met, thus implying that the cursor was not open. I just thought that it would be a good idea to initialize it, if possible, so that no error would result if someone did try to reference it.
            • 3. Re: Initialize Ref Cursor to Avoid ORA-01001: invalid cursor
              rp0428
              >
              I just thought that it would be a good idea to initialize it, if possible, so that no error would result if someone did try to reference it.
              >
              But it IS an error to reference an unopened cursor just like it is an error to reference an unitialized variable.

              Either modify your Java code to trap the exception and deal with it appropriately or modify your procedure to open the cursor on an empty result set.

              There is no such thing as 'initializing' a cursor; you either open it or you don't.
              • 4. Re: Initialize Ref Cursor to Avoid ORA-01001: invalid cursor
                Billy~Verreynne
                cad0227 wrote:

                I'm writing a stored procedure that will return a REF CURSOR. However, there are times when the cursor will not be opened if certain conditions are not met, so I'm wondering if there's a way to initialize the REF CURSOR so that the caller doesn't receive the "ORA-01001: invalid cursor" error when trying to work with the cursor if it has not been opened.
                Does the SQL projection needs to be the same? If not then one can "initialise" the ref cursor as simple "+select null as NO_DATA from dual+". If the projection needs to be the same, then something more complex is needed, like selecting nulls from dual and casting that to the appropriate data types, in order to return a row with all projected columns as null.

                E.g.
                .. code ..
                  if not refCur%IsOpen then
                    --// "initialise" the cursor to return a null row
                    open refCur for select null as NO_DATA from dual;
                  end if;
                .. code ..
                • 5. Re: Initialize Ref Cursor to Avoid ORA-01001: invalid cursor
                  _Karthick_
                  cad0227 wrote:
                  Hi,

                  I'm writing a stored procedure that will return a REF CURSOR. However, there are times when the cursor will not be opened if certain conditions are not met, so I'm wondering if there's a way to initialize the REF CURSOR so that the caller doesn't receive the "ORA-01001: invalid cursor" error when trying to work with the cursor if it has not been opened.

                  Any help is greatly appreciated...

                  Thanks,
                  Christine
                  The more appropriate way would be for the caller to handle the situation. The caller should capture the INVALID_CURSOR exception and do whatever is necessary.

                  The other suggestions like having a seperate flag or having a dummy select all will lead the caller to act for the specific situation, which is cursor not being opened. The same is the case with INVALID_CURSOR exception raised by oracle.

                  All the caller need to do is to handle the INVALID_CURSOR exception and you should be good. And also INVALID_CURSOR is not an error, its a exception which has a specific meaning to it. In you case the meaning is that the required condition where not met to return a cursor.
                  • 6. Re: Initialize Ref Cursor to Avoid ORA-01001: invalid cursor
                    cad0227
                    Thanks for the feedback everyone.

                    Karthick, this is the first time I was deailing with returning a REF CURSOR, so I just wanted to know what my options were, but your feedback makes sense and I will be working with the Java caller to ensure that they first check the error code being returned and understand that they will see an INVALID CURSOR message if they try to access the cursor when certain conditions have not been met.

                    Thanks for your help!
                    Karthick_Arp wrote:
                    cad0227 wrote:
                    Hi,

                    I'm writing a stored procedure that will return a REF CURSOR. However, there are times when the cursor will not be opened if certain conditions are not met, so I'm wondering if there's a way to initialize the REF CURSOR so that the caller doesn't receive the "ORA-01001: invalid cursor" error when trying to work with the cursor if it has not been opened.

                    Any help is greatly appreciated...

                    Thanks,
                    Christine
                    The more appropriate way would be for the caller to handle the situation. The caller should capture the INVALID_CURSOR exception and do whatever is necessary.

                    The other suggestions like having a seperate flag or having a dummy select all will lead the caller to act for the specific situation, which is cursor not being opened. The same is the case with INVALID_CURSOR exception raised by oracle.

                    All the caller need to do is to handle the INVALID_CURSOR exception and you should be good. And also INVALID_CURSOR is not an error, its a exception which has a specific meaning to it. In you case the meaning is that the required condition where not met to return a cursor.
                    >
                    cad0227 wrote:
                    Hi,

                    I'm writing a stored procedure that will return a REF CURSOR. However, there are times when the cursor will not be opened if certain conditions are not met, so I'm wondering if there's a way to initialize the REF CURSOR so that the caller doesn't receive the "ORA-01001: invalid cursor" error when trying to work with the cursor if it has not been opened.

                    Any help is greatly appreciated...

                    Thanks,
                    Christine
                    The more appropriate way would be for the caller to handle the situation. The caller should capture the INVALID_CURSOR exception and do whatever is necessary.

                    The other suggestions like having a seperate flag or having a dummy select all will lead the caller to act for the specific situation, which is cursor not being opened. The same is the case with INVALID_CURSOR exception raised by oracle.

                    All the caller need to do is to handle the INVALID_CURSOR exception and you should be good. And also INVALID_CURSOR is not an error, its a exception which has a specific meaning to it. In you case the meaning is that the required condition where not met to return a cursor.