0

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;
3
  • You seem to be using / as the statement terminator; did you configure DBeaver accordingly?
    – mustaccio
    Oct 5, 2021 at 12:27
  • 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... Oct 5, 2021 at 18:09
  • @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". Oct 6, 2021 at 8:07

1 Answer 1

0

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>
2
  • 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. Oct 6, 2021 at 6:49
  • 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
    – Littlefoot
    Oct 6, 2021 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.