1 2 Previous Next 16 Replies Latest reply on Oct 10, 2009 4:39 PM by 530623

    Capture CDM Ruleframe / Trigger error messages into htmldb

    417747
      Hi,

      Does someone know how to convert the headstart cdm ruleframe errormessages(or other messages generated by db triggers) into user friendly errormessages in htmldb ?
      In sql/plus the errormessage stack can be displayed with the headstart script "messages.sql". Is it possible to do the same in htmldb ?

      Example: At this moment a message like this is displayed when trying to delete a record with a child:

      ORA-20999: ORA-06512: at "HST65.CG$ERRORS", line 566 ORA-06512: at "TMI_SYS.CG$TMI_USER_DATA", line 469 ORA-20998: Transaction Failed ORA-06512: at "HST65.QMS$ERRORS", line 128 ORA-06512: at "HST65.QMS_TRANSACTION_MGT", line 900 ORA-06512: at "TMI_SYS.CG$ADS_TMI_USER_DATA_ROLES", line 46 ORA-04088: error during execution of trigger 'TMI_SYS.CG$ADS_TMI_USER_DATA_ROLES' ORA-06512: at "TMI_SYS.CG$ADS_TMI_USER_DATA", line 41 ORA-04088: error during execution of trigger 'TMI_SYS.CG$ADS_TMI_USER_DATA'

      Greetings,

      Chris
        • 1. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
          Sergio-Oracle
          Chris,

          I imagine that one way to display a friendly error message is to wrap your delete statement in a exception handler and trap the exception. In the exception block, you can set an item value which can be used as the error message.

          Sergio
          • 2. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
            60437
            Chris,

            If I'm not mistaken you'll have to do something like what Sergio said, but htp.p the message the way you want it to appear right from the exception block. If you try to set an item with an assignment statement, it might fail because that involves doing a commit and you're still in the trigger context. Another thing you can try is instead of assigning the value use this call:
              htmldb_application.save_in_substitution_cache (p_name => 'P1_ERR_TEXT', p_value => 'error message');
            then use the named item in the error message after raising another error in the exception block so that the engine knows your process failed.

            Scott
            • 3. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
              417747
              Thanks for your help so far!

              I'm pretty new to htmldb so I hope you can explain me how to wrap the delete statement in an exception handler ??
              Where do I have the put the code ?
              I now have a delete button which submits the form, don't know where the delete actually takes place.

              The same for updating a form. When a field is updated with a value that is not permitted by a business rule; how and where to capture this error ?

              Chris
              • 4. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                Learco Brizzi
                Hi Chris,

                That's the same problem I encountered recently.

                The problem starts when you use the default generated wizard code for row processing. So there will be no 'wrapping of your delete statement', because there isn't one generated for you. You don't want to code dml processing yourself (again), especially when you use headstart with ruleframe.

                The result of the default behavior of wwv_flow.accept is an html error message.

                The trick is to capture the html error message automatically in an item, so you can use pl/sql to further handle the error (ie. substringing the error message and a lookup table). Here's how I did it:

                Remove the default by the wizard generated 'Process Error Message' in the 'process row of <table_name>'-process , so the message will be send to the error part in your template.

                In your template add the following javascript at the 'Error Page Template Control' part:

                (apologize for my bad javascript, I have a PL/SQL background)

                <script>
                var re = new RegExp (':', 'gi');
                var strText = new String();
                strText = '#MESSAGE#';
                var newstr = strText.replace(re, '');
                newstr = escape(newstr);
                var myUrl = "f?p=&APP_ID.:200:&APP_SESSION.::::P200_TEKST:" + newstr;
                window.location.replace(myUrl);
                </script>

                The javascript does the following:
                - remove the 'url unfriendly' characters from the error message
                - call an custom made error page (200) and puts the message in an item (P200_TEKST)


                The error page (200) could be made of a PL/SQL region with the following PL/SQL:

                htp.p('<font color="#ff0000" face="Arial" ><b>'||
                v('P200_TEKST') ||'</b></font>');
                htp.p('<br><br>');
                htp.p('<font color ="red"><b>Terug</b></font>');
                htp.p('<br><br>');

                In this region you could call a PL/SQL function which uses the item v('P200_TEKST') to substringing the error message and use a lookup table to create a more readable message.




                Suggestion for Sergio and his team members: In a future release an error stack associated with the session state filled by wwv_flow.accept and queryable with pl/sql or an api will be a nice feature.
                • 5. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                  417747
                  Hi Learco,

                  This is exactly what I meant!

                  I followed your instructions and it almost works.
                  The only thing that isn't working (javascript error: Unterminated string constant)is: strText = '#MESSAGE#'; because it is substituted by this:

                  strText = 'ORA-20999:
                  ORA-06512: at "HST65.CG$ERRORS", line 566
                  ORA-06512: at "TMI_SYS.CG$TMI_USER_DATA", line 469
                  ORA-20998: Transaction Failed
                  ORA-06512: at "HST65.QMS$ERRORS", line 128
                  ORA-06512: at "HST65.QMS_TRANSACTION_MGT", line 900
                  ORA-06512: at "TMI_SYS.CG$ADS_TMI_USER_DATA_ROLES", line 46
                  ORA-04088: error during execution of trigger 'TMI_SYS.CG$ADS_TMI_USER_DATA_ROLES'
                  ORA-06512: at "TMI_SYS.CG$ADS_TMI_USER_DATA", line 41
                  ORA-04088: error during execution of trigger 'TMI_SYS.CG$ADS_TMI_USER_DATA'';

                  There are alot of " and ' and newlines in the error message. Is there another way to capture this error in a string ?

                  Chris
                  • 6. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                    Learco Brizzi
                    Hi Chris,

                    In my javascript I used

                    var re = new RegExp (':', 'gi');
                    var newstr = strText.replace(re, '');

                    To get rid of the ':' characters, you could use the same structure to get rid of the single and double quote's. I think you need to use escape characters in the regular expression. Let me know which code you used, so I can extend my javascript with it.

                    Success,
                    Learco
                    • 7. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                      417747
                      Learco,

                      I used exactly the same code as you:

                      <script>
                      var re = new RegExp (':', 'gi');
                      var strText = new String();
                      strText = '#MESSAGE#';
                      var newstr = strText.replace(re, '');
                      newstr = escape(newstr);
                      var myUrl = "f?p=&APP_ID.:200:&APP_SESSION.::::P200_TEKST:" + newstr;
                      window.location.replace(myUrl);
                      </script>

                      But the #MESSAGE# will be substituted by an error message like I showed in my previous message. The page will then give a javascript error. (Unterminated string constant)
                      When I look at the source of the page I see:

                      <script>
                      var re = new RegExp (':', 'gi');
                      var strText = new String();
                      strText = 'ORA-20999:
                      ORA-06512: at "HST65.CG$ERRORS", line 566
                      ORA-06512: at "TMI_SYS.CG$TMI_USER_DATA", line 469
                      ORA-20998: Transaction Failed
                      ORA-06512: at "HST65.QMS$ERRORS", line 128
                      ORA-06512: at "HST65.QMS_TRANSACTION_MGT", line 900
                      ORA-06512: at "TMI_SYS.CG$ADS_TMI_USER_DATA_ROLES", line 46
                      ORA-04088: error during execution of trigger 'TMI_SYS.CG$ADS_TMI_USER_DATA_ROLES'
                      ORA-06512: at "TMI_SYS.CG$ADS_TMI_USER_DATA", line 41
                      ORA-04088: error during execution of trigger 'TMI_SYS.CG$ADS_TMI_USER_DATA'';
                      var newstr = strText.replace(re, '');
                      newstr = escape(newstr);
                      var myUrl = "f?p=&APP_ID.:200:&APP_SESSION.::::P200_TEKST:" + newstr;
                      window.location.replace(myUrl);
                      </script>

                      So the problem is: "strText = 'ORA-20999: " and further.

                      Chris
                      • 8. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                        Learco Brizzi
                        I see what you mean. You don't have to put the
                        #MESSAGE# part inside the <script> tags. You could try
                        something like this:

                        <form name="error_form">
                        <textarea name="error_text">
                        #MESSAGE#
                        </textarea>
                        </form>

                        or

                        <form name="error_form">
                        <input type="hidden" name="error_text"
                        #MESSAGE#
                        </input>
                        </form>

                        Then refer in your javascript to the textarea (or the hidden field) with 'form_name'.'field_name'.value and let the functions strip the text. I am not sure if you have to include the form tags, maybe htmldb takes care of that (check the source).

                        <script>
                        var re = new RegExp (':', 'gi');
                        var strText = new String();
                        strText = 'form_name'.'field_name'.value ;
                        var newstr = strText.replace(re, '');
                        newstr = escape(newstr);
                        var myUrl = "f?p=&APP_ID.:200:&APP_SESSION.::::P200_TEKST:" + newstr;
                        window.location.replace(myUrl);
                        </script>

                        Let me know if it works,
                        Learco
                        • 9. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                          Sergio-Oracle
                          Learco,

                          Your suggestion for more flexible error handling makes sense and we will take it into consideration.

                          Sergio
                          • 10. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                            417747
                            Learco,

                            It works ! The message is captured, stripped and displayed on an error page.

                            But now my next question which is more complicated I guess.

                            Is it possible to show the userfriendly error message from Headstart CDM ruleframe in stead of the errors mentioned in my previous posts? There is a sql script (messages.sql) which reeds out the message stack.

                            In my example this message should appear on the error page:
                            "Error TMI-00131: 5: You can only delete a USER DATA with default = Yes, if it's the last one."
                            (this error is shown after executing the messages.sql script after the sql command which fails. This works in SQL/PLUS or a forms application)

                            Chris
                            • 11. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                              Learco Brizzi
                              Chris,

                              Good to here this part of the problem is solved. Would you show me the html / javascript you used?

                              Could you not just examine the (pl/)sql code in the message.sql file and make your own package of it? After that you could call it in your page.

                              Learco
                              • 12. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                                417747
                                Learco,

                                This is the code I used in the template:

                                <textarea name="error_text">
                                #MESSAGE#
                                </textarea>

                                <script>
                                var re = new RegExp (':', 'gi');
                                var strText = new String();
                                strText = wwv_flow.error_text.value ;
                                var newstr = strText.replace(re, '');
                                newstr = escape(newstr);
                                var myUrl = "f?p=&APP_ID.:200:&APP_SESSION.::::P200_TEKST:" + newstr;
                                window.location.replace(myUrl);
                                </script>

                                About the messages.sql; I did examine the code but the problem is, are the messages still available ?
                                The script should be executed directly after the sql statement in the same session or transaction I guess.
                                When the script is executed twice or in another session (or transaction?) no rows are returned.

                                The code of the script:

                                set serveroutput on;
                                declare
                                l_message_rectype_tbl hil_message.message_tabtype;
                                l_message_count number := 0;
                                l_raise_error boolean := false;
                                l_error varchar2(2000);
                                begin

                                cg$errors.get_error_messages
                                ( l_message_rectype_tbl
                                , l_message_count
                                , l_raise_error
                                );

                                if l_message_count > 0
                                then
                                for i in 1..l_message_count loop
                                l_error := cg$errors.get_display_string
                                ( p_msg_code => l_message_rectype_tbl(i).msg_code
                                , p_msg_text => l_message_rectype_tbl(i).msg_text
                                , p_msg_type => l_message_rectype_tbl(i).severity
                                );
                                while length(l_error) > 255
                                loop
                                dbms_output.put_line(substr(l_error,1,255));
                                l_error := substr(l_error,256);
                                end loop;
                                dbms_output.put_line(l_error);
                                end loop;
                                end if;
                                end;
                                /

                                Chris
                                • 13. Re: Capture CDM Ruleframe / Trigger error messages into htmldb
                                  Learco Brizzi
                                  Hi Chris,

                                  Here a suggestion. Try to modify the cg$errors Headstart package in such a way that the errors are not only stored in a PL/SQL table, but also in a real table along with the session number and make a function to query on that table. In fact this is the suggestion I made for Sergio.

                                  Learco
                                  1 2 Previous Next