oracle 儲存過程錯誤：ORA-00904 標識符無效，動態 SQL

我正在嘗試查找包含名為 的列的所有表，在這些表的該列中arg_column_name查找值arg_column_value，並將表的名稱和找到的行數返回給使用者。

我使用 dbeaver 作為 IDE。我知道 T-SQL 和 pl/pgsql - 但我沒有 Oracle PL/SQL 的先驗知識。

這就是問題很重的原因。

我的程序是：

CREATE OR REPLACE PROCEDURE FIND_TABLE_WITH_COLUMN_VALUE (arg_column_name IN VARCHAR2, arg_column_value IN VARCHAR2) 
IS 
   v_rowcount NUMBER;
   v_sql_statement VARCHAR2(4000);
BEGIN
   FOR L IN (
       SELECT 
           OWNER || '.' || TABLE_NAME AS OWNER_TABLE,
               'BEGIN 
                   SELECT 1 FROM ' || OWNER || '.' || TABLE_NAME || ' 
                   WHERE ' || COLUMN_NAME || '=''' || arg_column_value || ''';
                   :0:=SQL%ROWCOUNT;
               END;
               ' AS SQL_STATEMENT
       FROM ALL_TAB_COLUMNS 
       WHERE 1=1
           AND COLUMN_NAME = arg_column_name
   )
   LOOP 
       v_sql_statement := L.SQL_STATEMENT;
       EXECUTE IMMEDIATE v_sql_statement USING OUT v_rowcount;
       DBMS_OUTPUT.put_line(L.OWNER_TABLE);
       DBMS_OUTPUT.put_line(v_rowcount);
   END LOOP;
END;
/

和錯誤：

SQL Error [6550] [65000]: ORA-06550: line 14, column 22:
PL/SQL: ORA-00904: "ARG_COLUMN_NAME": invalid identifier
ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 3:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 17, column 3:
PL/SQL: Statement ignored
ORA-06550: line 18, column 21:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 18, column 3:
PL/SQL: Statement ignored
ORA-06550: line 19, column 24:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 19, column 3:
PL/SQL: Statement ignored
ORA-06550: line 20, column 24:
PLS-00201: identifier 'V_ROWCOUNT' must be declared
ORA-06550: line 20, column 3:
PL/SQL: Statement ignored

這怎麼可能？

1. “ARG_COLUMN_NAME”：無效標識符- 這不是我指定的函式參數嗎？
2. 必須聲明標識符“V_SQL_STATEMENT” - 它是否尚未在儲存過程的 IS 部分中指定？

---

更新：@Littlefoot 的答案已經奏效。我認為我的錯誤的原因可能是雙重的：

1. dbeaver 存在 oracle 語法問題，或者
2. 我對 oracle 語法有疑問（尤其是語句終止符\ ）

我還下載了 Oracle SQL Developer。它比 dbeaver 更有效地完成 Oracle SQL 開發工作。

這是我根據@Littlefoot 的輸入調整的最終程式碼：

create or replace procedure find_table_with_column_value
 (arg_column_name in varchar2, arg_column_value in varchar2, row_count_limit in number default 100000)
is
   v_rowcount      number;
   v_sql_statement varchar2(4000);
   v_owner_table varchar2(400);
   v_prefix varchar2(10) := '';
   err_msg varchar2(4000);
BEGIN
   FOR l IN (
       SELECT
           c.owner || '.' || c.table_name AS owner_table,
           c.column_name,
           t.num_rows
       FROM
           all_tab_columns c
       INNER JOIN all_tables t ON
           t.table_name = c.table_name
           AND t.owner = c.owner
       WHERE
           1 = 1
           AND upper(c.column_name) = upper(dbms_assert.qualified_sql_name(arg_column_name))
           AND t.NUM_ROWS <= row_count_limit
           AND c.DATA_TYPE = 'VARCHAR2' -- safeguard against NUMERIC columns
          )
   LOOP
       v_sql_statement :=
         'select count(*) from ' || l.owner_table ||
         ' where ' || l.column_name || ' = ' ||
           chr(39) || arg_column_value || chr(39);

       v_owner_table := l.owner_table;
       
       EXECUTE IMMEDIATE v_sql_statement INTO v_rowcount;
       
       IF v_rowcount > 0 
           THEN v_prefix := '> ';
           ELSE v_prefix := '';
       END IF;
       
       dbms_output.put_line(v_prefix || l.owner_table || ': ' || v_rowcount);
   END LOOP;

EXCEPTION
WHEN OTHERS THEN
   err_msg := SQLERRM;
   dbms_output.put_line('Error with ' || v_owner_table);
   dbms_output.put_line('Error message = ' || err_msg);
END;

稍微重寫（以便從游標中獲取行數）：

SQL> create or replace procedure find_table_with_column_value
 2    (arg_column_name in varchar2, arg_column_value in varchar2)
 3  is
 4    v_rowcount      number;
 5    v_sql_statement varchar2(4000);
 6  begin
 7    for l in (select owner || '.' || table_name as owner_table
 8              from all_tab_columns
 9              where column_name = dbms_assert.qualified_sql_name(arg_column_name)
10             )
11    loop
12      v_sql_statement :=
13        'select count(*) from ' || l.owner_table ||
14        ' where ' || dbms_assert.qualified_sql_name(arg_column_name) ||' = ' ||
15          chr(39) || arg_column_value || chr(39);
16      execute immediate v_sql_statement into v_rowcount;
17      dbms_output.put_line(l.owner_table ||': '|| v_rowcount);
18    end loop;
19  end;
20  /

Procedure created.

SQL> set serveroutput on
SQL> exec find_table_with_column_value('ENAME', 'KING');
SCOTT.EMP: 1
SCOTT.BONUS: 0
SCOTT.V_EMP: 1

PL/SQL procedure successfully completed.

SQL>

引用自：https://dba.stackexchange.com/questions/300661