oracle stored procedure errors: ORA-00904 invalid identifier, dynamic SQL

Question

I am trying to find all tables that contain column called arg_column_name, look up value arg_column_value in that column of those tables, and return names of the tables and counts of found rows to the user.

I use dbeaver as IDE. I know T-SQL and pl/pgsql - but I have no prior knowledge of Oracle PL/SQL.

This is the reason the question is pretty loaded.

The procedure that I have is:

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

And the errors:

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

How is that possible?

1. "ARG_COLUMN_NAME": invalid identifier - is this not the function argument that I specified?
2. identifier 'V_SQL_STATEMENT' must be declared - is it not already specified in the IS part of the stored proc?

---

Update: the answer by @Littlefoot has worked. I think the cause of my error may be twofold:

1. dbeaver has issues with oracle syntax, or
2. I have issues with oracle syntax (especially the statement terminator \ )

I also have downloaded the Oracle SQL Developer. It does the job with Oracle SQL development more effectively than dbeaver.

And here is the final code that I have adjusted based on the inputs by @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;

• mustaccio: You seem to be using / as the statement terminator; did you configure DBeaver accordingly?
• Mark Stewart: Just a generic suggestion; when I'm working with an unfamiliar language, and get such messages, I trim almost everything out except a few simple statements, then add things back in until it breaks...
• Igor Zhilin: @mustaccio You might be right. Although did not find anything about oracle statement terminator in DBeaver settings, I tried to create a simple proc from dbeaver ( CREATE OR REPLACE PROCEDURE test() IS BEGIN DBMS_OUTPUT.put_line('2'); END \ ), it had been created but executing it returned "Package or function TEST is in an invalid state".

Answer 1

Slightly rewritten (so that number of rows is fetched out of the cursor):

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>

• Igor Zhilin: Appreciate it. Still receive issues in my dbeaver IDE, so I assume the IDE is the issue. Need to run this in a command line. What is this command-line tool? As I realize, Oracle is not really visual IDE-friendly, all references I see on the internet use some sort of command-line thing instead of a visual environment.
• Littlefoot: You're welcome. I ran this code in Oracle's SQLPlus, command-line tool. It is *good as it allows me to easily copy/paste code and result in here. If you want, you can download Oracle's SQL Developer GUI. It is a free tool (download requires (free) registration), check it here: oracle.com/tools/downloads/sqldev-downloads.html