Toad World® Forums

Line Numbers for PL/SQL code

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

For the first question, that line number is what comes back from Oracle and what they’re telling you is that the error is occurring on the 42nd line of code that they see. You’ll see the same error if you were to run it via SQL*Plus. Once you execute the script, go to the Messages tab on the Editor and you’ll see a tab with Errors. Clicking on these errors will take you to where the line actually error’d out.

I’ll have to let someone with ninja level SQL skills answer question #2.

On Apr 17, 2017, at 7:40 PM, tom.wickerath bounce-tomwickerath@toadworld.com wrote:

Line Numbers for PL/SQL code

Thread created by tom.wickerath
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](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37336026927381#42426083757635)

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 two columns in the same table include the search term, 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;

/

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

Morning Tom,

this is why you only get one row listed:

open l_cursor for 'select ’ || l_cols || 'else null end cname from ’ ||

x.table_name || l_where || ‘) and rownum=1’;

The ‘and rownum = 1’ is saying, only give me the first row.

If you do not initialise a declared variable with a value at declaration, or at the start of the code after the begin, then it has the value NULL. You cannot add etc to a NULL. This is why you never got a count.

You don’t need to upper case the table name. You can use the table name returned from all/user_tables as is, when looking for columns, as it is already correct. Using upper() will stop oracle from using an index, if there is one, on table_name, in all/user_tab_columns.

HTH

Cheers,

Norm. [TeamT]

Sent from my Android device with K-9 Mail. Please excuse my brevity.

