Hello!
I'm encountering an issue with my Oracle 19c database when using TOAD's Data Import wizard. When at the Run step, hitting the button to proceed raises the error 'ORA-00904: "DEFAULT_ON_NULL": invalid identifier.' I enabled the Spool SQL console and see the Select statement that is raising the exception.
Columns DEFAULT_LENGTH nor DEFAULT_ON_NULL are present in the view TOAD is querying against, ALL_TAB_COLUMNS. Typically I would use a different view, ALL_TAB_COLS, which has both of these missing columns present.
Is this a known issue? Currently running TOAD 17.0 Patch 1, version 17.0.353.2906. Oracle database is 19c SE2.
SQL:
select column_Name
from all_tab_columns
where owner = :own
and table_name = :tn
and default_length is not null
and default_length > 0
and default_on_null = 'NO';
-- ORA-00904: "DEFAULT_ON_NULL": invalid identifier
Amended / Working SQL:
select column_Name, default_length, default_on_null
from all_tab_cols
where owner = 'x' --:ownIm
and table_name = 'DUMMY_TAB' --:tn
and default_length is not null
and default_length > 0
and default_on_null = 'NO';
Image: Error from Data Import Wizard, and amened SQL
Thank you!
-Randy G.
Hi Randy,
Thanks for reporting this. When I connect to 19c, that column exists in all_tab_columns and I do not get an error. Was your database upgraded? If so, maybe this was missed as part of the upgrade.
Here it is in the Oracle documentation.
I could switch to all_tab_cols, but of course that doesn't help you now.
-John
Hi John,
I appreciate the response. We are in the middle of an upgrade project, going from 12 to 19. We're using EXPDP / IMPDP to load the necessary files into an empty 19c database. I'll bring this point up to the DBA who's performing this work, as you may very well be on the right path.
Thank you, and will report back with any findings.
-Randy G.
1 Like
Hi John,
Finally getting back to this. I found out what the issue was. The schema (we'll call it TEST) that I'm connecting to the database with using TOAD has a table called ALL_TAB_COLUMNS. Reason as to why, I'm uncertain, but TOAD is querying TEST.ALL_TAB_COLUMNS under the schema as opposed to using the system-defined view.
Performing the same import function as the SYS user worked like a charm. I'll advise the user that they'll need to connect as a different user with appropriate privileges to access ALL_TAB_COLUMNS while we figure out what the TEST.ALL_TAB_COLUMNS tables purpose is.
I guess the proposed amendment I would have would be to have that TOAD's Data Import wizard explicitly query the system-defined view as opposed to whichever schema that the user is connected to TOAD with.
Thank you!
-Randy G.
Ah.
For what it's worth, we definitely aren't explicitly specifying TEST.ALL_TAB_COLUMNS (or whatever your schema name is)
Normally when we query the data dictionary views, we specify with the SYS schema, like
Select *
from SYS.ALL_TAB_COLUMNS
where....
because if you leave SYS. out, and there is an ALL_TAB_COLUMNS table in the schema that you happen to be connected to, Oracle will return the results from that instead.
I guess we just missed SYS. on that one. I'll add it. Thanks for pointing it out.
Looking at our source, there are actually a lot of places where we reference ALL_TAB_COLUMNS without specifying the SYS schema.
I'd recommend you get rid of that table as soon as you can, or at least rename it to avoid other problems. If you can't do that, then another option is do your best to keep it up to date with Oracle's data dictionary. Because in addition to columns being missing....if the data that it contains is different than the real ALL_TAB_COLUMNS, that's going to cause problems in Toad.
We are getting close to release of 17.1 and I'm afraid this would involve more changes and we are willing to make this close to release. I will fix it for the first version after 17.1.
