This discussion is archived
5 Replies Latest reply: Jan 15, 2013 8:12 AM by 758358 RSS

ORA-25201

926884 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points