That script is a little messy imho. Here’s one that may be a little easier to understand (at least it is to me :slight_smile: . 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…

declare

l_column_name varchar2(128);
l_count integer := 0;
l_search_string varchar2(100) := ‘{search_criteria}’;
l_schema varchar2(100) := ‘{Schema_Name}’;

cursor c_get_tables is
select table_name c_table_name
from all_tables
where owner = l_schema;

begin

dbms_output.put_line('Searching for tables in ‘||l_schema||’ with columns named: '||l_search_string);

for cur_rec in c_get_tables loop
l_column_name := null;

begin
select column_name
into l_column_name
from all_tab_columns
where table_name = cur_rec.c_table_name
and data_type in (‘CHAR’, ‘NVARCHAR2’, ‘VARCHAR2’)
and column_name = upper(l_search_string);
exception when no_data_found then
null;
end;

if l_column_name is not null then
l_count := l_count + 1;
dbms_output.put_line(cur_rec.c_table_name||’ - '||l_column_name);
end if;

end loop;

dbms_output.put_line('Total Tables found: '||l_count);

exception when others then
dbms_output.put_line(SQLERRM);
end;

Hi Gregory,

Yes, I understand that this line number is what comes back from Oracle, and would be seen in SQL Plus. It is just a bit of a PITA to have to add the number of comment lines to the indicated line number in the error message, to try to find the correct location. This particular procedure is fairly short, but we have spaghetti code [a Steven Feuerstein term, from his video titled “Write Tiny Chunks of (Reusable) Code” https://youtu.be/bCVFn2tvDqE], that goes on for 20 printed pages. It is not so easy to find the actual lines that attracted an error, in those cases.

Once you execute the script, go to the Messages tab on the Editor and you’ll see a tab with Errors. Clicking on these errors will take you to where the line actually error’d out.

I think I found where you meant, but it does not take me anywhere by double-clicking or right-clicking. What am I doing wrong?

It looks like this:

Messages tab
Errors

[Error] ORA-00942: table or view does not exist ORA-06512: at line 42 (36: 0): >> declare i_count number:=0; …

I cannot seem to figure out how to attach an image to this reply (it took me a while to figure out how to attach the first image last night).

Thank You,
Tom

Hi Dennis,

Thank you for your contribution, but I cannot get it to work?

I searched my test sandbox schema (TGW7078) for the string ‘C93A’. Using the modified procedure I posted, I find 22 tables:

22 tables found.

PL/SQL procedure successfully completed.
Time End: 4/18/2017 12:31:39 PM
Elapsed Time for Script Execution: 19 secs

However, after substituting the schema and search term into your procedure, e.g.:

declare
l_column_name varchar2(128);
l_count integer := 0;
l_search_string varchar2(100) := ‘C93A’;
l_schema varchar2(100) := ‘TGW7078’;
etc.

I get this return result:

Time Start: 4/18/2017 12:35:30 PM
Searching for tables in TGW7078 with columns named: C93A
Total Tables found: 0
PL/SQL procedure successfully completed.
Time End: 4/18/2017 12:35:31 PM
Elapsed Time for Script Execution: 807 msecs

I am not skilled enough at PL/SQL, at this point, to do effective troubleshooting. Any ideas?

Thanks,

Tom

Oh, I just noticed something from your return result: “Searching for tables in TGW7078 with columns named: C93A”.

I am looking for data in columns, not column names. I just tried it with a known column name (ap_entry_id), but it still returns no results:

Time Start: 4/18/2017 12:39:04 PM
Searching for tables in TGW7078 with columns named: ap_entry_id
Total Tables found: 0
PL/SQL procedure successfully completed.
Time End: 4/18/2017 12:39:05 PM
Elapsed Time for Script Execution: 1 sec

Just clicking on the line will (should) take you to that line in the editor.

On Apr 18, 2017, at 3:37 PM, tom.wickerath bounce-tomwickerath@toadworld.com wrote:

RE: Line Numbers for PL/SQL code

Reply by tom.wickerath
Hi Dennis,

Thank you for your contribution, but I cannot get it to work?

I searched my test sandbox schema (TGW7078) for the string ‘C93A’. Using the modified procedure I posted, I find 22 tables:

22 tables found.

PL/SQL procedure successfully completed.

Time End: 4/18/2017 12:31:39 PM

Elapsed Time for Script Execution: 19 secs

However, after substituting the schema and search term into your procedure, e.g.:

declare

l_column_name varchar2(128);

l_count integer := 0;

l_search_string varchar2(100) := ‘C93A’;

l_schema varchar2(100) := ‘TGW7078’;

etc.

I get this return result:

Time Start: 4/18/2017 12:35:30 PM

Searching for tables in TGW7078 with columns named: C93A

Total Tables found: 0

PL/SQL procedure successfully completed.

Time End: 4/18/2017 12:35:31 PM

Elapsed Time for Script Execution: 807 msecs

I am not skilled enough at PL/SQL, at this point, to do effective troubleshooting. Any ideas?

Thanks,

Tom

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

Okay, it looks like your search is designed to consider column names, with data types in (‘CHAR’, ‘NVARCHAR2’, ‘VARCHAR2’). I just happened to test it with a numeric column above (ap_entry_id).

I just changed my search to use a varchar2 column, airplanenbr. It kind of works, but it only found three tables before encountering an ORA-01422 error:

Time Start: 4/18/2017 12:44:56 PM
Searching for tables in TGW7078 with columns named: airplanenbr
VC_EQUIPMENTINSTANCESHAPE - AIRPLANENBR
VC_CONNECTORS - AIRPLANENBR
VC_CONNECTORINSTANCESHAPE - AIRPLANENBR
ORA-01422: exact fetch returns more than requested number of rows
PL/SQL procedure successfully completed.
Time End: 4/18/2017 12:44:57 PM
Elapsed Time for Script Execution: 1 sec

In any case, my need is for searching for where data occurs in this database of approx. 280 tables, with each table having many many columns.

Just clicking on the line will (should) take you to that line in the editor.

Is there an option in TOAD that we need to enable, to get this to work?

7534.DBStuff.jpeg

Love the image. It’s a keeper!

What is the trick to attaching an image to a reply in this forum?

From the web page, you can drag an image onto the edit window or for attachments (zips, …), above the edit window, you see Compose Options Preview. Click Options and you can attach files.

On Apr 18, 2017, at 4:02 PM, tom.wickerath bounce-tomwickerath@toadworld.com wrote:

RE: Line Numbers for PL/SQL code

Reply by tom.wickerath
What is the trick to attaching an image to a reply in this forum?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

Hi Norm,

The ‘and rownum = 1’ is saying, only give me the first row.

But I am looking for additional columns in the table, not rows. I just tried commenting out the “and rownum=1’;” part, to see if the code would tell me more than one column in the same table that has the same data. It does not. It still only found the first column.

open l_cursor for 'select ’ || l_cols || 'else null end cname from ’ ||

x.table_name || l_where || ‘)’; – and rownum=1’;

– x.table_name || l_where || ‘) and rownum=1’;

fetch l_cursor into l_cname;

Duh! Silly me. I must learn to read! Apologies.

I think the open cursor, fetch, if found, close cursor is why.

I’ll explain.

You open the cursor and fetch a row. If a tow was fetched, you print out the details, however, you then close the cursor. So you only print the first results. What you need to do is loop around fetching rows from the cursor until %not found, at which point you would exit the loop and then close the cursor. Something like…

Open l_cursor …

Loop

Fetch l_cursor into …

Exit when l_cursor%notfound;

Dbms_output the details here.

End loop;

Close l_cursor;

There’s an example at docs.oracle.com/…/sqloperations.htm that might help.

Hi Gregory,

From the web page, you can drag an image onto the edit window or for attachments (zips, …), above the edit window, you see Compose Options Preview. Click Options and you can attach files.

Okay, it looks like that works. Here is the image I tried to include earlier. When I double-click on the selected line shown above, or right-click it, nothing happens. Is there an option that needs to be enabled to make this work? I am using TOAD for Oracle, version 12.10.0.30.

Thank You,

Tom

Hi Norm,

I forgot to reply to this part of your answer earlier:

You don’t need to upper case the table name. You can use the table name returned from all/user_tables as is, when looking for columns, as it is already correct.

Really? If I run this query I get 102 rows returned:

select * from user_tab_columns where table_name = ‘AWSM_TLK_AIRPLANE’;

However, if I run this query, I get zero rows returned:

select * from user_tab_columns where table_name = ‘awsm_tlk_airplane’;

Using upper() will stop oracle from using an index, if there is one, on table_name, in all/user_tab_columns.

user_tab_columns is a view that uses a different view, user_tab_cols, as its source of data. However, upon using describe user_tab_cols, I see a fairly complicated query that includes several dependencies:

– Dependencies:
– TAB$ (Table)
– OBJ$ (Table)
– “_BASE_USER” (View)
– COLTYPE$ (Table)
– ATTRCOL$ (Table)
– HIST_HEAD$ (Table)
– COL$ (Table)
– “_CURRENT_EDITION_OBJ” (View)

The only reference I see to “TABLE_NAME” in this view is as a comment.

So, I do not know yet whether or not this system view has an index on the TABLE_NAME column.

Tom

Hi Norm,

I tried adding the Loop / End loop and Exit when l_cursor%notfound; statements, but I’m still getting the same results–the code does not find a second column in the same table, with the same search term:

open l_cursor for 'select ’ || l_cols || 'else null end cname from ’ ||
x.table_name || l_where || ‘) and rownum=1’;
Loop
fetch l_cursor into l_cname;
Exit when l_cursor%notfound;
if ( l_cursor%found ) then
i_count:=i_count + 1;

       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;

