ORA-00904: "OBJECT_TYPE": invalid identifier

Toad for Oracle & DevOps Toolkit
1

In Data Grid after I click on 'Export Dataset' I get the following error: ORA-00904: "OBJECT_TYPE": invalid identifier. I'm running the query from an Oracle db. retrieving data from MSSQL db./table. It appears to be related the fact the data is retrieved via db. link(MS SQL). Doing the same thing locally, i.e. retrieving/exporting dataset from local db/table works OK. Doing the same thing via db. link to an external Oracle db. works OK as well. I simplified the query to just retrieve a count(*) from the MQSQL db/table, but still get the same error! Tried a couple of tables and the outcome is consistent. Any feedback, suggestion is appreciated. Toad version 17.0 patch 1. Thanks.

2

I have heard of this problem but haven't been able to reproduce it. I do have an idea of what causes it.

Try this:

  1. Shut down Toad
  2. Browse to C:\Users\YOUR_WINDOWS_USER_NAME\AppData\Roaming\Quest Software\Toad for Oracle\17.1\User Files\
  3. Rename the file Variables.dat to Variables.backup
  4. Start Toad and try again

If the problem is gone, please shut down Toad again, zip up your User Files folder, and send it to me at John.Dorlon@quest.com so I can try to reproduce it. If you could also send me a script to create a table that you can reproduce the problem with, that would help. I don't need table data, just the CREATE TABLE statement.

If you don't want to send your entire User Files folder, please at least send the Variables.backup file and table script.

I'll be out of office today and tomorrow but will look at this Monday.

Thanks

3

Hi John Dorlon,

I’ve started using Release 16.1.53.1594 and now am receiving the same error message.

My SQL Script is also using a dblink to a secondary database.

I can’t use your recommendation to rename variables.dat because that file doesn’t exist on this Release. (I did find files variables.sql and variables.rst.)

Please advise if you have any other suggestions.

Thanks,

Warren

4

try deleting/renaming variables.sql and variables.rst.

5

John - I renamed both “variables” objects and restarted Toad. I still received the error on my file download.

Then I checked the two “variables” objects and expected them to be recreated. But these two objects were not recreated.
Is that what you expected?

And what do you recommend as my next step to resolve this issue?

Thanks

Warren

6

John - after posting my previous message I uninstalled and reinstalled the application. This time I did not changes any of the default settings (just in case). Unfortunately, the file Export failed again with this same error.
Please advise on next step.

Regards, Warren

7

Hi Warren,

I am unable to reproduce this, and I don't see any SQL that we are running that has OBJECT_TYPE in it when the export dataset window opens.

Can you turn on spool sql, reproduce the problem, then send me spool sql output which shows the ORA- error?

To turn on spool SQL, go to the main menu, click Database -> Spool SQL -> Spool to screen. Also let me know which Oracle version you are using.

Thanks

8

FYI - Oracle 19c (19.22 to be exact)

9

Please turn on spool SQL to capture the SQL that is causing the error and post it here.

Main menu -> Database -> Spool SQL -> Spool to screen

Thanks

10

Hi John,

The error does not occur when I run the script. The error occurs when I try to Export the Output.

So, the script I previously sent would still be the same.

Do you still need me to Spool it again to the screen and email it to you?

Please advise.

Thanks,
Warren

11

Hi Warren,

The ORA-00904 must be coming from some internal SQL that Toad is executing (but I don't know which one). Spool SQL will capture that SQL. Once I see the SQL that's causing the error, I can probably find the code in Toad that's causing the problem and fix it.

  1. turn on spool sql
  2. Run your SQL in the editor
  3. Try to export it, so the error occurs
  4. Send me the spool sql output

So, the script I previously sent would still be the same.

Oh, did you send it? If so, I didn't get it. Not sure why. Step #3 will actually produce the output that I am most interested in, but step # 2 will show me what's in your script, so maybe I'll be able to reproduce it with that.

Thanks

-John

12

John,

My email was rejected by your application so I'm resending it without including the Spool Output in the email.

Please advise if you don't see the attachment again

(attachments)

Toad Export Error - 20250915.txt (29.8 KB)

13

John,

I hope you received my email that included screen prints and an attached TXT file with the Spool Output.

My attempt to send the Spool Output within the email caused it to be rejected. So, I tried it again but still sending this to alert you in case you still don't see the attachment.

If you received everything no need to respond to this email.

Thanks,
Warren

GENERAL

14

Thanks Warren.

Ok. I see these queries in the output. The errors indicate that your database link connects to SQL Server (the original poster mentioned that, but since you didn't, I assumed yours was Oracle). Anyway - I'll see if there is something that I can do to work around this, but Toad for Oracle does not support any database vendor except Oracle.

SELECT version
FROM SYS.PRODUCT_COMPONENT_VERSION@KRONOSCLOUD
WHERE UPPER(PRODUCT) LIKE '%ORACLE%';
-- ORA-00942: table or view does not exist
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'SYS.PRODUCT_COMPONENT_VERSION'. {42S02,NativeErr = 208}[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180}
ORA-02063: preceding 2 lines from KRONOSCLOUD
-- Elapsed Time: 0.361 seconds
-- Rows fetched: 0

Select owner, object_name, object_type
from sys.all_objects@KRONOSCLOUD
where object_name = 'VP_ALLPERSONV42'
and object_type in ('TABLE', 'VIEW', 'MATERIALIZED VIEW')
order by 3;
-- ORA-00904: "OBJECT_TYPE": invalid identifier
-- Elapsed Time: 0.255 seconds
-- Rows fetched: 0

As a workaround, you can do this:

Create table BLAH
as your select statement goes here;

Select * from BLAH;

Now you can export the results from BLAH.

and when you are done: drop table BLAH;

15

Hi John,

I've been running this script for many years, all using Toad.

Recently I obtained a new laptop, and a later version of Toad was installed. That is when this problem surfaced. I still have my old laptop, and the script still runs fine on it. So, something has changed within Toad that's causing this problem.

I'm still hopeful that you can find a cure. But if not then I'll explore your workaround.

Please keep me posted so I know how to proceed.

Thanks!

Warren

16

More info: Toad is running those SQLs that I pasted above because of the "Auto detect schema and table" option, which is used in the Insert, Merge, SQL Loader, MS Access, and, if you choose the option to name the sheet after the table, Excel.

So for those cases, Toad runs those SQLs to determine a table name to use in the result. But as of now, Toad is doing this regardless of the export format. So if you are exporting to, say "Delimited", Toad is running these queries when it doesn't need to.

I'll make a change so that Toad only does that if one of the above-mentioned formats is used, and to fail more gracefully if one of the other formats is used.

17

Please keep me posted so I know how to proceed.

I don't think there is anything you can do in 16.1 to prevent Toad from running those queries, but you can always install your old version of Toad on your new laptop, without removing 16.1. Different versions of Toad can work just fine together. They keep all of their settings separately, so they can be run at the same time.

Just curious - which version did you have before? I think the code in question has been around for a long time.

Another possible workaround - if you don't want to create a local table. You could create a local view. "Create or replace view BLAH as select......". Then "select * from BLAH" and export that.

18

Fixed for next beta.

19

Hi John,

My previous laptop has Toad for Oracle Release 13.3.0.181.

I've set up the work-around that you provided.

Please advise if you have any other questions or information to share.

Thanks,
Warren