ORA-01791 not a SELECTed expression

Hi fellas,

ORA-01791 not a SELECTed expression is kind of an untraceable error when i had came across in a first place. Oracle form which has a LOV (List of Values) pops up an error "ORACLE ERROR : Unable to read from List of Values" when click on the LOV button or press CTRL+L key combination. 

I haven't come across the exact reason for this but this has something to do with the database changes (Not like package changes or table structure changes but due to db version changes or rack changes) since the relevant query runs fine in all the existing 10g and 11g databases except a particular database which had a major change few dates back.

The relevant query is something like,

SELECT DISTINCT receipt_no, r_amount
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

Problem occurred because of the combination of the DISTINCT keyword with the ORDER BY keyword. To resolve this,

• Remove the DISTINCT keyword if there is a trigger available in relevant to eliminate entering duplicate data in to the table.
• Select all the columns which are using for ORDER BY clause along with the needed columns (In this scenario, you don't have to remove the DISTINCT keyword).

The query will be like,

SELECT receipt_no, r_amount
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

or

SELECT DISTINCT receipt_no, r_amount, creation_date
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

Happy coding...:)

How to handle "100501 Non-Oracle exception" in a simple way

Hi fellas,

                       I have comacross 100501 Non-Oracle exception in one of my oracle form yesterday. 100501 is the error code of FORM_TRIGGER_FAILURE. In my form, after firing some validation, this non oracle exception message poped up and then gone after click on the ok button on the message box. This message didn't do any harm my form's functionality but do kind of a harm in QC (quality controlling) phase.

                       the previous developer who modified the form i'm working now used RAISE FORM_TRIGGER_FAILURE in exception handling area. The original code is,

EXCEPTION
    WHEN OTHERS THEN
        set_application_property(CURSOR_STYLE, 'DEFAULT');
        alert_message(110,SQLERRM);
        ROLLBACK;
        RAISE FORM_TRIGGER_FAILURE;

The relevant exception in my scenario is a FORM_TRIGGER_FAILURE. Therefore the relevant error code is displaying via alert_message. In a scenario like this simply you can comment the relevant code which is responsible of displaying error messages or you could simple write a small coding part like below to handle that scenario separately.

EXCEPTION 
    WHEN FORM_TRIGGER_FAILURE THEN
        set_application_property(CURSOR_STYLE, 'DEFAULT'); 
        ROLLBACK;
        RAISE FORM_TRIGGER_FAILURE;

    WHEN OTHERS THEN 
        set_application_property(CURSOR_STYLE, 'DEFAULT'); 
        alert_message(110,SQLERRM);
        ROLLBACK;
        RAISE FORM_TRIGGER_FAILURE;

Most valuable code is,
WHEN FORM_TRIGGER_FAILURE THEN
RAISE FORM_TRIGGER_FAILURE;