6 Replies Latest reply on Mar 4, 2014 11:03 AM by Gurujothi

    Display RAISE_APPLICATION_ERROR message in oracle forms

    user597769

      Hi,

       

      I have a database procedure which is doing a series of validations. Where the validation fails, I have put in a raise_application_error call with a custom error message. I am calling this in PRE-FORM trigger of the form and would like to display the custom error message. I have managed to display something (not the exact error message) using the message(sqlerrm) statement. But this shows a lot of other junk as well like the error number associated with the custom error at the beginning of the message & the entire stack at the end of the custom error - something like

       

      ora-20001: <<custom message>>. ora-06512: at <<package.procedure_name>>, line 100

       

      Is there a way by which I can display only the user-defined message without sending the message text back to the form as a return value OUT parameter or re-coding the message in the form

       

      thanks

      Parag

        • 2. Re: Display RAISE_APPLICATION_ERROR message in oracle forms
          user346369

          The PLL_On_Error procedure shown here will parse out the message and display it: 

           

          FRM-40735:Pre_Insert trigger raised unhandled exception ORA-20011

          • 3. Re: Display RAISE_APPLICATION_ERROR message in oracle forms
            user597769

            Folks,

             

            thanks for all your efforts ! however, I don't think you guys have understood my requirement very well - or possibly I haven't framed it well. Let my try again -

             

            I have a server side stored procedure which does a raise_application_error with the user-defined message, say, "hello world". I need this message to be displayed in the form from where the procedure is called. I get the message to be displayed using just the simple message(SQLERRM) statement in forms. However, the message that is displayed is something like this "ora-200001: hello world. ora-06512 blah..blah....". What I want to display is just the hello world which is the user - defined message I have coded. Now, there are ways to do that by writing other use-defined functions/procedures in form libraries (as you have rightly demonstrated) or server-side, which is fine. However, what I want to know is whether there is any oracle provided methods which allow this.

             

            thanks

            • 4. Re: Display RAISE_APPLICATION_ERROR message in oracle forms
              AnnEdmund

              Hi.. Are you looking for something like this?

              CREATE OR REPLACE PROCEDURE proc1(v_in          NUMBER,
                                                v_errmsg  OUT VARCHAR2)
              AS
              helloworld_exception EXCEPTION;
              BEGIN
              IF (v_in > 10) THEN
                 RAISE helloworld_exception;
              ELSE
                 v_errmsg := 'CORRECT';
              END IF;
              EXCEPTION
                WHEN helloworld_exception THEN
                 v_errmsg := 'HELLO WORLD';
              END;
              /

              --- Testcases
              SQL> SET SERVEROUTPUT ON;
              SQL> DECLARE
                2  v_outval VARCHAR2(20);
                3  BEGIN
                4   proc1(11,v_outval);
                5   DBMS_OUTPUT.PUT_LINE(v_outval);
                6  END;
                7  /
              HELLO WORLD

              PL/SQL procedure successfully completed.

              SQL> DECLARE
                2  v_outval VARCHAR2(20);
                3  BEGIN
                4  proc1(10,v_outval);
                5    DBMS_OUTPUT.PUT_LINE(v_outval);
                6   END;
                7  /
              CORRECT

              PL/SQL procedure successfully completed.

              • 5. Re: Display RAISE_APPLICATION_ERROR message in oracle forms
                CraigB

                user597769 wrote:

                 

                Folks,

                 

                thanks for all your efforts ! however, I don't think you guys have understood my requirement very well - or possibly I haven't framed it well. Let my try again -

                 

                I have a server side stored procedure which does a raise_application_error with the user-defined message, say, "hello world". I need this message to be displayed in the form from where the procedure is called. I get the message to be displayed using just the simple message(SQLERRM) statement in forms. However, the message that is displayed is something like this "ora-200001: hello world. ora-06512 blah..blah....". What I want to display is just the hello world which is the user - defined message I have coded. Now, there are ways to do that by writing other use-defined functions/procedures in form libraries (as you have rightly demonstrated) or server-side, which is fine. However, what I want to know is whether there is any oracle provided methods which allow this.

                 

                thanks

                Well, the value of the SQLERRM is just a string, so you can use Oracle Built-ins to substring, trim, etc., the SQLERRM string anyway you want. First, you know that the "user-defined" exception will start with "ORA-200001: "  therefore, you can start the process by removing this from the string.  For example:

                 

                DECLARE
                  v_err_msg VARCHAR2(240) := SQLERRM;
                  v_disp_msg VARCHAR2(240);
                BEGIN
                  v_disp_msg := substr(v_err_msg,13,Length(v_err_msg));
                  Clear_Message;
                  Message(v_disp_msg);
                  Pause;
                 END;
                

                 

                The output from this will be: 'Hello World. ORA-06512 blah..blah....".

                 

                Now that you've gotten rid of the first part (the User Defined Error number), you can remove the rest by substringing the SQLERRM up to where the ORA-06512 message starts.  Expanding on the above sample code...

                 

                 

                DECLARE
                  v_err_msg VARCHAR2(240) := SQLERRM;
                  v_disp_msg VARCHAR2(240);
                 BEGIN
                  v_disp_msg := substr(v_err_msg,13,Length(v_err_msg));
                  v_disp_msg := substr(v_disp_msg,1,instr(v_disp_msg,'ORA',1)-1);
                  Clear_Message;
                  Message(v_disp_msg);
                  Pause;
                END;
                

                 

                The output from this will be: 'Hello World. '.  This is a fairly simplistic example, but it demonstrates how to parse the string.

                 

                Craig...

                • 6. Re: Display RAISE_APPLICATION_ERROR message in oracle forms
                  Gurujothi

                  Hi Parag,

                   

                    Based on some validation you want to display message to user right???

                   

                    Create the following procedure in your form(Program Unit)

                   

                  PROCEDURE p_message(p_string varchar2) IS

                  BEGIN

                    fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

                    fnd_message.set_token('MESSAGE',p_string);

                    fnd_message.show;

                  END;

                   

                  and wherever you want to show message just call the procedure as the following,

                   

                  e.g

                  p_message('Sorry,Invalid password');

                   

                  Regards,

                  Guru.