Executing a partial script incorrectly results in ORA-00903 error

Toad for Oracle - Beta
1

All,

I’m on Toad 12.9.0.27. I’m building an insert-from-select statement, so I block the select statement without the preceding insert, then hit F9 and get an ORA-00903 error.

However, when I run the complete statement (including the insert) no error appears.

It looks like (somehow) Toad is passing a mangled query text to Oracle.

For the sake of completeness, I’m pasting the query below. You’ll have to modify to tables defined on your local system.

Abe

insert into iib_krg.localpart_log

select /+choose/

localtimestamp as timestamp

, dbms_transaction.local_transaction_id as transaction_id

, ‘U’ as change_type

, ‘U’ as logical_change_type

, ‘7’ as status

, lp.partno as partno /* supply correct value when row not found */

, lp.compno as compno /* supply correct value when row not found */

, lp.class5 /* old */

, lp.class5 /* new */

, lp.killdate /* old */

, lp.killdate /* new */

, ‘Fake update with data retrieved from OMS’ as message

, -1 as sessionid

from ehda.a_localpart lp inner join utl_krg.active_headquarters_vw hq on hq.compno = lp.compno – select only rows for headquarters

where coalesce(lp.killdate, current_date + 1) > current_date

and not exists

(select 1

from iib_krg.localpart_log ld

where ld.partno = lp.partno

and ld.compno = lp.compno);

2

Hi Abe,

have you tried database->Spool SQL->To Screen and then running the

highlighted select part again? The bottom of the screen will show

exactly what Toad sent to Oracle.

–

Cheers,

Norm. [TeamT]

3

Norm,

I’ve rebooted my laptop. Problem no longer reproduces :frowning:

Should the issue recur, I’ll inform you. I’ll try to remember the suggestion above.

Kind regards,
Abe

4

Norm,

It does reproduce. It takes an explain request (I hit F2 with the select-statement blocked to exclude the leading insert into table_name part) to start the problem.

After that, when I try to execute the same block, the error recurs.

The query issued by Toad is the following:

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:24:12.928

select *

from

Error: ORA-00903: invalid table name

Please let me know if you need any additional info.

Kind regards,
Abe

5

Morning Abe,

it looks like the table name is not being passed to Oracle, your code

seems to be “select * from” and nothing else.

I’m not a (Toad) developer, so I can’t help you sort this one out I’m

afraid, but when the developers come online in the USA, I’m sure you’ll

get a response.

Cheers,

Norm. [TeamT]

6

Norm,

I agree that that’s what it look like. Yet I know I selected quite a block of code.

Actually I copied the very same block of code into a separate editor tab to execute it without any problem.

Kind regards,
Abe

7

I’m not able to reproduce. I created a table using this statement…

CREATE
TABLE
emp_clone

AS

SELECT
*

 FROM scott.emp

WHERE

0

1;

…and I setup the following SQL to insert a row.

INSERT
INTO
emp_clone

(SELECT
*

  FROM scott.emp

 WHERE

ROWNUM

1);

I highlighted the inner SELECT to test it and executed with F9. I got the results as expected. Again I highlighted the inner SELECT, but this time I did an Explain Plan (CTRL+E) and then executed. I got the expected results.

Ø It takes an explain request (I hit F2 with the select-statement blocked to exclude the leading insert into table_name part) to start the problem.

Have you remapped F2 to Explain Plan? By default F2 toggles pinned/unpinned docked panels.

The failing statement in the spooled SQL doesn’t even match up to your example SQL. When you received the error this most recent time were you testing with a different statement? The spooled SQL shows “select *…” instead selecting a list of columns. If the failing statement doesn’t align with what you’re trying to execute it might be an internal query.

Michael

From: kornelis.abe [mailto:bounce-kornelisabe@toadworld.com]

Sent: Friday, February 19, 2016 2:29 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Executing a partial script incorrectly results in ORA-00903 error

RE: Executing a partial script incorrectly results in ORA-00903 error

Reply by kornelis.abe

Norm,

It does reproduce. It takes an explain request (I hit F2 with the select-statement blocked to exclude the leading insert into table_name part) to start the problem.

After that, when I try to execute the same block, the error recurs.

The query issued by Toad is the following:

Session:
IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:24:12.928

select *

from

Error: ORA-00903: invalid table name

Please let me know if you need any additional info.

Kind regards,

Abe

===

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

8

Michael,

no, I did not remap F2. I just use Ctrl-E. The F2-statement was a crooked bit in my brain :frowning:

The failing statement indeed does not even closely resemble the SQL I’m trying to exectute.

I’ve searched F8 for the exact text of the statement I was working on at that time (current version does no longer have the problem)
but failed to locate it. Probably I’ve been running parallel sessions of Toad and saved them in an order that dropped the statement from the statement recall list.

I’ll seek to better save exact status info next time around.

Thanks for your patience and support.
Abe

9

Ok,

I’ve got another instance of this error. It reproduces, at least on my OMST01 system.

Here’s the scenario:

  1. I start a fresh instance of Toad (the only one in this case) and connect to my schema (IIB_KRG)

  2. I drag my script (tbl_localpart_log.sql) from the project manager into the edit window.

  3. I position my cursor on the insert-from-select statement and hit Ctrl-E.

  4. I get the error. With sql statements spooled to screen, I can verify that indeed a "Seelct * from " is issued, so indeed an incomplete SQL statement.

The statement I’m trying top explain can be copied to another window, with or without the leading insert into clause, then it explains fine.

Here’s the complete statement:

insert into iib_krg.localpart_log

select /+choose/

localtimestamp as timestamp

, dbms_transaction.local_transaction_id as transaction_id

, ‘U’ as change_type

, ‘U’ as logical_change_type

, ‘7’ as status

, lp.partno as partno /* supply correct value when row not found */

, lp.compno as compno /* supply correct value when row not found */

, lp.class5 /* old */

, lp.class5 /* new */

, lp.killdate /* old */

, lp.killdate /* new */

, ‘Fake update with data retrieved from OMS’ as message

, -1 as sessionid

from ehda.a_localpart lp inner join utl_krg.active_headquarters_vw hq on hq.compno = lp.compno – select only rows for headquarters

where coalesce(lp.killdate, current_date + 1) > current_date

and not exists

(select 1

from iib_krg.localpart_log ld

where ld.partno = lp.partno

and ld.compno = lp.compno);

I hope this helps. If you need any additional info, please let me know.

PS: tried again copying the full script rather than just the statement. Fails again. So I’m appending the complete script - it probably contains something that causes the hiccup.

Abe.

[View:/cfs-file/__key/communityserver-discussions-components-files/86/tbl_5F00_localpart_5F00_log.sql:320:240]