FRM-40502: ORACLE error: unable to read list of values

This is what Oracle has to say about this error:
---

FRM-40502: ORACLE error: unable to read list of values.

Cause:  A fatal error occurred while trying to read a list of values.

Action:  Contact your DBA or an Oracle support representative.

Level:  >25

Type:  Error

---

I had not encountered this error till recent times. But it was today I had encountered this error. I was scratching my head as to what caused this error.

I was working with a form with 6i release, converted to 10g. In that while clicking F9 for LOV in one of the fields, I encountered this error. I started debugging the form, starting from Field, LOV, Record Group. There were no problems at first sight.

I copied the SQL query from the record group and ran it in SQL*Plus, there was no problems. The query was using some bind variables from inside the form. I had modified these fields with values.

I then tried to run exactly the same where clause of the query (I commented out the bind variables) and ran the form. Bingo! It worked and the FRM-4502 error suddenly is gone.

I then tried to run the previous query (with bind variables), now also the error did not came.

I suppose this error is due to the ID of the field with which Forms works rather than the name.

It made a super note for me today...

Hope if you are in same mood, use this logic to solve your problem.

Regards
Anantha

15 comments :

  1. Can you explain this solution a little more. I have the same EXACT problem.
    Thanks

    ReplyDelete
  2. I ran into same problem today. If the first column in your record group is a concatenation (or other sql function), do not give an alias for this column.

    My record group was something like this:

    Select a||b as col1, c from table.

    The LOV was filtered (Filter before dispay = yes). The result was FRM 4502.

    I changed the record group to

    Select a||b, c from table

    and everything works fine. You can still provide a diplay alias in the "Column mapping properties" of LOV.

    ReplyDelete
  3. Hi this is janardhan.
    Iam facing the same error in forms 6i.
    Iam trying to override the seeded LOV by passing the Date parameter to it through form personalization.But it giving the error when am trying to open the LOV as ,
    frm-40502: oracle error: unable to read list of values.
    But it is working fine when am hardcoding the values instead of bind variables...
    Please suggest me where am doing the mistake.
    Thanks in adavance.

    ReplyDelete
  4. It is high-time to say some concrete solution for this problem:

    1. Try removing the LOV and Record Group and then create it manually with hard coded values. If it runs do the next step
    2. Modify the hard coded values to bind variables.


    This should work in most of the cases. Even after this the problem is existing then comment back in this post.

    ReplyDelete
  5. Hi Ananatha,

    Thanks for ur response. But am not getting it.
    Can you will be a bit brief plz.You mean to say that i have to delete the existing(seeded) LOV and Record group

    ReplyDelete
  6. Yes, make sure you did a backup of your form before you do that...

    ReplyDelete
  7. But it's a seeded form.How to delete the LOV and Record group.I tried to delete the Seeded Record group as you said using Custom.pll and tried to attach my custom record gruop for that seeded LOV.But it is giving the following error.
    FRM-41075: Error deleting Group. and then this error is getting,
    FRM-41072: Cannot create Group TAGS_LOT......

    ReplyDelete
  8. is it possible for you to send me the original-look-alike query to me? I want to know the columns in it. Do not send me the original query if it is not shareable. send to my email tlananthu@gmail.com

    ReplyDelete
  9. Please find the mail...

    ReplyDelete
  10. when i created other users on my database , if i try to execute on forms using the new user i created this error " unable to read list of values pop up

    ReplyDelete
  11. Hi by trial and error, i found that some times if the column length of the lov's exceed the total size length then this error will come, decreasing or limiting the column lengths solves this problem.
    FRM-40502: ORACLE error: unable to read list of values.

    ReplyDelete
  12. I had the same issue today . I removed the column alias and recompile the form . No errors.
    Thanks Anantha .

    ReplyDelete
  13. Hi Anantha ,

    I have an issue with the LOV in oracle forms. The columns in the LOV query are emp no , last name, first name, dept no, dept name.
    I am running this form from the Oracle applications .
    When the user searches this LOV for a list of values , is there a way I can restrict his search to empno and last name but still be able to display all the columns in the LOV?

    ReplyDelete
  14. I had to delete the user and re-create it again to copy from other user

    ReplyDelete
  15. Remove any comments written in records group query.

    ReplyDelete