ORA-01036: illegal variable name/number

Errors in Oracle application:

• ORA-01036: illegal variable name/number
• FRM-40502: ORACLE error: unable to read list of values.

Reasons for populate above errors:

• I have created one form and it’s similar to quote Search form.
• There are three buttons I gave, the followings are found, clear and cancel.
• I have created one LOV and it executes when finding button pressed.
• For my new requirements, I have changed that LOV query in record group.
• I just comment the one line in LOV and I have uploaded custom form then I try to click the find button, “Error: ORA-01036: illegal variable name/number” came. See the below query,

1. SELECT   hp.party_name "CUSTOMER NAME", hca.account_number, qh.quote_name,
            qs.status_code, sal.NAME "SALES PERSON", qh.quote_number
       FROM aso_quote_headers_all qh,
            aso_quote_statuses_b qs,
            hz_cust_accounts hca,
            hz_parties hp,
            ra_salesreps_all sal
      WHERE 1 = 1
        AND qh.quote_status_id = qs.quote_status_id
        AND qh.resource_id = sal.resource_id
        --AND qh.cust_account_id= hca.cust_account_id(+)
        AND hp.party_id = hca.party_id(+)
        AND qh.cust_party_id = hp.party_id
        AND qh.max_version_flag = 'Y'
        AND qh.org_id = 1
        AND UPPER (hp.party_name) = UPPER (NVL (:party_name, hp.party_name))
        AND NVL (hca.account_number, '@') =
                             NVL (:account_number, NVL (hca.account_number, '@'))
        AND UPPER (qh.quote_name) LIKE UPPER (NVL (:quote_name, qh.quote_name))
        AND UPPER (qs.status_code) = UPPER (NVL (:status_code, qs.status_code))
        AND UPPER (sal.NAME) = UPPER (NVL (:sales_person, sal.NAME))
        AND qh.quote_number = NVL (:quote_number, qh.quote_number)
   ORDER BY qh.last_update_date DESC

Solution:

• Just remove the comment line in the LOV then upload the customs form, it will work. 

1. SELECT   hp.party_name "CUSTOMER NAME", hca.account_number, qh.quote_name,
            qs.status_code, sal.NAME "SALES PERSON", qh.quote_number
       FROM aso_quote_headers_all qh,
            aso_quote_statuses_b qs,
            hz_cust_accounts hca,
            hz_parties hp,
            ra_salesreps_all sal
      WHERE 1 = 1
        AND qh.quote_status_id = qs.quote_status_id
        AND qh.resource_id = sal.resource_id
        AND hp.party_id = hca.party_id(+)
        AND qh.cust_party_id = hp.party_id
        AND qh.max_version_flag = 'Y'
        AND qh.org_id = 1
        AND UPPER (hp.party_name) = UPPER (NVL (:party_name, hp.party_name))
        AND NVL (hca.account_number, '@') =
                             NVL (:account_number, NVL (hca.account_number, '@'))
        AND UPPER (qh.quote_name) LIKE UPPER (NVL (:quote_name, qh.quote_name))
        AND UPPER (qs.status_code) = UPPER (NVL (:status_code, qs.status_code))
        AND UPPER (sal.NAME) = UPPER (NVL (:sales_person, sal.NAME))
        AND qh.quote_number = NVL (:quote_number, qh.quote_number)
   ORDER BY qh.last_update_date DESC

Share: