5 Replies Latest reply: Jan 15, 2013 10:12 AM by 758358 RSS

    ORA-25201

    926884
      We have a DB job that runs about 1 month without problems and ends then with ORA-25201. The information available from Oracle about this error is very sparse. I can find neither a bug-fix nor hints how to avoid this problem. DB version is 10g2. Any hints?
        • 1. Re: ORA-25201
          758358
          Really need to see the code behind the job - but that error suggests that it is probably calling dbms_aq.enqueue or dequeue - which requires a parameter of either a dequeue_options_t TYPE or enqueue_options_t TYPE, each of these have a "VISIBILITY" attribute that can either be:

          ON_COMMIT or IMMEDIATE

          Your error, suggests you are passing something else:

          $ oerr ora 25201
          25201, 00000, "invalid value, VISIBILITY should be ON_COMMIT or IMMEDIATE"
          // *Cause: An invalid value specified for parameter VISIBILITY.
          // *Action: Specify either ON_COMMIT or IMMEDIATE.

          At a guess you have some IF statements wrapped around a call to the above, they only fire under circumstances hence the intermittent failures, but as I say without seeing what the job is doing it is difficult to say.

          Hope that helps,
          Paul
          • 2. Re: ORA-25201
            926884
            Hi Paul,

            Thanks for your hint. Visibility does not get an explicit value, instead the default, "ON_COMMIT", is used. Hence it should get never an illegal value, except there is a bug in dbms_aq ;-)

            Regards,
            Heinz

            From Oracle PL/SQL and Types Reference:

            TYPE SYS.ENQUEUE_OPTIONS_T IS RECORD (
            visibility BINARY_INTEGER DEFAULT ON_COMMIT,
            .
            .
            .);

            TYPE DEQUEUE_OPTIONS_T IS RECORD (
            .
            visibility BINARY_INTEGER DEFAULT ON_COMMIT,
            .
            .
            );

            The code:

            PROCEDURE dequeue_message(
            r_reply OUT my_reply_type,
            s_recipient IN VARCHAR2,
            b_remove IN BOOLEAN )
            IS
            r_dequeue_options dbms_aq.dequeue_options_t;
            r_message_properties dbms_aq.message_properties_t;
            s_message_handle RAW(16);
            BEGIN
            r_dequeue_options.consumer_name := s_recipient;
            r_dequeue_options.wait := i_timeout_;
            r_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
            IF b_remove THEN
            r_dequeue_options.dequeue_mode := DBMS_AQ.REMOVE;
            ELSE
            r_dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;
            END IF;

            DBMS_AQ.DEQUEUE( queue_name => s_queue_,
            dequeue_options => r_dequeue_options,
            message_properties => r_message_properties,
            payload => r_reply,
            msgid => s_message_handle );
            .
            .
            .

            PROCEDURE enqueue_message(
            r_reply IN my_reply_type,
            s_recipient IN VARCHAR2 )
            IS
            r_enqueue_options dbms_aq.enqueue_options_t;
            r_message_properties dbms_aq.message_properties_t;
            a_recipients dbms_aq.aq$_recipient_list_t;
            s_message_handle RAW(16);
            BEGIN
            a_recipients(1) := sys.aq$_agent( s_recipient, NULL, NULL );
            r_message_properties.recipient_list := a_recipients;
            r_message_properties.expiration := i_exp_time_;
            dbms_aq.enqueue( queue_name => s_queue_,
            enqueue_options => r_enqueue_options,
            message_properties => r_message_properties,
            payload => r_reply,
            msgid => s_message_handle);
            .
            .
            • 3. Re: ORA-25201
              758358
              Ok, so what error stack do you get from the program when it fails? Where in the code does the error occur?

              What specific version of 10gR2 are you using ?

              Thanks
              Paul
              • 4. Re: ORA-25201
                926884
                Hi Paul,

                DB-version: 10.2.0.4

                What I get in my trace-file is not really helpful:

                SQLCODE: ORA-25201: invalid value, VISIBILITY should be ON_COMMIT or IMMEDIATE

                and from the RAISE statement:

                ORA-06512: at "package-name", 117 ( line number of the RAISE statement...)
                ORA-06512: at line 1 (whatever this is)


                The code looks like this:

                BEGIN
                .
                .
                enqueue_message...
                .
                .
                EXCEPTION
                .
                .
                WHEN OTHERS THEN
                -- write SQLCODE to logfile
                ROLLBACK;
                RAISE;
                END;
                • 5. Re: ORA-25201
                  758358
                  You need to improve the error handling to get a proper view of exactly where the code is failing, either get rid of the exception block altogether (doesn't really serve any purpose other than to make your program think it failed at the RAISE rather than where it really failed) or add some instrumentation into your code to track progress and then print this out in the exception block along with the error message.

                  Your previous comments are correct though, if you are only setting up the type with default settings this error shouldn't occur unless something else is going on at the Oracle level outside your control (e.g. bug as you say).

                  Thanks
                  Paul