ORA-00904 invalid identifier in PL/SQL

Question

CREATE OR REPLACE PROCEDURE PURGE_PROGRAM
AS
BEGIN
   DECLARE
   v_param          VARCHAR2(3500);
   v_sql            VARCHAR2(500);
   v_purge_count    NUMBER(17);

  BEGIN
    SELECT param INTO v_param FROM PARAMETERS WHERE NAME='rententionPeriod';
    dbms_output.put_line('Param: '||v_param);

   IF v_param IS NOT NULL THEN
      SELECT COUNT(*) INTO  v_purge_count
      FROM
      (
         SELECT * FROM tbl1 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param
         UNION ALL
         SELECT * FROM tbl2 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param
      )x;

     v_sql := 'INSERT INTO tbl1_arc
               SELECT * FROM tbl1 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param';
EXECUTE IMMEDIATE v_sql;

     v_sql := 'INSERT INTO tbl2_arc
               SELECT * FROM tbl2 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param';
   EXECUTE IMMEDIATE v_sql;
   END IF;
  END;   
END PURGE_PROGRAM;
/

Above procedure, v_param is able to output with correct value, however subsequently I'm getting exception ORA-00904: "V_PARAM": invalid identifier Perhaps v_param is not accessible from the String v_sql?

Answer 1

It must be the later code when i refer v_param in a SQL string that need to be execute

Yes. v_param is out of scope within the dynamic SQL you are executing.

You need to use a bind variable in your dynamic SQL, and pass the value in:

v_sql := 'INSERT INTO tbl1_arc
               SELECT * FROM tbl1 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > :v_param';
EXECUTE IMMEDIATE v_sql USING v_param;

db<>fiddle

---

Not directly relevant, but it would be better to avoid modifying the table column value before comparing it; so you could do:

WHERE UPDATE_DATE > TRUNC(SYSDATE) - v_param

or possibly depending on how you're treating the cut-off - so check which values are returned and which you actually want:

WHERE UPDATE_DATE >= TRUNC(SYSDATE) + 1 - v_param

That would allow an index on UPDATE_DATE to be used, and involves less work overall even without an index.