Toad World® Forums

xmltype storage clause


#1

start with this statement
CREATE TABLE pangaea_all (
the_xml XMLTYPE
)
XMLTYPE COLUMN “THE_XML” STORE AS BINARY XML;

then in Toad Schema browser the script tab produces this:
DROP TABLE PALEO.PANGAEA_ALL CASCADE CONSTRAINTS;

CREATE TABLE PALEO.PANGAEA_ALL
(
THE_XML SYS.XMLTYPE
)
TABLESPACE PALEO
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
XMLTYPE THE_XML STORE AS CLOB
( TABLESPACE PALEO
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
INDEX (
TABLESPACE PALEO
STORAGE (
INITIAL 1M
NEXT 1
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;

notice the xmltype the_type store as clob (not what the original created sprecified).

Now SQLDeveloper sql tab shows this:
CREATE TABLE “PALEO”.“PANGAEA_ALL”
(
“THE_XML” “SYS”.“XMLTYPE”
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE “PALEO” XMLTYPE COLUMN “THE_XML” STORE AS BASICFILE BINARY XML
(
TABLESPACE “PALEO” ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
)
ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;

Here xmltype column “THE_XML” store as basicfile binary xml appears as created and as I think it should. Anybody got an explantation?

software versions: Toad 10.1, Oracle client 11g


#2

You might want to post this to the toad beta group and then participate in the
beta program (it’s free). This is the kind of stuff that people find and
the devs fix – and new beta builds are usually posted twice a week so you
can test drive fixes asap J


#3

SQL Developer shows the DDL differently b/c it uses DBMS_METADATA package to
generate the DDL. Toad doesn’t use this package.

You may want to report this to Quest Support…


#4

The developer is aware of the problem and working on it. No need to go through
support unless you just want to. :slight_smile:


#5

Although now that John has rung in – which usually means as good as
already fixed – you surely want to join the beta so you can test drive it
when he posts a message saying it’s all done J


#6

Thanks, this should be fixed in the next version (and next beta)