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