Oracle ORA-00904 error: invalid identifier-CodePudding

I am quite new to using Oracle. I am running a procedure to update a table. The procedure basically uses a table name and a column name as a parameter.

I am getting an ORA-00904. I looked into the error messages and researched about it, and found that my column names that I am passing might not be correct (accepted values are: less than or equal to 30 characters / alphanumeric and special characters $, _, and #)

My error message is:

Error in UV_KVS_EXTRACT_VALIDATION, error: ORA-00904: "CIT_DW_ETL"."TO_NUMBER_VALIDATE": invalid identifier, sql= select distinct uv.FRM_TRACK_ID, uv.INST_NUM, uv.TPST_SRV_CONTACT_DTE uv_value,  k.value kvs_value, 'KVS_EVENT_DATA' kvs_table, k.event_id kvs_id from IM_FORMS.FRM_2450_UV_mv uv join iidb_stg.fn_frm_track_mv frm on uv.FRM_TRACK_ID = frm.frm_track_id and frm.STS_CDE in ('AER','AIP','AUD','CMP','PND','QRY') join cit_udm.event e on uv.FRM_TRACK_ID = cit_dw_etl.to_number_validate(JSON_VALUE(e.sys_source_id, '$.ID1')) and uv.INST_NUM = cit_dw_etl.to_number_validate(JSON_VALUE(e.sys_source_id, '$.ID2')) join cit_udm.KVS_EVENT_DATA k on k.event_id = e.event_id where k.key = 'TPST_SRV_CONTACT_DTE'

For example, if I follow the above error message, to me it seems that column name TPST_SRV_CONTACT_DTE is incorrect. But it is alphanumeric less than 30 characters and has _ special character. It follows the format yet it is failing.

Below is the snippet of the procedure which might be failing:

 v_sql := 'select distinct uv.FRM_TRACK_ID, uv.INST_NUM, uv.' || v_uv_column || ' uv_value, '||
            ' k.value kvs_value, ''' || v_kvs_table || ''' kvs_table, k.' || case when v_kvs_table = 'KVS_EVENT_DATA' then 'event_id'
                when v_kvs_table = 'KVS_PRODUCT_DATA' then 'product_id' end || ' kvs_id' ||
            ' from IM_FORMS.' || v_uv_table || '_mv uv' ||
            ' join iidb_stg.fn_frm_track_mv frm on uv.FRM_TRACK_ID = frm.frm_track_id and frm.STS_CDE in (''AER'',''AIP'',''AUD'',''CMP'',''PND'',''QRY'')' ||
            case when v_kvs_table = 'KVS_EVENT_DATA' then
                    ' join cit_udm.event e on uv.FRM_TRACK_ID = cit_dw_etl.to_number_validate(JSON_VALUE(e.sys_source_id, ''$.ID1'')) and uv.INST_NUM = cit_dw_etl.to_number_validate(JSON_VALUE(e.sys_source_id, ''$.ID2''))' ||
                    ' join cit_udm.KVS_EVENT_DATA k on k.event_id = e.event_id where k.key = ''' || v_key || ''''
                when v_kvs_table = 'KVS_PRODUCT_DATA' then
                    ' join cit_udm.product p on uv.FRM_TRACK_ID = cit_dw_etl.to_number_validate(JSON_VALUE(p.sys_source_id, ''$.ID1'')) and uv.INST_NUM = cit_dw_etl.to_number_validate(JSON_VALUE(p.sys_source_id, ''$.ID2''))' ||
                    ' join cit_udm.KVS_PRODUCT_DATA k on k.product_id = p.product_id where k.key = ''' || v_key || ''''
                else ''
            end
            ;

This v_sql gets used downstream in the code.

I am 90% sure it is something related to the way columns are named and where it might be occurring from, but I am clueless what and how to fix it. Is it something related to changing single quotes to double quotes?

Any comments or suggestions are most welcome.

Thanks.

CodePudding user response：

I guess that your problem is in this part kvs_value, 'KVS_EVENT_DATA' kvs_table,. If you remove the 'KVS_EVENT_DATA' then it will work. I do not know what you are trying to get here.

CodePudding user response：

error: ORA-00904: "CIT_DW_ETL"."TO_NUMBER_VALIDATE"

cit_dw_etl.to_number_validate is a function call, here:

v.FRM_TRACK_ID = cit_dw_etl.to_number_validate(JSON_VALUE(e.sys_source_id, '$.ID1'))

The error indicates that Oracle can't find the function, most likely because you don't have permission to see or execute it, or because the function hasn't been created.