Invalid identifier error in oracle procedure for database link

Question

I have below procedure for which i am passing db link as a parameter and create dynamic sql statement. While executing the procedure i am getting error as ORA-00904: "DB_CONNECTION_NAME": invalid identifier. I have declared the variable but still i am getting this error.

CREATE OR REPLACE PROCEDURE "EXT_SDR_RECEIVED"(in_db_link IN VARCHAR2)
AS
  last_sm_id         NUMBER := 0;
  last_capt_date     DATE;
  l_sql              VARCHAR2(5000);
  db_connection_name VARCHAR2(100);
  BEGIN

    SELECT db_link INTO db_connection_name
    FROM rator_monitoring_configuration.db_connection
    WHERE db_link = in_db_link;

    --DELETE DATA FROM TEMP_SDR_RECEIVED
    DELETE FROM temp_sdr_received WHERE create_date < SYSDATE - 7;

    -- first retrieve the last id (of the newest record) which has been imported at last extraction
    SELECT last_task_id INTO last_sm_id 
    FROM capturing WHERE db_table = 'TEMP_SDR_RECEIVED';

    SELECT capturing_date INTO last_capt_date
    FROM capturing WHERE db_table = 'TEMP_SDR_RECEIVED';

    dbms_output.PUT_LINE('DB' || db_connection_name);
    -- retrieve all new records from remote SDR_O2 table and insert it into TEMP_SDR_RECEIVED where ID is greater than LAST_SM_ID

    l_sql := 'INSERT INTO TEMP_SDR_RECEIVED(ID,RATING_CODE,A_NUMBER,CREATE_DATE,VOUCHER_ATTEMPT_ID,RATOR_BRAND_ID,BRAND_ID,STATUS_DESCRIPTION,ACCOUNT_PAYMENT_ID,SUBSCRIPTION_ID,DB_LINK)
              SELECT SD.ID,SD.RATING_CODE,SD.A_NUMBER,to_date(substr(SD.ID, 1, 8), ''YYYYMMDD''),VA.ID,VA.BRAND_ID,BR.BRAND_ID,VA.STATUS_DESCRIPTION,VA.ACCOUNT_PAYMENT_ID,VA.SUBSCRIPTION_ID,DB_CONNECTION_NAME
                FROM SDR_O2@' || db_connection_name || ' SD
                JOIN VOUCHER_ATTEMPT@' || db_connection_name || ' VA
                  ON SD.ID = VA.SDR_ID,
                     RATOR_MONITORING_CONFIGURATION.BRAND BR
               WHERE VA.BRAND_ID IS NOT NULL
                 AND BR.RATOR_BRAND_ID = VA.BRAND_ID
                 AND SD.RATING_CODE=''VOUCHER'' 
                 AND VA.STATUS_DESCRIPTION = ''USSD voucher''
                 AND SD.ID > LAST_SM_ID';

    EXECUTE IMMEDIATE l_sql;

  END ext_sdr_received;

i am sorry table doesn't have DB_connection_name column but i want to insert the parameter in this field as a db link — Andrew, Dec 18 '15 at 13:45

sstan · Accepted Answer · 2015-12-18T13:53:58.793

1

You are referencing DB_CONNECTION_NAME in the select part of your dynamic query (look after VA.SUBSCRIPTION_ID). Do any of your 3 tables have that column? I suspect not.

I suspect that you wanted to select the value in the DB_CONNECTION_NAME variable instead. To do that, change that last part of the SELECT in your dynamic query like this:

'...,VA.SUBSCRIPTION_ID, ''' || DB_CONNECTION_NAME || '''
...

You may also want to look into how execute immediate supports parameter binding, so that, where possible, you can avoid having to write this ugly string concatenation code.

Also, I notice you are mixing join notations. That's asking for trouble. Stick to ANSI JOIN syntax.

edited Dec 18 '15 at 13:53

answered Dec 18 '15 at 13:45

sstan

35,425
6
48
66

i have changed that condition according to you said. Now i am getting error as ORA-00904: "LAST_SM_ID": invalid identifier – Andrew Dec 18 '15 at 13:47
It's the same problem, but with the `last_sm_id` variable this time. The way you've referenced it, Oracle is looking for a table column called `last_sm_id`. Instead, you want to reference the variable and concatenate the value into your dynamic query. `and SD.ID > ' || LAST_SM_ID;` – sstan Dec 18 '15 at 13:50

Invalid identifier error in oracle procedure for database link

1 Answers1