不是重复的，谢谢
我正在尝试重写一些sql以使用动态SQL。仅使用 loop 游标的版本返回值。我已经尽我所能切换它，但总是收到以下错误：
ORA-00904: "TOTAL_ROW_COUNT": invalid identifier
这按预期工作，返回dbms_output：
SET SERVEROUTPUT ON;
SET TIMING ON;
DECLARE
--variables
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);
TOTAL_ROW_COUNT NUMBER;
TOT_OBJECT_SIZE_MB NUMBER;
FULL_TABLE_COUNT NUMBER;
EST_ONE_ROW_MB NUMBER;
C_TOTAL_ROW_COUNT NUMBER;
SPACE_REQUIRED NUMBER;
v_total_driver_only NUMBER := 0;
v_total_add_joins NUMBER := 0;
v_unjoined_all NUMBER := 0;
v_complete_space_req NUMBER := 0;
--NEW VARIABLES FOR DYN-SQL:
v_sql VARCHAR2 (1100);
cur SYS_REFCURSOR;
owner VARCHAR2 (100);
table_name VARCHAR2 (100);
BEGIN
SELECT tablename
INTO v_csv_name
FROM table_tracker
WHERE CREATED_AT = (SELECT MAX (CREATED_AT) FROM table_tracker);
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 BOB1.MV_PDU_TABLE;
v_sql :=
'SELECT /*+ monitor parallel (4)*/ a.owner,
a.table_name,
b.driver_table,
b.mandatory_join,
sum(c.sum_bytes) TOT_OBJECT_SIZE_MB,
trunc(TOT_OBJECT_SIZE_MB / FULL_TABLE_COUNT,7) EST_ONE_ROW_MB,
(EST_ONE_ROW_MB * TOTAL_ROW_COUNT) SPACE_REQUIRED
FROM dba_tables@DB1 a, MV_PDU_TABLE b, MV_PRD_SEG_DATA c
WHERE a.table_name IN ( SELECT table_name
FROM MV_PDU_TABLE
WHERE driver_table IS NOT NULL
AND additional_joins IS NULL
)
AND a.owner IN ( SELECT DISTINCT productionschema FROM MV_PDU_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';
--main loop
OPEN cur FOR v_sql;
LOOP
FETCH cur INTO OWNER, TABLE_NAME;
EXIT WHEN cur%NOTFOUND;
EXECUTE IMMEDIATE
' select /*+parallel (10)*/ count(*) from '
|| owner
|| '.'
|| table_name
|| '@'
|| dblink
INTO TOTAL_ROW_COUNT;
EXECUTE IMMEDIATE
' select /*+monitor parallel (10)*/ count(*) from '
|| owner
|| '.'
|| table_name
|| '@'
|| dblink
INTO C_TOTAL_ROW_COUNT;
END LOOP;
DBMS_OUTPUT.put_line (TOTAL_ROW_COUNT);
DBMS_OUTPUT.put_line (C_TOTAL_ROW_COUNT);
END;
/
这是我收到以下错误的时候：
ERROR at line 1:
ORA-00904: "TOTAL_ROW_COUNT": invalid identifier
ORA-06512: at line 63
它似乎以不同的方式处理某些列别名作为 for 循环，也许这就是问题所在，但我无法完全确定出了什么问题或如何正确重写它
--
在进行其他查询后，代码现已更改，我正在逐段删除其中的一些内容，最终得到：
ERROR at line 1:
ORA-00932：数据类型不一致：预期 - 得到 -
这是我根据用户"APC"的要求发布的当前代码
declare
--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);
--ROW_COUNT NUMBER;
TOTAL_ROW_COUNT varchar2(100);
TOT_OBJECT_SIZE_MB NUMBER;
--FULL_TABLE_COUNT NUMBER;
--EST_ONE_ROW_MB NUMBER;
--C_TOTAL_ROW_COUNT NUMBER;
--SPACE_REQUIRED NUMBER;
--v_total_driver_only NUMBER := 0;
--v_total_add_joins NUMBER := 0;
--v_all_tabs NUMBER := 0;
--v_unjoined_all NUMBER := 0;
--v_complete_space_req NUMBER := 0;
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);
--
--
begin
SELECT tablename into v_csv_name
FROM table_tracker
WHERE
CREATED_AT = (select MAX(CREATED_AT) from table_tracker);
SELECT mv_name into v_Mv_name
FROM table_tracker_mv
WHERE
CREATED_AT = (select MAX(CREATED_AT) from 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 pdu01.MV_PDU_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
--trunc(TOT_OBJECT_SIZE_MB / FULL_TABLE_COUNT,7) EST_ONE_ROW_MB
--trunc(TOT_OBJECT_SIZE_MB) EST_ONE_ROW_MB
--(EST_ONE_ROW_MB * :TOTAL_ROW_COUNT)
FROM dba_tables@DB1 a, MV_PDU_TABLE b, MV_PRD_SEG_DATA c
WHERE a.table_name IN ( SELECT table_name
FROM MV_PDU_TABLE
WHERE driver_table IS NOT NULL
AND additional_joins IS NULL
)
AND a.owner IN ( SELECT DISTINCT productionschema FROM MV_PDU_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 PDU document:');
--UTL_FILE.PUT_LINE(file_handle, ' ');
--utl_file.put_line(file_handle, 'OWNER,TABLE_NAME,TOT_OBJECT_SIZE_MB,TOTAL_ROW_COUNT,EST_ONE_ROW_MB');
--main loop
open cur for v_sql1;
loop
fetch cur into OWNER;--,TABLE_NAME;--,TOT_OBJECT_SIZE_MB;--,EST_ONE_ROW_MB;
--exit when cur%NOTFOUND;
-- execute immediate' select /*+parallel (4)*/ count(*) from '||owner||'.'||table_name || '@' || l_dblink into TOTAL_ROW_COUNT;
--utl_file.put_line(file_handle,
-- OWNER || ',' ||
-- TABLE_NAME || ',' ||
-- TOT_OBJECT_SIZE_MB || ',' ||
-- TOTAL_ROW_COUNT || ',' ||
-- round(TOT_OBJECT_SIZE_MB / TOTAL_ROW_COUNT,5)
-- );
--v_unjoined_all := v_unjoined_all + TOT_OBJECT_SIZE_MB;
end loop;
--UTL_FILE.PUT_LINE(file_handle, ' ');
--utl_file.put_line(file_handle,
-- 'Total estimated space required for your unjoined tables '|| round(v_unjoined_all,0) ||' MB'
--);
close cur;
--
--
--utl_file.fclose(file_handle);
end;
/
变量TOTAL_ROW_COUNT仅存在于 PL/SQL 上下文中，除非传入，否则 SQL 语句无法看到它。
要使代码正常工作，请更改：
(EST_ONE_ROW_MB * TOTAL_ROW_COUNT) SPACE_REQUIRED
自：
(EST_ONE_ROW_MB * :TOTAL_ROW_COUNT) SPACE_REQUIRED
然后更改：
open cur for v_sql;
自：
open cur for v_sql using TOTAL_ROW_COUNT;
但我有一种感觉，只会修复语法错误并创建一个新的错误。 TOTAL_ROW_COUNT在填充之前就已使用。 也许您的意思是改用列NUM_ROWS？