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?
- "ARG_COLUMN_NAME": invalid identifier - is this not the function argument that I specified?
- 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:
- dbeaver has issues with oracle syntax, or
- 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;
/
as the statement terminator; did you configure DBeaver accordingly?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".