Toad World® Forums

Toad DDL generation for VARRAYs seems not right for 10g


#1

Hi folks,

I am using TOAD 10.5.1.3.

TOAD has its own DDL generation routines separate from DBMS_METADATA, right?

I notice that when TOAD generates a CREATE TABLE script for a table containing
VARRAY datatype (such as SDO_GEOMETRY), that the DDL for the VARRAY contains the
LOGGING keyword that Oracle 10g does not like. It works fine on 11gR2, but not
on 10g. Is there a way to have this option suppressed on 10g?

Thanks!

Paul

Try the following:

CREATE TABLE foo3 (shape MDSYS.SDO_GEOMETRY);

Then grab the table DDL from the script tab. The resulting LOGGING keyword
throws “ORA-22853: invalid LOB storage option specification” on
Oracle 10g (but not 11gR2).

CREATE TABLE foo3

(

SHAPE MDSYS.SDO_GEOMETRY

)

COLUMN SHAPE NOT SUBSTITUTABLE AT ALL LEVELS

VARRAY “SHAPE”.“SDO_ELEM_INFO” STORE AS LOB (

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

CACHE

LOGGING

INDEX (

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

VARRAY “SHAPE”.“SDO_ORDINATES” STORE AS LOB (

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

CACHE

LOGGING

INDEX (

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

TABLESPACE ATTR_DATA

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;


This email was scanned by MessageLabs



#2

Hi Paul,

Thanks for the report. I can reproduce the problem.

Looks like your only option is to uncheck LOB storage (or uncheck the whole
storage clause). Setting Logging to Never should work, but I see it isn’t
working. It’s too late to fix these for Toad 10.6 but I can have it fixed
for the first beta after that.

-John