oracle - ORA-00904 动态 SQL 块中的标识符无效错误
问题描述
我正在运行下面的代码,一旦执行,就会报告一个错误,告诉我 EST_ONE_ROW_MB 是一个无效的标识符。
我被告知我可能需要让动态 SQL 部分作为独立查询运行,以作为初始故障排除练习开始,但我对于如何在这里编写一个子查询来产生所需的输出并消除错误。
create or replace procedure JUST_ME is
--variables
l_dblink varchar2(100) := 'DB1';
file_handle UTL_FILE.file_type;
v_ts_name varchar2(30);
v_link_name varchar2(10);
v_csv_name varchar2(100);
EST_ONE_ROW_MB varchar2(100) ;
TOTAL_ROW_COUNT NUMBER;
SPACE_REQUIRED NUMBER;
TOT_OBJECT_SIZE_MB NUMBER;
v_Mv_name varchar2(100);
v_sql1 varchar2(1500);
cur SYS_REFCURSOR;
owner varchar2(100);
table_name varchar2(100);
driver_table varchar2(100);
mandatory_join varchar2(100);
C_TOTAL_ROW_COUNT varchar2(100);
v_total_driver_only varchar2(100);
--
begin
SELECT tablename into v_csv_name
FROM BOB01.BOB_new_table_tracker
WHERE
CREATED_AT = (select MAX(CREATED_AT) from BOB01.BOB_new_table_tracker);
SELECT mv_name into v_Mv_name
FROM BOB01.BOB_new_table_tracker_mv
WHERE
CREATED_AT = (select MAX(CREATED_AT) from BOB01.BOB_new_table_tracker_mv);
select link_name into v_link_name from link_and_mail where mdate = (select max(mdate) from link_and_mail);
select distinct targetschema into v_ts_name from BOB01.MV_BOB_TABLE;
v_sql1 := 'SELECT /*+ monitor parallel (4)*/ a.owner,
a.table_name,
b.driver_table,
b.mandatory_join,
sum(c.sum_bytes) TOT_OBJECT_SIZE_MB,
(TOT_OBJECT_SIZE_MB) / (:C_TOTAL_ROW_COUNT) AS "EST_ONE_ROW_MB",
(EST_ONE_ROW_MB) * (:TOTAL_ROW_COUNT) AS "SPACE_REQUIRED"
FROM dba_tables@DB1 a, '|| v_Mv_name ||' b, MV_PRD_SEG_DATA c
WHERE a.table_name IN ( SELECT table_name
FROM MV_BOB_TABLE
WHERE driver_table IS NOT NULL
AND additional_joins IS NULL
)
AND a.owner IN ( SELECT DISTINCT productionschema FROM MV_BOB_TABLE c )
and a.table_name = b.table_name
and a.table_name = c.segment_name
group by a.owner,a.table_name,b.driver_table,b.mandatory_join
ORDER BY table_name';
file_handle := utl_file.fopen('ESTIMATES_CSV', v_csv_name||'_EST_PROC.csv', 'w', 32767);
--
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, 'The below report shows total row counts in PROD');
UTL_FILE.PUT_LINE(file_handle, ' for unjoined tables in the BOB document:');
UTL_FILE.PUT_LINE(file_handle, ' ');
utl_file.put_line(file_handle, 'OWNER,TABLE_NAME,MANDATORY_JOIN,TOT_OBJECT_SIZE_MB,EST_ONE_ROW_MB,TOTAL_ROW_COUNT,SPACE_REQUIRED');
--main loop
open cur for v_sql1 using TOTAL_ROW_COUNT,C_TOTAL_ROW_COUNT;
loop
fetch cur into OWNER,TABLE_NAME,MANDATORY_JOIN,TOT_OBJECT_SIZE_MB,EST_ONE_ROW_MB,TOTAL_ROW_COUNT,SPACE_REQUIRED;--,EST_ONE_ROW_MB;
exit when cur%NOTFOUND;
execute immediate' select /*+parallel (4)*/ count(*) from '||owner||'.'||table_name || '@' || l_dblink into TOTAL_ROW_COUNT;
execute immediate' select /*+monitor parallel (10)*/ count(*) from ' ||owner||'.'||table_name || '@' || l_dblink||' b '||','||
driver_table || '@' || l_dblink||' a ' ||' where ' ||mandatory_join into TOTAL_ROW_COUNT;
execute immediate' select /*+monitor parallel (10)*/ count(*) from ' ||owner||'.'||table_name || '@' || l_dblink into C_TOTAL_ROW_COUNT;
utl_file.put_line(file_handle,
OWNER || ',' ||
TABLE_NAME || ',' ||
TOT_OBJECT_SIZE_MB || ',' ||
TOTAL_ROW_COUNT || ',' ||
C_TOTAL_ROW_COUNT || ',' ||
round(TOT_OBJECT_SIZE_MB / C_TOTAL_ROW_COUNT,7)|| ',' ||
round(round(TOT_OBJECT_SIZE_MB / C_TOTAL_ROW_COUNT,7) * round(TOTAL_ROW_COUNT,0),0)
);
v_total_driver_only := v_total_driver_only + round(TOT_OBJECT_SIZE_MB / C_TOTAL_ROW_COUNT,7) * round(TOTAL_ROW_COUNT,0);
end loop;
UTL_FILE.PUT_LINE(file_handle, ' ');
utl_file.put_line(file_handle,
'Total Estimated Space Required '|| round(v_total_driver_only,0) ||' MB'
);
utl_file.fclose(file_handle);
end JUST_ME;
解决方案
以EST_ONE_ROW_MB
这种方式使用是不可能的,因为它没有被定义为列。将其替换为(TOT_OBJECT_SIZE_MB) / (:C_TOTAL_ROW_COUNT)
SELECT /*+ monitor parallel (4)*/ a.owner,
a.table_name,
b.driver_table,
b.mandatory_join,
sum(c.sum_bytes) TOT_OBJECT_SIZE_MB, -- will be the same problem
(sum(c.sum_bytes)) / (:C_TOTAL_ROW_COUNT) AS "EST_ONE_ROW_MB",
((sum(c.sum_bytes)) / (:C_TOTAL_ROW_COUNT)) * (:TOTAL_ROW_COUNT) AS "SPACE_REQUIRED"
...
如果你有一个内联视图,你可以这样做。例如
select EST_ONE_ROW_MB * (:TOTAL_ROW_COUNT) AS "SPACE_REQUIRED"
from(
select (TOT_OBJECT_SIZE_MB) / (:C_TOTAL_ROW_COUNT) AS "EST_ONE_ROW_MB"
from ....
)
推荐阅读
- javascript - ReactJS - 使用三元运算符进行条件渲染 - InvalidValueError
- amp-html - AMP URL API 未返回有效的 AMP 页面
- android - 在 ABI 的 NDK 工具链文件夹中找不到工具链,前缀为:Android Studio 中的 mipsel-linux-android 错误
- python-3.x - 在 GCP 功能中使用 Flask 路由?
- keras - Keras 功能 API:将 CNN 模型与 RNN 结合起来查看图像序列
- python - 更快地关闭 tkinter 窗口(当有很多按钮时)
- c# - 合并两个列表并替换元素
- php - 更改 PHP 梨邮件中的电子邮件标题
- python - 为什么我的标签没有显示在 tkinter 中?
- python - Setuptools:使用源文件夹中的 setup.py 从“项目”导入