Table Script Generation by Toad Gives Invalid SQL Error (ORA-00900)

Toad for Oracle
1

I am using Toad for Oracle Professional version 10.6.1.3. My goal is to view the script generated by Toad for a selected table. Here are my steps:

  1. Log in to my database.
  2. Open the Schema Browser.
  3. Select the proper schema. The tables are listed.
  4. Select the table. The column list is displayed on the right.
  5. Click the “Script” tab. An error is returned: “TOAD Error:: ORA-00900: invalid SQL statement.”
    There is no other information given. Some tables give this error; some tables don’t. Apparently this does not happen for other members of my team with the same login to the same database, so I am guessing there is some sort of setting or option that I have turned on/off that is causing this error. Using the “Spool SQL” feature, I was able to determine that all of the tables that give me this error contain one or more fields with the CLOB data type. When this happens, a series of SQL statements are ran to get various information about the CLOB. One of these isn’t displayed by the “Spool SQL” window, and this is where the invalid SQL error is generated.

Any help would be greatly appreciated.

2

Post the “Spool SQL” output here. Even if the statement producing the error isn’t shown, that’ll show me the statements leading up to it.

3

Here you go… Thank you for your help!

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:26.861

Select queue_table TABLE_NAME

FROM SYS.ALL_QUEUE_TABLES Q

WHERE Q.OWNER = :own

and queue_table = :OneObjectName

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:26.951

Select name

from SYS.ALL_SNAPSHOTS

where owner = :own

:own(VARCHAR[7],IN)=‘MYDBNAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:27.000

Select owner, type_name, typecode FROM SYS.ALL_TYPES where owner is not null

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:28.146

select

TABLE_TYPE_OWNER, TABLE_TYPE, OBJECT_ID_TYPE, TABLE_NAME,

TABLESPACE_NAME, BUFFER_POOL, IOT_NAME, IOT_TYPE, MIN_EXTENTS,

NEXT_EXTENT, MAX_EXTENTS, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,

PCT_INCREASE, FREELISTS, FREELIST_GROUPS, PCT_FREE, PCT_USED,

INSTANCES, CLUSTER_NAME, DEGREE, NUM_ROWS, AVG_ROW_LEN,

TEMPORARY, LOGGING, PARTITIONED, NESTED, ROW_MOVEMENT,

MONITORING, DURATION, DEPENDENCIES, COMPRESSION, COMPRESS_FOR,

‘NO’ READ_ONLY, CACHE

from ALL_OBJECT_TABLES t

where owner =:own

and ((iot_type is null) or (iot_type <> ‘IOT_MAPPING’))

and (( table_name = :OneObjectName ) or

(table_name like ‘SYS_IOT_OVER%’ and iot_name = :OneObjectName ))

union all

select

null, null, null, TABLE_NAME,

TABLESPACE_NAME, BUFFER_POOL, IOT_NAME, IOT_TYPE, MIN_EXTENTS,

NEXT_EXTENT, MAX_EXTENTS, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,

PCT_INCREASE, FREELISTS, FREELIST_GROUPS, PCT_FREE, PCT_USED,

INSTANCES, CLUSTER_NAME, DEGREE, NUM_ROWS, AVG_ROW_LEN,

TEMPORARY, LOGGING, PARTITIONED, NESTED, ROW_MOVEMENT,

MONITORING, DURATION, DEPENDENCIES, COMPRESSION, COMPRESS_FOR,

READ_ONLY, CACHE

from ALL_TABLES t

where owner =:own

and ((iot_type is null) or (iot_type <> ‘IOT_MAPPING’))

and (( table_name = :OneObjectName ) or

(table_name like ‘SYS_IOT_OVER%’ and iot_name = :OneObjectName ))

order by table_name

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:28.893

Select table_name, flashback_archive_name

from sys.dba_flashback_archive_tables

where owner_name =:own

and TABLE_NAME = :OneObjectName

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:28.921

Select table_name, column_name, data_type, data_type_mod, data_type_owner,

decode(data_type, ‘CHAR’, char_length,

‘VARCHAR’, char_length,

‘VARCHAR2’, char_length,

‘NCHAR’, char_length,

‘NVARCHAR’, char_length,

‘NVARCHAR2’, char_length,

data_length) data_length,

data_precision, data_scale, nullable, char_used

, virtual_column

FROM SYS.ALL_TAB_COLS c

WHERE OWNER = :own

AND HIDDEN_COLUMN = ‘NO’

and TABLE_NAME = :OneObjectName

order by table_name, column_id

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:29.027

