Ora-12801 + ora-01006

Toad for Oracle
1

Since last week, whenever I try to see my columns or data in Toad, I get following error. I have no idea what caused this and if it’s a Toad problem or Oracle DB problem.
Colleagues tried logging in on the same database, same user, and have no problems. I’m using Toad for Oracle 13.1.0.78.

image

Kind regards

2

It must be Toad related I guess. If I reset to a clean set of user files the problem is solved. When I import them again, the error reappears.

3

Can you turn on spool sql so we can see the query that is causing the error?

Possible solution: go to Options -> Schema Browser -> Data tab. Check “Use NOPARALLEL Hint”.

4

Hi,

the spool sql results in 2 errors.

ORA-01006: bind variable does note exist

as the query below does not provide information about :TABNAME

Select cols.column_id, cols.column_name as Name, cols.nullable,
       cols.data_type as Type, cols.data_type_mod,
       decode(cols.data_type, 'CHAR', cols.char_length,
                              'VARCHAR', cols.char_length,
                              'VARCHAR2', cols.char_length,
                              'NCHAR', cols.char_length,
                              'NVARCHAR', cols.char_length,
                              'NVARCHAR2', cols.char_length,
                              null) nchar_length,
       decode(cols.data_type, 'NUMBER', cols.data_precision + cols.data_scale, cols.data_length) length,
       cols.data_precision Precision, cols.data_scale Scale, cols.data_length dlength, cols.data_default  
       ,cols.char_used
       ,cols.hidden_column, cols.internal_column_id
       ,cols.qualified_col_name
       ,InitCap(cols.histogram) histogram
       ,cols.virtual_column
       ,cols.num_distinct, cols.num_nulls, round(cols.density, 5) density
       ,cols.user_generated, cols.default_on_null, cols.identity_column
       ,coms.comments
FROM
      sys.user_col_comments coms, 
      sys.user_tab_cols cols
where 1=1
and   coms.table_name (+) = cols.Table_Name
and   coms.column_name (+) = cols.column_Name
and   coms.origin_con_id (+) = TO_NUMBER(sys_context('USERENV','CON_ID'))
and   cols.table_name = :TABNAME
order by column_id, internal_column_id

ORA-00936: missing expression

SELECT 
   ROWID,  *
FROM <our table>

Whenever I try to change the option "Use NOPARALLEL hint" I get the error below.

image

After resetting all settings and executing the same steps, no ORA errors appear + the "Use NOPARALLEL hint" setting can be changed succesfully.

Kind regards

5

I don’t get a missing expression error with that query.
Can you zip up and send me your user files folder (the one that produces the error, not the clean one). send to john.dorlon@quest.com. Thanks.

6

Hi,
before I wanted to share you the corrupted file, I tried exporting the current, working (and also clean) user folder. When I tried to import the faulty user files, I tried again, and now it works without any problem…
I can’t wrap my head around it as we first thought it was related to the user files, but now they work, so maybe it is a database problem?
I can’t seem to reproduce the error…

Kind regards

7

I don’t think there is anything corrupt in your user files folder, it’s just that I was thinking that there might have been some combination of options in there causing an invalid query. To be honest, I didn’t think the chances of that were so great, as I probably would have heard of this problem with others if there was a bad query somewhere in Schema Browser -> Tables tab (one of the most heavily used areas of Toad).

The ORA-12801 does seem database related. The ORA-01006 - that part could have been Toad.

Anyway, if you can’t reproduce anymore, I guess there is no point sending me your files.

I’m glad it’s working, but if it comes up again, let me know (and maybe try it on several tables to make sure it happens on all of them)

Thanks.

8

Hi,

yes, will definitely do if the problem occurs again.

I can say that I did try it on multiple tables and all resulted in the same error, so not just one table.

Kind regards.

9

By the way, to make that “the following files could not be found” message in options go away, go to the “executables” area and make sure that all of those options point to files that exist. I usually just blank out all of the ones in the top section and then just check the “auto detect” option at the top.

10

Hi John,

2 of the executables path do no longer exist.
I had no problems with them there, but it’s indeed better for those to be blanks.

Thanks !