title: Invalid identifier error in oracle procedure for database link
body: <p>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 <code>ORA-00904: "DB_CONNECTION_NAME": invalid identifier</code>. I have declared the variable but still i am getting this error.</p>
  
  <pre><code>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 &lt; 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 &gt; LAST_SM_ID';
  
      EXECUTE IMMEDIATE l_sql;
  
    END ext_sdr_received;
  </code></pre>