Unjustified ORA-00903: Invalid tablename

Toad for Oracle - Beta
1

All,

I’m on Toad 12.10.0.30.

I’m trying to run a script that starts with
declare
l_current_host all_db_links.host%type;
l_rep_db varchar2(128);
l_sql varchar2(4000);
begin
select host
into l_current_host
from dual
left outer join sys.all_db_links
on owner = ‘UTL_KRG’
and db_link = ‘REP_UTL_KRG’;

To test I commented out the into clause, then blocked the query:
select host
– into l_current_host
from dual
left outer join sys.all_db_links
on owner = ‘UTL_KRG’
and db_link = ‘REP_UTL_KRG’;

and hit F9 - I get an ORA-00903: Invalid table name.
So I put my cursor on all_db_links and hit F4. Ok, so it exists and is accessible.

I then retried the query with Ctrl-Enter, still no luck.

So I copied the query to a new editor tab.
There it executes without problem.

What am I doing wrong?

Kind regards & thanks in advance,
Abe Kornelis

2

Hmmm…interesting - it’s working here. Maybe try checking/unchecking the option > Execute/Compile > ‘Treat blank line as statement terminator’ ?

3

If you’re trying to run this as a script, you should use F5, not F9. F9 is statement execution.

4

That code ran without error here (On 12cR2 Beta)

What version of Oracle are you running it on?

5

All,

thanks for your replies,

@Dennis: I unchecked the statement terminator option. No difference. Since there are no blank lines in the statement, I’m not really surprised.

@Gregory: I commented out the into clause to test the query and validate its results interactively. Hence the attempt to execute the statment using F9 and Ctrl-Enter. F5 would run the whole script which should then result in an error due to the missing INTO clause…

@John: 11.2.0.4.0

Since the statement executes normally when issued in a separate editor tab, I suspect a Toad issue, though I cannot be 100% sure, of course.

Kind regards,
Abe

6

Oh, I missed that part. Looks like Toad is executing some bad SQL in the background. Turn on Spool SQL to see if you can identify it.

7

John,

that results in the following:

– Session: UTL_KRG@WMT300
– Timestamp: 15:52:49.510
select *
from
Error: ORA-00903: Ongeldige tabelnaam.

So it seems in incomplete query is fired at Oracle.
Not surprising Oracle balks :wink:

I hope this helps you find the root cause.
If I need to do anything else to collect symptom data,
just let me know. I’ll be back in the office tomorrow moring.

Kind regards,
Abe

8

Definitely that is a problem but I can’t tell where it’s coming from in our source since there is so little of the query to go on!

so, if you put this in the editor and run it with F9, does it fail consistently? If not, can you tell me how to change it so that it does?

declare
l_current_host all_db_links.host%type;
l_rep_db varchar2(128);
l_sql varchar2(4000);
begin
select host
into l_current_host
from dual
left outer join sys.all_db_links
on owner = ‘UTL_KRG’
and db_link = ‘REP_UTL_KRG’;
end;

9

John,

When executing the complete snippet, there’s no problem.

Luckily, it reproduces consistently. To reproduce:

  1. comment out the into l_current_host statement
  2. block select the entire select statement, including the commented-out line, but excluding the declarations, the begin and the end statements.
  3. Use eigerh F9 or Ctrl-Enter to execute the selected fragment

That should give you the maverick behaviour I’ve been seeing here.

Abe

10

I see it now, thanks Abe.