Select TABLE_NAME, COLUMN_NAME, DEFAULT_LENGTH, DATA_DEFAULT

FROM SYS.ALL_TAB_COLUMNS C WHERE OWNER = :own

And default_length is not null

:own(VARCHAR[7],IN)=‘MYDBNAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:29.501

SELECT table_name, column_name,

DECODE(encryption_alg, ‘3 Key Triple DES 168 bits key’, ‘3DES168’,

‘AES 128 bits key’, ‘AES128’,

‘AES 192 bits key’, ‘AES192’,

‘AES 256 bits key’, ‘AES256’,

‘Internal Error’) encryption_alg,

salt

from sys.ALL_ENCRYPTED_COLUMNS

where owner =:own

and TABLE_NAME = :OneObjectName

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:29.522

Select * from SYS.ALL_REFS where owner = :own

and TABLE_NAME = :OneObjectName

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:29.578

Select l.TABLE_NAME, l.LOG_GROUP_NAME, c.COLUMN_NAME, l.ALWAYS

, c.logging_property, l.log_group_type, l.generated

from SYS.ALL_LOG_GROUPS l, SYS.ALL_LOG_GROUP_COLUMNS c

where l.OWNER = c.OWNER (+)

and l.owner = :own

and l.LOG_GROUP_NAME = c.LOG_GROUP_NAME (+)

and l.TABLE_NAME = c.TABLE_NAME (+)

and l.table_name = :OneObjectName

order by l.TABLE_NAME, l.LOG_GROUP_NAME, c.POSITION

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:29.838

Select * from SYS.ALL_INDEXES i where table_owner = :own

and index_type <> ‘LOB’

and table_name = :OneObjectName

order by 2

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:29.979

SELECT ‘x’

FROM sys.obj$, sys.ind$, sys.USER$, sys.object_usage

WHERE 0=1

Error: ORA-00942: table or view does not exist

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:30.016

Select t.*

from SYS.ALL_TRIGGERS t

where t.table_owner = :own

and t.table_name = :OneObjectName

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:30.196

Select table_name, segment_name, column_name,

index_name, chunk, pctversion, cache, logging, in_row

, freepools, retention

, tablespace_name

, encrypt, compression, deduplication, securefile

from sys.ALL_LOBS

where owner = :own

and table_name = :OneObjectName

:own(VARCHAR[7],IN)=‘MYDBNAME’

:OneObjectName(VARCHAR[12],IN)=‘MYTABLENAME’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:30.389

SELECT value

FROM v$parameter

WHERE LOWER(name) = ‘undo_management’

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:30.416

SELECT value

FROM v$parameter

WHERE LOWER(name) = ‘undo_management’

Error: ORA-00942: table or view does not exist

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:30.488

Session: MYUSERNAME@MYDBINSTANCE

Timestamp: 09:26:30.504

Error: ORA-00900: invalid SQL statement

4

Doing any of the following should make the error go away. I’m not 100% sure about the 3rd one, though. I tried to reproduce the problem using Toad 10.6 but could not.

  1. ask for someone to grant you the SELECT privilege on V$PARAMETER

  2. upgrade to Toad 12.1 or newer

  3. Click the first toolbar button on the script tab. In the dialog that appears, go to the “Storage Objects” tab. Set the “Lob Parameters” tab to “No Lob Parameters”

5

Oops, for #3 I meant uncheck “Lob Storage” on the “Storage Objects” tab.

6

Thank you for your help. Option 3, un-checking the Lob Storage check box, did solve the problem. I am curious what I am losing by un-checking this check box, if you happen to know.

I can’t seem to get the attention of a DBA to look at item 1 for me. Oh well.

I am pursuing upgrading a later version of Toad. I’ll let everyone know if that resolves this issue…

7

Well, since it caused an error, you aren’t losing anything! :slight_smile:

With that option checked, when it doesn’t cause an error, it causes lob storage parameters to be included in your table script. For example:

CREATE TABLE JDORLON.DEPT

(

DEPTNO NUMBER(2),

DNAME VARCHAR2(14 BYTE),

LOC VARCHAR2(13 BYTE),

A_CLOB CLOB

)

LOB (A_CLOB) STORE AS SECUREFILE (

TABLESPACE USERS

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

NOCACHE

LOGGING

INDEX (

TABLESPACE USERS

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

STORAGE (

INITIAL 104K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

TABLESPACE USERS

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

);

That index part of the lob storage clause is ignored by Oracle, so it was removed from the script in later versions of Toad. The above was generated by Toad 10.6.