6 Replies Latest reply: Mar 4, 2014 5:03 AM by Gurujothi RSS

    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

        • 1. Re: Display RAISE_APPLICATION_ERROR message in oracle forms
          Soofi

          Please take a look @

          Handling PL/SQL Errors

           

           

          Regards,

          Soofi

          • 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
                AnnPricks E

                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.