This discussion is archived
13 Replies Latest reply: Aug 5, 2013 7:06 AM by gpoz RSS

problem with new DB app, report+form, report works great, form says ORA-01403: no data found

gpoz Newbie
Currently Being Moderated

I have a new table, the PK is a varchar2(5) column, when I allow the default query in the report to do its work, I get all the expected data.  when I click on the edit icon (pencil), I get an error screen indicating ORA-01403: no data found.  I'm hosed!  This was generated by the app!  no changes were made to anything in the app, except to turn off tabs at create time.  I even left the default name.

 

My ARF is hitting the right table with the PK column, but finds nothing.  I have the "success" message showing me the PK value.  What could be going on here, and how can it be addressed?  Today is the 1st time I have seen this matter.

 

I'm running 4.22 as the workspace admin, I have other apps that work fine (to expectation), my browser is FF22, though I plan a downgrade to 18.  Our DB is 11.1.0.7.

  • 1. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    jrimblas Expert
    Currently Being Moderated

    You said you have a "success" message showing you the PK value. Can you elaborate on this?

    Can you see the correct PK value in the URL? Does the item parameter match what you expect (correct page item and value)? Perhaps share that full URL here?

     

    Debug the page and see which process, item or step is actually failing. You could be running some other process on the form page and that could be what actually fails.  Treat it as if the ARF works correctly and see what else could be happening.

     

    Thanks

    Jorge

  • 2. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    gpoz Newbie
    Currently Being Moderated

    Jorge, thanks for your attention to my problem, I appreciate any insights, although there is a little clarification I can offer.  Also, if you can, please remind me the tags to use in my text that would properly set off the code snippets or prior message content?

     

    [you wrote]

    You said you have a "success" message showing you the PK value. Can you elaborate on this?

     

    The form page, under the ARF, allows for the display of a "success" message and a "failure" message.  I have seen my "success" message appear, but it didn't show my key field as a brought-back value (which I told it to include), and I think now this is not relevant any longer.  I found that there was a link on the report attributes page between #ROWID# and a P2_ROWID that was incorrect (probably from an earlier stage of dev in the app), and I changed this to my key field, and this altered the outcome of the ARF action.  This leads to ....

     

    [you wrote]

    Can you see the correct PK value in the URL? Does the item parameter match what you expect (correct page item and value)? Perhaps share that full URL here?

     

    I have expected values in my URL.  The URL does show my key value (tail end of URL underlined here):

    ../apex/f?p=120:2:7519563874482::NO::P2_VCODE:RB15

     

    [you wrote]

    Debug the page and see which process, item or step is actually failing. You could be running some other process on the form page and that could be what actually fails.  Treat it as if the ARF works correctly and see what else could be happening.

     

    I can add the detail that my 1st message was based on testing with a table where I set the PK as data type VARCHAR2, but in more testing on the actual app (whose URL piece is above) I am using a PK which is CHAR.

     

    The result of the debug effort is that APEX has built its own query for pulling back the row in the ARF, and it is joining on my PK field to an APEX item P_ROWID which I don't think I created.  Nor does it appear to offer me any avenue for correcting it.    debug snippet:    where "VCODE" = :p_rowid; end;

  • 3. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    jrimblas Expert
    Currently Being Moderated

    So sorry I didn't see this before, I never got an email.

     

    When you create the report and form APEX gives you the option to specify the PK columns or use ROWID.  When you chose ROWID you get a Px_ROWID item and the parameter passed as the PK is the ROWID.

    There seems to be a disconnect between what the wizard created and what's now on your pages.  You can change that yourself.  Looks like you don't want to use the PK.

    So go to the "Process Row of xxx" process the ARP and make sure it uses the correct item as the PK.  P2_VCODE as the item and VCODE as the column,

    Do the same thing for your ARF process (Fetch Row from xxx), user P2_VCODE as the item and VCODE as the column.  Remove the ROWID refrences.

    Also go and delete the ROWID item added to your form.

     

    As far the forums, for quoting use the bubble and quote icon on the toolbar. For code you need to click on "Use advanced editor" and then you you'll see this double greater dan symbols in blue (>>) click on them to see the Syntax Highlighting options and other options.

     

    Thanks

    -Jorge

  • 4. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    Hari_639 Guru
    Currently Being Moderated

    Hello,

     

    I suggest you delete existing ARF process and create it from scratch with new PK field. This should solve your problem, instead of we guessing whats could be the issue, with-out seeing your page

     

    Regards,

    Hari

  • 5. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    gpoz Newbie
    Currently Being Moderated

    Hari,

     

    You are right, and you're recommendation is received well.  But I am concerned with this behavior since it is the default setup of APEX, I didn't choose what it is doing:

     

    1. have a table with a natural PK (non-numeric) defined as PK constraint

    2. create a new app with no pages but a login, all default options, except set schema and "start from scratch"

    3. add pages for form+report, and specify the natural PK, populate the inserted rows in a trigger with the user-provided PK value (although this is not relevant, since I am describing only the outcome of an edit attempt)

    4. run it, and choose to edit

    5. failure in the app with NO_DATA_FOUND

    6. the default ARF structure is such as to try fetching a row with join of my PK column to the rowid (visible only in the debug log)

     

    something seems wrong in this.

  • 6. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    gpoz Newbie
    Currently Being Moderated

    actually, I DO want to use a primary key, a natural key at that.  And I was aware of the link location where I could be sure of the right use of my PK column matched to my form field.  to my great disappointment that didn't fix the problem.

     

    so when I create the form, I specified that it should use my PK column -- please see also reply by Hari and my reply back.

  • 7. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    jrimblas Expert
    Currently Being Moderated

    Hmmm, I'm with Hari, just recreate it. 

    Stuff like this does happen and sometimes it's not worth the time to resolve.  Many times the wrong option was selected or who knows what happened during the wizard steps or after.

     

    If you validated that your processes have the correct items and it all looks fine then recreate it all.  You could duplicate the app and try it if you're worried about not being able to reference something.  It's the beauty of APEX.

     

    If it's an option, you could also import your app into apex.oracle.com and we could take a look.  It doesn't need tables or be fully functional.

     

    Thanks

    -Jorge

  • 8. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    Hari_639 Guru
    Currently Being Moderated

    Ok, if you think it's some problem with APEX (bug?) then you can try out same thing @ https://apex.oracle.com/pls/apex/f?p=4550:1:0:::::

     

    If you get error, please post workspace name and guest developer credentials. I can have a look.

     

    Luckily you are using latest version of APEX which is same as hosted instance

     

    Regards,

    Hari

  • 9. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    gpoz Newbie
    Currently Being Moderated

    okay, here is the answer to the matter, although it took many efforts rebuilding a small but focused DB app to narrow it down.  Also, reference the info at APEX Automated Row Fetch Failing Due to Primary Key of Type CHAR(20) | Oracle Observer, which states it succintly.

     

    First, I had to make sure I was specifying everything correctly in the creation of the app.  Two things I altered allowed this to move forward ...

    1. Correctly set up the primary key on the column(s) in the table before starting the app creation

    2. Make sure that when the PLSQL block provides the PK value for insert, the block returns the right value, not just assigns value to page bind variables

     

    Second, having done those, I came to the matter under the link I have included

    1. A char field can't be the last column in a PK, since APEX apparently doesn't like the trailing blanks, and comparisons fail

     

    I tested with a PK with a non-CHAR, and it works fine.

    I tested with a PK with just a CHAR, and it was NO_DATA_FOUND, though data existed clearly.

    I tested with a PK with a CHAR and something else non-CHAR as the last column of a composite key, and it works fine.

     

    Not a bug, just an APEX characteristic to be aware of.

     

    technically, this all arises from a test on the apex.oracle.com site, as recommended, which worked perfectly the first time, and frustrated me enough to ddig the more for this final outcome.  THANKS, GUYS!

  • 10. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    Hari_639 Guru
    Currently Being Moderated

    Interesting, finally it boils down how CHAR data type is different from VARCHAR2 in Oracle and how Oracle behaves when comparing VARCHAR2 data with CHAR

     

    Thanks for sharing.

     

    Regards,

    Hari

  • 11. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    gpoz Newbie
    Currently Being Moderated

    Honestly, given the nature of the medium (the web browser), it might be more precisely traceable to the construction of the URL when these values are passed as parameters.  If a CHAR value is on the end, trailing spaces can be lost without another APEX-acceptable delimiter to keep them in place (":" - a colon).  In a simple experiment with my test app that failed, I was able to right-pad with space to the table-defined length of the CHAR data item, add a colon at the end, and it worked fine.

     

    The explanation under the link I provided is probably on target, but my tests appear to confirm that this is either correctable or work-around-able by making sure no CHAR field is the last parameter passed in the URL, and that appears to be decided by whatever APEX believes is the primary key, at least in this report/form situation).

  • 12. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    Sven W. Guru
    Currently Being Moderated

    As a side note: you should never ever feel the need for a PK of type CHAR. There is simply no reason for that.

    It just creates an aweful lot of problems when comparing values and offers absolutely no advantage (see also: Ask Tom "shoud I use char or varchar2")

  • 13. Re: problem with new DB app, report+form, report works great, form says ORA-01403: no data found
    gpoz Newbie
    Currently Being Moderated

    Sven, I agree 100%.  The difficulty in the real world is that sometimes you have legacy architecture to deal with, and the $$ attached to updating, changing many programs and retesting is not a good trade-off in their minds.  I read AskTom often, but many levels of decision-makers above me do not.

Legend

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