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;

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>