End loop;
close l_cursor;
end loop;

I also tried moving the Loop statement to happen before the open l_cursor statement.

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

The procedure runs for several minutes, then craps out with an overflow:

Error at line 36
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 52

I’d really like to get this procedure modified, so that it does not stop searching for additional columns in a table, once a match has been found.

Thank You,

Tom

It should just be a single click. No double or right involved.

On Apr 18, 2017, at 5:44 PM, tom.wickerath bounce-tomwickerath@toadworld.com wrote:

RE: Line Numbers for PL/SQL code

Reply by tom.wickerath

Hi Gregory,

From the web page, you can drag an image onto the edit window or for attachments (zips, …), above the edit window, you see Compose Options Preview. Click Options and you can attach files.

Okay, it looks like that works. Here is the image I tried to include earlier. When I double-click on the selected line shown above, or right-click it, nothing happens. Is there an option that needs to be enabled to make this work? I am using TOAD for Oracle, version 12.10.0.30.

Thank You,

Tom

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hi Gregory,

It should just be a single click. No double or right involved.

The only thing I see with a single click is a tool-tip like window, which shows the error message along with the first several lines of code. It definitely is not jumping to the offending line of code for me. This is why I ask if there is an option I need to enable.

I can arrange a WebEx meeting with you, so that you can see my screen, if you think that would be helpful. This is a nagging problem for me. It does not make it easy to use TOAD…

Thank You,

Tom