Oracle PL/SQL ORA-00904: invalid identifier

I’m trying to declare a string and use it in a select statement but it’s throwing ORA-00904: invalid identifier.

DECLARE
    var_laufi VARCHAR2(20) := 'JEIV';
BEGIN
    EXECUTE IMMEDIATE q'[
    WITH aux AS (
        SELECT DISTINCT
            zbuag_id AS ca_dat
        FROM
            vw_penddv2
        WHERE
            category = 'Issue'
    ), aux2 AS (
        SELECT DISTINCT
            vkont AS ca_lock
        FROM
            cdc.uap_dfkklocks@rbip
        WHERE
            laufi = ]'
                      || var_laufi
                      || q'[
            AND tdate >= to_char(sysdate, 'YYYYMMDD')
    )
    SELECT
        *
    FROM
        aux    a
        FULL OUTER JOIN aux2   b ON b.ca_lock = a.ca_dat
    WHERE
        ( a.ca_dat IS NULL
          OR b.ca_lock IS NULL )
    ]'
                      ;
END;

However, if I just try to display the variable itself, it works fine.

SET SERVEROUTPUT ON;

DECLARE
    var_laufi VARCHAR2(20) := 'JEIV';
BEGIN
    dbms_output.put_line(var_laufi);
END;

Result:

JEIV


PL/SQL procedure successfully completed.

I’m missing something here but I can’t figure out what is it.

Answer

Dynamic SQL is evil if you misuse it. Rule of thumb: never execute it if you didn’t check what you’re executing! How? Display the statement. Here’s how:

DECLARE
   var_laufi  VARCHAR2 (20) := 'JEIV';
   var_str    VARCHAR2 (2000);
BEGIN
   var_str := q'[
    WITH aux AS (
        SELECT DISTINCT
            zbuag_id AS ca_dat
        FROM
            vw_penddv2
        WHERE
            category = 'Issue'
    ), aux2 AS (
        SELECT DISTINCT
            vkont AS ca_lock
        FROM
            cdc.uap_dfkklocks@rbip
        WHERE
            laufi = ]' || var_laufi || q'[
            AND tdate >= to_char(sysdate, 'YYYYMMDD')
    )
    SELECT
        *
    FROM
        aux    a
        FULL OUTER JOIN aux2   b ON b.ca_lock = a.ca_dat
    WHERE
        ( a.ca_dat IS NULL
          OR b.ca_lock IS NULL )
    ]';

   DBMS_OUTPUT.put_line (var_str);
END;
/

The result (formatted):

WITH
   aux
   AS
      (SELECT DISTINCT zbuag_id AS ca_dat
         FROM vw_penddv2
        WHERE category = 'Issue'),
   aux2
   AS
      (SELECT DISTINCT vkont AS ca_lock
         FROM cdc.uap_dfkklocks@rbip
        WHERE     laufi = JEIV                                  --> here
              AND tdate >= TO_CHAR (SYSDATE, 'YYYYMMDD'))
SELECT *
  FROM aux a FULL OUTER JOIN aux2 b ON b.ca_lock = a.ca_dat
 WHERE (   a.ca_dat IS NULL
        OR b.ca_lock IS NULL)

See the comment? where laufi = JEIV is invalid, should have been enclosed into single quotes.

Also, is tdate really a string? You’re comparing it to sysdate represented as a string in specified format. If that’s so, bad, BAD idea to store DATE datatype values as strings.