Hello -
Using TOAD for Oracle, version 12.10.0.30. When I encounter a PL/SQL error, which sadly is very often as I'm a newbie [:)] when it comes to PL/SQL, the line number in the Oracle error message is not correct if my code contains comments and/or blank lines. Is there any way to get the true and correct line number to display? For example, the attached image shows a slightly modified version of code provided by Tom Kyte for searching all columns in all tables. For anyone interested, I am running a modified version of code available at this URL:
asktom.oracle.com/.../f
I am only allowed to run this code in anonymous mode, as the DBA will not create a saved procedure. I have added extensive commenting that is shown on the first (32) lines. As you can see, the error number indicates a line (line 42) that could not possibly have attracted the error in question, since this is still part of the Declare section:
Second Question
This one is specific to this search procedure. I have copied the procedure below, sans a few comments internal to the company I work for, that I cannot share. My question relates to the third comment. I have found that if two columns in the same table include the search term (same data), only the first column found will be displayed in the output. I know it is not good database design to have data repeated in two columns, but it is what it is....I have no control of the existing design.
Thank You,
Tom
/* Adaptation of Tom Kyte procedure to search all tables in an Oracle database for a particular
data value. This adaptation allows the procedure to run in anonymous mode, which means
that you do not need to create the procedure first.
Reference:
asktom.oracle.com/.../f
Note:
Had to use the ALL_TAB_COLUMNS table, instead of the USER_TAB_COLUMNS table.
Included a criteria for OWNER that is not found in the original procedure.
CAUTIONS:
1.) MUST use the "Execute as script" button. MUST select the correct schema, in TOAD's
"Current Schema" drop-down, to run as that user. If changing the schema, note that there are
two places where the schema is specified in the code.
2.) 4 line comment removed
3.) This procedure seems to stop searching a table after it finds a hit. If the searched string is
included in a different column in the same table, it appears as if the procedure does not find
the data.
*/
set serveroutput on size unlimited
declare
i_count number:=0;
l_cols long;
l_where long;
l_cursor sys_refcursor;
l_cname varchar2(4000);
p_string varchar2(4000):='C93A';
begin
dbms_application_info.set_client_info( '%' || p_string || '%' );
dbms_output.put_line('Data Searched: ' || p_string);
dbms_output.put_line(' ');
for x in ( select table_name from all_tables where owner = 'APDBMS_SOR'
order by table_name ) -- <-- Added by Tom, so that results will print alphabetically, by table_name
-- for x in ( select table_name from user_tables )
loop
l_cols := 'case when 1=0 then ''x'' ';
l_where := ' where ( 1=0 ';
for y in
(
select '"' || column_name || '"' column_name
from all_tab_columns
-- from user_tab_columns
where table_name = upper(x.table_name)
and owner = 'APDBMS_SOR'
and (data_type in ( 'CHAR', 'NVARCHAR2', 'VARCHAR2' ))
--and (data_type in ( 'CHAR', 'DATE', 'FLOAT', 'NCHAR', 'NUMBER', 'NVARCHAR2', 'VARCHAR2' )
--or data_type like 'INTERVAL%' or data_type like 'TIMESTAMP%' )
-- order by column_name -- <-- Added by Tom, so that results will print alphabetically, by table_name, but it turns out the procedure finds only the first column in a table.
)
loop
l_cols := l_cols || ' when ' || y.column_name ||
' like sys_context(''userenv'',''client_info'') then ' ||
' ''' || y.column_name || '''';
l_where := l_where || ' or ' || y.column_name || ' like sys_context(''userenv'',''client_info'') ';
end loop;
open l_cursor for 'select ' || l_cols || 'else null end cname from ' ||
x.table_name || l_where || ') and rownum=1';
fetch l_cursor into l_cname;
if ( l_cursor%found ) then
i_count:=i_count + 1; -- Why can't I increment a counter variable without attracting error: PLS-00306: wrong number or types of arguments in call to '+'
-- Answer: i_count was declared as long. When I changed it to number, the code runs, however, it appears to be null below
dbms_output.put_line(x.table_name || chr(9) || chr(9) || l_cname );
--dbms_output.put_line('-- ' || x.table_name || chr(9) || chr(9) || l_cname );
dbms_output.put_line('select * from ' || x.table_name || ' where ' || l_cname || ' like ''%' || p_string || '%'';');
dbms_output.put_line(' ');
end if;
close l_cursor;
end loop;
dbms_output.put_line(' '); -- Added to provide a single line in-between our results and the summary that starts with " PL/SQL procedure successfully completed."
dbms_output.put_line(i_count || ' tables found.'); -- <-- Result is " tables found." without any number indicated??
-- Solution: Initialized variable to zero in declaration, e.g. Changed: "i_count number;" to: "i_count number:=0;"
dbms_output.put_line(' ');
end;
/
. Feel free to change it, or whatever to suit your needs/wants. Just add the search criteria and the schema you want to search. Hope it helps…
