8 Replies Latest reply: Dec 19, 2010 9:02 PM by 823609 RSS

    ORA-01001 on implicit cursor

    814468
      Hi there!

      I keep getting an ORA-01001 on a straight forward SELECT .. INTO statement and I just don't understand why. All the common causes for ORA-01001 don't apply as this is an implicit cursor where I can't do anything wrong with OPEN, CLOSE etc.

      This is the code block that throws the exception:

      BEGIN

      SELECT nvl(rc.status, c_status_wfi_error) AS status,
      nvl(rc.description, nvl(p_status_string, 'Unknown Error')) AS description
      INTO l_status, l_description
      FROM w_items_2_workflows iw
      LEFT JOIN w_wfitems_wf_return_codes rc
      ON (rc.workflowitems_2_workflow = iw.id)
      WHERE iw.workflow = rec_d_workflow.workflow
      AND iw.workflowitem = p_workflowitem
      AND iw.scenario = rec_d_workflow.scenario
      AND rc.return_code = l_return_code;

      EXCEPTION
      WHEN OTHERS THEN
      p_error_message := aax_zentral.get_error_message(737) || ' (' ||
      to_char(l_return_code) || '/workflow:' ||
      to_char(rec_d_workflow.workflow) ||
      '/workflowitem:' ||
      to_char(p_workflowitem) || '/scenario:' ||
      to_char(rec_d_workflow.scenario) || '/id:' ||
      to_char(rec_d_workflow.id) || ') ' ||
      to_char(SQLCODE) || ' - ' || SQLERRM;

      RAISE aax_zentral.ex_others;
      END;

      Any ideas?

      You would make a desperate man very happy ;)

      Cheers
      Volker
        • 1. Re: ORA-01001 on implicit cursor
          SomeoneElse
          Get rid of your entire EXCEPTION section and rerun the statement.

          Find out the real error and line number.
          • 2. Re: ORA-01001 on implicit cursor
            user617722
            Please check the MAXOPENCURSORS parameter values. If it is too less then increase this values and try to execute. Please also check the exception for sqlerrm only. It will tell the error straight forward.
            • 3. Re: ORA-01001 on implicit cursor
              814468
              How can I check the MAXOPENCURSORS parameter? I only know the OPEN_CURSORS parameter and that is set to a sufficiently high value.

              I keep reading about a precompiler setting named MAXOPENCURSORS but we don't use a precompiler, this is all straight forward PL/SQL...?


              The exception in sqlerrm is defenitely "ORA-01001: invalid cursor".

              What I couldn't do yet is remove the exception handler as SomeoneElse suggested because I don't want to risk passing unhandled exceptions to the invoking application, but what I did do is add a backtrace to the error-ouput:

              EXCEPTION
              WHEN OTHERS THEN
              p_error_message := aax_zentral.get_error_message(737) || ' (' ||
              to_char(l_return_code) || '/workflow:' ||
              to_char(rec_d_workflow.workflow) ||
              '/workflowitem:' ||
              to_char(p_workflowitem) || '/scenario:' ||
              to_char(rec_d_workflow.scenario) || '/id:' ||
              to_char(rec_d_workflow.id) || ') ' ||
              to_char(SQLCODE) || ' - ' || SQLERRM || '; Stacktrace BEGIN - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
              RAISE aax_zentral.ex_others;
              END;

              Problem is this only returns one more line saying

              ORA-06512: at "PACKAGENAME", line XY

              As far as I understand, there should be at least two lines, one containing ORA-06512 and one containing the actual error, but there is only one... I'm confused.
              • 4. Re: ORA-01001 on implicit cursor
                John Spencer
                Removing the exception handler may give you a more informative error message. You can make a copy of the subset of the package that is needed to replicate the error, then use that for your testing.

                My guess would be that the invalid cursor is rec_d_workflow, and the error manifests in that block because you are referencing it there, but the actual error happens earlier in the code path.

                John
                • 5. Re: ORA-01001 on implicit cursor
                  814468
                  Problem is that the error doesn't always occur. It happens sporadically and I can't do anything to reproduce it, which means I have to use the actual production setup for testing, and that of course limits my options...

                  About rec_d_workflow: I'm not sure whether that's technically a cursor. It's a %ROWTYPE variable that is being assigned earlier, so I would assume this can't be the cause, right?

                  Anyway... I will try to reproduce the error in a testing-environment where I can deactivate the exception-handler, maybe this tells me something.
                  • 6. Re: ORA-01001 on implicit cursor
                    823609
                    has this issue been resolved. I'm facing a similar issue too. Any pointers on how to address this issue. Facing ORA-01001 on implicit cursor on SELECT INTO statement.

                    Thanks,
                    Daniel.
                    • 7. Re: ORA-01001 on implicit cursor
                      823672
                      Nope, sorry, still working on it.

                      I'll let you know, if I find out anything!

                      Cheers
                      Volker
                      • 8. Re: ORA-01001 on implicit cursor
                        823609
                        Thanks Volker,

                        i will be checking this thread for any updates.

                        regards,
                        Daniel.