FRM-40502: ORACLE error: unable to read list of values.
I have a button that has a trigger WHEN-BUTTON-PRESSED which calls the Show_LOV function in Forms that will then return a TRUE or FALSE boolean value. If true will display the LOV based on my result set from the query. If I look at the LOV I see the Record group. I go to the record group and look at the query and everything is fine. I even run the query separately in TOAD and still all the data comes up fine as the same user I'm logged in as.
But in the FORM I get this error. Here's the info on this error:
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
But nothing is wrong with the database.
This is on Oracle 9i Developer Suite Forms connected to an Oracle 9i database on Windows XP.
Thanks,
Gio
But in the FORM I get this error. Here's the info on this error:
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
But nothing is wrong with the database.
This is on Oracle 9i Developer Suite Forms connected to an Oracle 9i database on Windows XP.
Thanks,
Gio
ASKER
Spoke to soon. It turns out I still can't read it in Oracle 9i Developer Suite. In Oracle 5.0 and 6i I have no problem.
Gio
Gio
ASKER
Okay NOW I found my answer and will like to leave the answer here and then have it closed. Here's my query that the LOV based on the RECORDGROUP would execute:
SELECT coy_comment_type_cd, coy_comment_type_desc, coy_comment_order
FROM dts_comment_type
WHERE coy_group_cd = 'CTR'
AND coy_status = 'A'
AND coy_comment_order < 90
AND coy_comment_order >= (SELECT MIN(coy_comment_order)
FROM dts_comment_type
WHERE (((coy_comment_type_cd = :dts_contract.ctc_contract
(:dts_contract.ctc_contrac
)
OR (:dts_application.app_dlr_
ORDER BY coy_comment_order ASC;
Now if I were to run this query in SQL+, TOAD, etc...it would come up with the values. Same goes for running the Form in 5.0, and 6i.
Now in Oracle 9i Developer Suite Forms it's a DIFFERENT story. If you look at the sub-query within my query, it's get a MINimum value. Well in my examples the data block item ctc_contract_status would be NULL thus when Forms tried to execute the query, the MIN value would be NULL. Thus the outer query would fail at the last AND clause of "coy_comment_order >= NULL". That's where the bug was. Thus I changed the MIN to be:
MIN(NVL(coy_comment_order,
to guarantee a value returned.
Gio
SELECT coy_comment_type_cd, coy_comment_type_desc, coy_comment_order
FROM dts_comment_type
WHERE coy_group_cd = 'CTR'
AND coy_status = 'A'
AND coy_comment_order < 90
AND coy_comment_order >= (SELECT MIN(coy_comment_order)
FROM dts_comment_type
WHERE (((coy_comment_type_cd = :dts_contract.ctc_contract
(:dts_contract.ctc_contrac
)
OR (:dts_application.app_dlr_
ORDER BY coy_comment_order ASC;
Now if I were to run this query in SQL+, TOAD, etc...it would come up with the values. Same goes for running the Form in 5.0, and 6i.
Now in Oracle 9i Developer Suite Forms it's a DIFFERENT story. If you look at the sub-query within my query, it's get a MINimum value. Well in my examples the data block item ctc_contract_status would be NULL thus when Forms tried to execute the query, the MIN value would be NULL. Thus the outer query would fail at the last AND clause of "coy_comment_order >= NULL". That's where the bug was. Thus I changed the MIN to be:
MIN(NVL(coy_comment_order,
to guarantee a value returned.
Gio
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Look at this
MY QUERY IS
SELECT DISTINCT J.*,V.VAN_NAME,V.ID ,J.SALESREP_ID AS R_SALESREP_ID,J.NAME AS R_S_NAME,J.WAREHOUSE_ID
FROM JTF_RS_SALESREPS J,VAN V,ROUTE R
WHERE J.WAREHOUSE_ID IN
(SELECT ORG_ID FROM USER_ORG WHERE USER_ID=:GLOBAL.USERID)
AND
R.VAN_ID=V.ID
AND
J.SALESPER_ID=R.SALESREP_I
QUERY IT ON PL/SQL IT GET DATA BUT ON FORMS 10G GET ERROR FRM-40502
SELECT DISTINCT J.*,V.VAN_NAME,V.ID ,J.SALESREP_ID AS R_SALESREP_ID,J.NAME AS R_S_NAME,J.WAREHOUSE_ID
FROM JTF_RS_SALESREPS J,VAN V,ROUTE R
WHERE J.WAREHOUSE_ID IN
(SELECT ORG_ID FROM USER_ORG WHERE USER_ID=:GLOBAL.USERID)
AND
R.VAN_ID=V.ID
AND
J.SALESPER_ID=R.SALESREP_I
QUERY IT ON PL/SQL IT GET DATA BUT ON FORMS 10G GET ERROR FRM-40502
ASKER
I'll close this question.
Gio