I’m trying to declare a string and use it in a select statement but it’s throwing ORA-00904: invalid identifier.
x
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.
Advertisement
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.