This discussion is archived
13 Replies Latest reply: Mar 29, 2013 5:32 AM by 706722 RSS

Validation returns : no data found + unhandled user-defined exception

836384 Newbie
Currently Being Moderated
Hi, I'm working with Application Express 4.1.1.00.23.

I have an application for timesheet management.
On one page, I've got a report (not interactive) in which you can enter the hours you've worked. First you add a free line, choose the task and then enter the hours you've spent.

You can only have one empty line at a time. If you try to add a line when you already have an empty line, a validation will display an error. It worked fine in version 3.2. Now we upgraded to 4.1 and get a weird error :
1) The validation msg appears fine
2) The report appears fine
3) There is a msg at the end of the report saying :
report error:
ORA-01403: no data found
ORA-06510: PL/SQL: unhandled user-defined exception


Could somebody point out the source of this problem ?
Thank you!
------------------
Here is the debug :
..........
0.31117     0.00204     parse query as: FEUILLES_DE_TEMPS     4     

0.31319     0.07865     ...Execute Statement: select NULL "CHECK$01", NULL "nom_projet2", NULL "NO_TACHE_ENTRE", NULL "NO_TACHE", NULL "NO_SEMAINE_EMPLOYE", NULL "NOM_TACHE", NULL "Tâches", NULL "SEQUENCE", NULL "Heures dim", NULL "DIM", NULL "Heures lun", NULL "LUN", NULL "Heures mar", NULL "MAR", NULL "Heures mer", NULL "MER", NULL "Heures jeu", NULL "JEU", NULL "Heures ven", NULL "VEN", NULL "Heures sam", .........

0.39191     0.00158     print column headings     4

0.39342     0.02546     rows loop: 500 row(s)     4

