1 2 Previous Next 21 Replies Latest reply on Jul 4, 2008 7:05 AM by 630507

    Handle ORA- errors

    552129
      Hi

      Does someone know how to handle ORA- errors from the db when I try to update or delete someting from the database (if i got a error) ?

      Best Regards
      Jesper Vels
        • 1. Re: Handle ORA- errors
          Dimitri Gielis
          Hi Jesper,

          What type of errors do you get?
          You can do a Validation before you submit your page, so you won't get a DB error.
          Or you can use the Apexlib framework to check formats in javascript (for ex. date format).

          Dimitri
          • 2. Re: Handle ORA- errors
            543580
            Normally when you get ORA- errors it means you've done something wrong, I would check any pl/sql you have(if any) and then check any constraints you have on the database and make vaildation rules to match those at a minimum, also check that you aren't exceeding character limits in varchars or entering a character or string into an interger. It would be useful to see the actual errors you were getting to give you more help.
            Hope this helps
            Luke

            Message was edited by:
            LWhitelock
            • 3. Re: Handle ORA- errors
              552129
              Hi

              I have some constraints in the db.

              Do I need to make some validation in apex (pl/sql) do control the constraints? Or can i "use" constraints so I not need to make the same "constraint"/validation code in apex?

              Or can I handle the output from the db, if I violate the constraint?

              Best Regards
              Jesper Vels
              • 4. Re: Handle ORA- errors
                Dimitri Gielis
                Hi Jesper,

                You can use an item validation for ex. SQL - Exists.
                I'm also using the ApexLib Framework which does some checks for you and allows you to make nicer error handling/messages.

                Can't you force the user not making a mistake? for ex. by using a dropdown list?

                Dimitri
                • 5. Re: Handle ORA- errors
                  369783
                  Hi Jesper,

                  In my opinion (which has been wrong in the past), like Dimitri said, using Patrick Wolf's Apexlib Framework is a nice, clean way to handle errors through the validation, before they can ever generate database errors.

                  Apexlib is pretty easy to install, it's mainly some Oracle procedures and functions, with some javascript (and a css file and image file) for the Apache/web server directory.

                  It took me a few emails back and forth with Patrick to get it working correctly for use, mainly due to my misunderstanding of what it was supposed to offer me and how it worked. Patrick was more than helpful and very prompt in getting back to me, and I'm now using it with a great improvement in my error handling and the messages displayed.

                  What I do now is simply write the validation routines, checking for valid input and the conditions that would generate an error once it made it to the database. I then "trap" those conditions and write a "nice" message to the error message queue, and then instead of sending the "bad" data to the database, the page is redisplayed with the "nice" error message instead.

                  So, instead of the user seeing "Integrity Constraint MY_TABLE_FK violated", they instead can see something like "You need to supply an Item Number".

                  Bill Ferguson
                  • 6. Re: Handle ORA- errors
                    552129
                    Hi

                    I have a table with contact person's

                    When a user login, he can maintain the contact people (delete and add),

                    In the db there is some constraints, so when the user try to delete a contact person there is a control, there must exist least 1 contact person (when I try to delete the last contact person in SQL Developer I go the error), some it could be nice to handle this in apex

                    Best Regards
                    Jesper Vels
                    • 7. Re: Handle ORA- errors
                      369783
                      How about a page validation that fires on the Delete button, "Function returning text", something like:
                      declare
                        v_count PLS_INTEGER := 0;
                      begin

                      select count(*)
                      into v_count
                      from contact_table;

                      if v_count = 1 then
                        return "Cannot Delete the last entry in table.';
                      end if;
                      end;
                      Bill Ferguson
                      • 8. Re: Handle ORA- errors
                        369783
                        I forgot to ask though, is this a form or a tabular report with a delete box next to each record?

                        Something similar to the above should work for a form, but a few changes if it's a tabular report.

                        Bill Ferguson
                        • 9. Re: Handle ORA- errors
                          Duncs
                          Jesper

                          I tend to use the APEX_LIB framework: http://inside-apex.blogspot.com/ which will validate your form items (including tabular forms as you go)

                          If you are just interested in capturing and handling oracle error messages, i put this post on my blog which shows you how to set up a custom error page that you can direct to with a nice error message rather than the oracle ORA-00001 style messages:

                          http://djmein.blogspot.com/2007/04/custom-error-handling-in-apex.html

                          Hope that points you in the right direction

                          Regards

                          Duncan
                          • 10. Re: Handle ORA- errors
                            klsharpe
                            I have a region, type SQL query (PL/SQL function body returning SQL query). The query statement returned uses an Oracle Text index, i.e., "where contains(part_name, 'BRACKET') > 0".

                            I use javascript to validate the user's search terms but it is possible a user could enter a combination of terms (that I didn't think of) and such terms still will cause an Oracle Text error (esp., one that can break the Text parser syntax) once the returned SQL statement is executed. Such as these:

                            "where contains (part_name, '%%1%%') > 0"
                            report error:
                            ORA-29902: error in executing ODCIIndexStart() routine
                            ORA-20000: Oracle Text error:
                            DRG-51030: wildcard query expansion resulted in too many terms

                            or
                            "where contains (part_name, '%%%%') > 0"
                            report error:
                            ORA-29902: error in executing ODCIIndexStart() routine
                            ORA-20000: Oracle Text error:
                            DRG-50901: text query parser syntax error on line 1, column 1

                            I have been unable to effect any changes in the region property/attibute: Region Error Message, to handle these errors.

                            How can those errors be trapped?
                            Thanks,
                            Kelly
                            • 11. Re: Handle ORA- errors
                              klsharpe
                              I have an update on this error.

                              I registered to use Apex Beta v3.1(/pls/apexbeta) and tried the new IRR type on a table (very nice feature!)

                              I also created an Oracle Text index on that table, created another page using SQL query returned using PL/SQL function body.

                              I enter ! in the Search Terms text box and can cause the Text parser syntax error (DRG-50901) to appear. I have modified the Region Error Message property to provide the formatted message I would like. It does not appear.

                              I also modified the Error Page Template Control to display a formatted message but it also does not appear to be used in this case.

                              Let me know if you (hint, hint, Apex development team member) would like to view this error and I can give you access to the app.
                              Thanks,
                              Kelly
                              • 12. Re: Handle ORA- errors
                                klsharpe
                                I thought I would bump this question again to see if anyone would like to answer. Even if someone tells me I am missing the obvious and being dense. (But still demonstrate how to override a Text parser syntax error with my own message ;-) )

                                I would like to find out how to cause information in the Region Error Message property to appear on the screen.

                                Thanks,
                                Kelly
                                • 13. Re: Handle ORA- errors
                                  klsharpe
                                  Bumping my question up front again.
                                  So is there anyone who can get the informaton in the Region Error Message (or other error template) to display in place of SQLERRM? The conditions are:

                                  The region type is SQL Query (PL/SQL function body returning SQL query).
                                  Returned SQL query uses a CONTAINS clause on a Text index.
                                  User tried some wonky syntax in the search terms and the Text engine returns a parser syntax error.

                                  With Javascript I can validate most search terms a user may try but not all. Help text can educate the user about what will not work but I still would like to display a better message.

                                  Thanks,
                                  Kelly
                                  • 14. Re: Handle ORA- errors
                                    klsharpe
                                    Ok, I have figured out how to capture this error message but it is a clunky solution.

                                    I want to avoid the Oracle Text parser syntax error for any characters the user might enter.

                                    The Search Terms item was assigned to one HTML region; the SQL Query (PL/SQL function body..) to display my report defined in the next region.

                                    A validation was added in page processing...

                                    Name: P1_SEARCH_TERMS
                                    Type: Function Returning Error Text
                                    Validation Expression 1:

                                    declare
                                    my_count number;
                                    Oracle_Text_Error EXCEPTION;
                                    PRAGMA EXCEPTION_INIT(Oracle_Text_Error, -29902);
                                    begin
                                    select count(1) into my_count
                                    from demo_product_info
                                    where
                                    contains(product_name, '' || :P1_SEARCH_TERMS || '') > 0;
                                    return null;
                                    exception when oracle_text_error then
                                    return 'Validation check...Here is the Text error.';
                                    end;

                                    Error Message: Got this error.
                                    Error message display location: Inline in Notification
                                    Associated Item: P1_SEARCH_TERMS

                                    In the next region for the report, the SQL Query, now has a conditional display selected...

                                    Condition Type: No Inline Validation Errors Displayed

                                    So now when ! or some other wonky characters are entered into search terms,
                                    the inline validation message states:
                                    Validation check...Here is the Text error.

                                    and the second region is not displayed.

                                    SO, with all that demonstrated, I still don't understand the point of the Region Error Message - what does it do and what conditions is it used? Or why the text below cannot be suppressed when the item and report are in one region?

                                    report error:
                                    ORA-29902: error in executing ODCIIndexStart() routine
                                    ORA-20000: Oracle Text error:
                                    DRG-50901: text query parser syntax error on line 1, column 2

                                    Finally, we will likely use a solution where as much Javascript validation will be performed before issuing the GO request and educate the user with help text to use smarter terms. It is preferable to run the query once and not have to pre-check it for Text exceptions.

                                    Thanks,
                                    Kelly
                                    1 2 Previous Next