sql - Oracle PL/SQL ORA-00904:标识符无效
问题描述
我正在尝试声明一个字符串并在 select 语句中使用它,但它抛出 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;
但是,如果我只是尝试显示变量本身,它就可以正常工作。
SET SERVEROUTPUT ON;
DECLARE
var_laufi VARCHAR2(20) := 'JEIV';
BEGIN
dbms_output.put_line(var_laufi);
END;
结果:
JEIV
PL/SQL procedure successfully completed.
我在这里遗漏了一些东西,但我无法弄清楚它是什么。
解决方案
如果您滥用动态 SQL,它是邪恶的。经验法则:如果你没有检查你正在执行的内容,就永远不要执行它!如何?显示语句。就是这样:
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;
/
结果(格式化):
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)
看到评论了吗?where laufi = JEIV
无效,应该用单引号括起来。
还有,tdate
真的是字符串吗?您将其与sysdate
以指定格式表示的字符串进行比较。DATE
如果是这样,将数据类型值存储为字符串的想法很糟糕。
推荐阅读
- python - Python yFInance api如何获得收盘价而不是调整后的收盘价?
- tensorflow - 如何将分类特征和标签输入 TensorFlow 时间序列预测?
- amazon-web-services - 如何记录通过 Lex 机器人完成的呼出电话?
- amazon-web-services - 如何为大数据扩展 AWS quicksight?
- python - 日期时间自动转换为 Int
- python - 如何从 Counter 库中绘图?
- reactjs - 如何让 TypeScript 知道我正在使用哪些 proptypes?
- angular - Angular MFE - WebPack5 - 模块联合 - 图像路径问题
- xamarin - Xamarin.Form 共享 Essentials 图像和 URL 以同时发送两者
- react-native - 大炮读取未定义的属性(读取地图)