0.41889     0.00087     report error: ORA-01403: no data found ORA-06510: PL/SQL: unhandled user-defined exception
.....
  • 1. Re: Validation returns : no data found + unhandled user-defined exception
    902143 Explorer
    Currently Being Moderated
    Hi Amelie,

    I don't know what is the validation code you are trying to execute,

    If you have any SELECT INTO statement make sure it returns a value, otherwise you may get error message saying no data found. before executing SELECT INTO. get the count of the rows. IF(COUNT>0) then execute SELECT INTO statement.
  • 2. Re: Validation returns : no data found + unhandled user-defined exception
    836384 Newbie
    Currently Being Moderated
    Hi, thanks for your answer. Here is the validation we are executing. I'm pretty sure its fine since count(*) always return a value (plus we see V2 in the debug...) :

    -------------
    declare
    l_count number;

    begin
    apex_debug_message.enable_debug_messages(7);
    apex_debug_message.log_message('-----------------------------------V1----------------------------------');
    -- get remaining empty tasks count
    SELECT count(*) into l_count
    FROM VIEW_TACHE_SYSTEME
    WHERE NO_TACHE NOT IN (SELECT DISTINCT NO_TACHE FROM TACHE_JOUR WHERE NO_SEMAINE_EMPLOYE = :P36_NO_SEMAINE_EMPLOYE);

    apex_debug_message.log_message('-----------------------------------V2----------------------------------');
    apex_debug_message.log_message(l_count);

    if l_count = 0 then
    return 'Veuillez utiliser la tâche vide avant d''ajouter une ligne.';
    else
    return null;
    end if;
    end;

    ------------
    Here is the debug :
    ---------------

    0.14643     0.00318     ...Validation "ADD_ROW_VALIDATION" - Type: FUNC_BODY_RETURNING_ERR_TEXT     4     

    0.14961     0.00318     ...Execute Statement: declare function x return varchar2 is begin declare l_count number; begin apex_debug_message.enable_debug_messages(7); apex_debug_message.log_message('-----------------------------------V1----------------------------------'); -- get remaining empty tasks count SELECT count(*) into l_count FROM VIEW_TACHE_SYSTEME WHERE NO_TACHE NOT IN (SELECT DISTINCT NO_TACHE FROM TACHE_JOUR WHERE NO_SEMAINE_EMPLOYE = :P36_NO_SEMAINE_EMPLOYE); apex_debug_message.log_message('-----------------------------------V2----------------------------------'); apex_debug_message.log_message(l_count); if l_count = 0 then return 'Veuillez utiliser la tâche vide avant d''ajouter une ligne.'; else return null; end if; end; return null; end; begin wwv_flow.g_computation_result_vc := x; end;     4     

    0.15280     0.01299     -----------------------------------V1----------------------------------     6     

    0.16578     0.00310     -----------------------------------V2----------------------------------     6     

    0.16888     0.00235     0     6     

    0.17123     0.00225     ......Result = Veuillez utiliser la tâche vide avant d'ajouter une ligne.     4     

    0.17347     0.00276     ......Did NOT pass     4     

    0.17624     0.00314     Add error onto error stack     4     

    0.17938     0.00343     ...Error data:     4     

    0.18280     0.00327     ......message: Veuillez utiliser la tâche vide avant d'ajouter une ligne.     4     

    0.18607     0.00446     ......display_location: INLINE_IN_NOTIFICATION     4     

    0.19053     0.00323     ......is_internal_error: false     4     

    0.19375     0.00323     ......error_backtrace: - PL/SQL Call Stack - object line object handle number name c000000382130f30 717 package body APEX_040100.WWV_FLOW_ERROR c000000382130f30 833 package body APEX_040100.WWV_FLOW_ERROR c00000039da9b5c8 154 package body APEX_040100.WWV_FLOW_VALIDATION c00000039da9b5c8 363 package body APEX_040100.WWV_FLOW_VALIDATION c00000039da9b5c8 784 package body APEX_040100.WWV_FLOW_VALIDATION c00000039da9b5c8 905 package body APEX_040100.WWV_FLOW_VALIDATION c00000039d1142c8 10005 package body APEX_040100.WWV_FLOW c000000381287050 31 anonymous block
  • 4. Re: Validation returns : no data found + unhandled user-defined exception
    560577 Pro
    Currently Being Moderated
    I can only add that so far I'm having the same problem.

    1. Tabular form, existed before 4.1.1.00.23 (don't know if that has anything to do with it).
    2. Popup Key LOVs. This does have something to do with it. If I change to Popup LOV I still have the problem but if I change to select-lists I don't have the problem. It's related to Popup LOVs for sure (both the key and non-key variety). Problem is, my LOVs have too many records and they blow the limit on select-list size so I'm stuck with popup LOVs.
  • 5. Re: Validation returns : no data found + unhandled user-defined exception
    846624 Newbie
    Currently Being Moderated
    Is there yet some solution for this problem?

    I'm discovering the same.
    My problem is that I do not really know which statement causes this error.
    And then obviously why this is happening, because APEX is something like an almost black box.

    For me it is only a problem when selecting some rows with the row-selector but keeping empty rows out (which fail the validation).

    So, are there any new ideas for this?
  • 6. Re: Validation returns : no data found + unhandled user-defined exception
    Cleopatra Newbie
    Currently Being Moderated
    Hi Amelie,
    have you ever found a solution for this issue?
    I seem the having the same problem.

    regards,
    Cleo
  • 7. Re: Validation returns : no data found + unhandled user-defined exception
    797499 Newbie
    Currently Being Moderated
    I had this issue and found a workaround (at least in my case).

    1. Do you have any javascript on your popup LOV field defined in the "Element Attributes"?

    2. If so, make sure it's enclosed in double quotes.

    For example (this caused my error):
    onchange=alert($v(this));
    This did not cause the error:
    onchange="alert($v(this));"
    Either way is valid javascript (there are no spaces so enclosing in double quotes should not be mandatory), but something in the Apex rendering engine gets confused at runtime or something by it. As evidenced by other item types (select-list, plain text field) where it does work, the Popup LOV should treat it no differently.

    Oracle has acknowledged it's a bug and has created bug# 14085739 for it. It's quite obscure though since the error message has nothing directly to do with the cause.
  • 8. Re: Validation returns : no data found + unhandled user-defined exception
    Cleopatra Newbie
    Currently Being Moderated
    Hi,
    Thanks for that info, I wish that was the case fro me though :(
    Checked all my javascript in the popup LOV, no idea what's causing this to happen.

    Cleo
  • 9. Re: Validation returns : no data found + unhandled user-defined exception
    560577 Pro
    Currently Being Moderated
    Bummer :-( Sorry the above wasn't the cause of your issue.

    I am sort of coming to the conclusion then that if Apex encounters any problem while painting a tabular form region, it looks like it gives a craptastic and misleading generic error (this "user defined exception" which is not defined by you I'm sure). I really wish Apex would have tighter tabular forms...not only with similar declarative functionality as with page items, but also that they wouldn't be a house of cards ready to collapse at the slightest out of the ordinary thing.
  • 10. Re: Validation returns : no data found + unhandled user-defined exception
    836384 Newbie
    Currently Being Moderated
    Hi, I am still working on that bug with oracle support. Will keep you posted If they give me any solution.
    I created the application in the simplest way possible here : feel free to try it (any username will do)
    http://apex.oracle.com/pls/apex/f?p=43554:10

    Steps :
    1) Click on "validation error"
    2) Click on "test" (this triggers a validation) based on the field "X".
    3) you'll see the no data found error.

    4) now go back to the main menu
    5) Click on "validation ok"
    6) Click test again (same validation)
    7) the validation error wont happen.

    The difference between the two ? I used a a report "with a SQL query" (no error) and another one with "SQL query (updateable report)" (error).
    Both region source are :
    SELECT
    APEX_ITEM.TEXT(1, 'HELLO') nom_tache,
    APEX_ITEM.TEXT(2, 'HELLO') nom_tache2
    from dual

    Both validation are : Item/Column in expression 1 does NOT equal expression 2 (expression 1 : P10_X; expression 2 : 1)


    Sorry for the css... no time to adjust it.
  • 11. Re: Validation returns : no data found + unhandled user-defined exception
    969796 Newbie
    Currently Being Moderated
    Hi, has anyone got a solution for this yet? Thanks
  • 12. Re: Validation returns : no data found + unhandled user-defined exception
    Nicolette Expert
    Currently Being Moderated
    Amelie

    In my test so far I have concluded that defining your region as "SQL query (updateable report)" and using apex_item syntax in your source don't mix.

    As for the error. The error isn't coming from the validation code. But because there is a validation error for that report the builder runs some code to attach a error class to the apriopriate column/row item. Because of the use of apex_item syntax that item cann't be found what results in the error message.

    Nicolette
  • 13. Re: Validation returns : no data found + unhandled user-defined exception
    706722 Newbie
    Currently Being Moderated
    I have the same error.

    In my raport there is apex_item unfortunatelly I need it. The error is shown always when I have error on page (inline in notification). My workaround is to change error to show it in error page but I hope it's just temporary.

    Anything change in that mater?

    edit: in one report I changed apex_item to text field -> I have still possible to get value via apex_application.g_xx so for me it works in one report -> i have to look to other raport if its possible to not use apex_item

    Edited by: nirud on 2013-03-29 05:30

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points