5 Replies Latest reply on May 5, 2011 1:39 PM by 859740

    Error message after PL/SQL page process


      I've a function in a package which returns a BOOLEAN. I want to use it in a PL/SQL page process like this:

      if not my_package.my_function(my_variable) then
      null; -- message
      end if;

      I would like to use the result of my function to display one of the pre-defined messages: "Process success message" or the "Process Error Message".

      To solve this, I think I need to assign a value to a global variable which tells APEX what the result of executing my "Process Source" was. Right?

      At first I tried this with exceptions in the function, and an exception in de "process source". But after raising these exception the "process error message" also appears. But I would like to use (only) the "build-in"-messages..... Easier to translate...

        • 1. Re: Error message after PL/SQL page process
          Nico Martens
          We solved this problem an exception clause. This way your process will always succeed and the apex error page will never occure...

          So do something like

          Application Item : AI_MESSAGE

          Pl/sql process
          <some code>
          when others then
          :AI_MESSAGE := 'Error Occured';

          And in you process success message you put: &AI_MESSAGE.

          This will give you your error but the downside of this implementation is that you will lose your real error... But even these things can be worked around
          (hint: :AI_MESSAGE := dbms_utility.format_error_stack() || dbms_utility.format_error_backtrace;)

          Hope this helps


          • 2. Re: Error message after PL/SQL page process
            Is AI_MESSAGE a predefined (available) variable or do I have to define it somewhere (where?) first?

            Because the error I get is....:

            Unexpected error, unable to find item name at application or page level.
                 Error      ERR-1002 Unable to find item ID for item "AI_MESSAGE" in application "104".

            When I do this:

            if not mw4bwt.mwa_form.f_auto(:P6_USERID_MED) then
            :AI_MESSAGE := 'NOT OK';
            :AI_MESSAGE := 'OK';
            end if;
            • 3. Re: Error message after PL/SQL page process
              I Googled a little and was able to create the AI_MESSAGE as an application item (Shared Components, Application Items).

              My success-message of my page is *&AI_MESSAGE* and that's exactly the message I get on the screen, after executing the process...: *&AI_MESSAGE*.

              I was expecting that the &ID_MESSAGE woud by substituted for some text of my own.

              Edited by: 856737 on May 5, 2011 1:46 PM
              • 4. Re: Error message after PL/SQL page process
                Confirm that the <tt>&&AI_MESSAGE.</tt> substitution string has a terminating dot ("."): this is required using the static text exact substitution method to reference the value of an APEX item. (It's not clear from your post.)
                • 5. Re: Error message after PL/SQL page process
                  I missed the dot (.) in Nico's example.

                  Sorry, and thanks, it works a explained in this threat.

                  But I'm still wondering how I can use the predefined "Error message" in combination with a PL/SQL procedure.... What's the user of the possibility to fill in an errormessage anyway if I can't display this message if the procedure fails...?