时间戳

首页 > 解决方案 > SQL 适用于 for 循环，但不适用于动态 sql，您能帮我找出原因吗？

sql - SQL 适用于 for 循环，但不适用于动态 sql，您能帮我找出原因吗？

问题描述

不是重复的，谢谢

我正在尝试重写一些 sql 以使用动态 SQL。仅使用游标循环的版本返回值。我已尽我所能切换它，但始终出现以下错误：

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;
/

标签： sqloracleplsql

解决方案

该变量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？

推荐阅读