Database Software

How can I insert the data, without changing the content...(be aware two different schema's...)

Made some attempts towards CLOB etc, but this changes the content...

...and CTAS creates a BASICFILE BINARY XML, instead of the needed BASICFILE CLOB...after forcing BASICFILE via "alter session set db_securefile=never;"

dbv 11.2.0.2.0

SQL> conn mediawiki/xml
Connected.

SQL> select dbms_metadata.get_ddl('TABLE','MEDIAWIKI_STORAGE','MEDIAWIKI') from dual;

DBMS_METADATA.GET_DDL('TABLE','MEDIAWIKI_STORAGE','MEDIAWIKI')
--------------------------------------------------------------------------------
  CREATE TABLE "MEDIAWIKI"."MEDIAWIKI_STORAGE"
   (    "ID" NUMBER,
        "XMLSTORE" "XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
  DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "WIKIMEDIA"
 XMLTYPE COLUMN "XMLSTORE" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "WIKIMEDIA" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  ) ALLOW NONSCHEMA DISALLOW ANYSCHEMA

SQL> conn mediawiki_clob/xml
Connected.

SQL> select dbms_metadata.get_ddl('TABLE','MEDIAWIKI_CLOB','MEDIAWIKI_CLOB') from dual;

DBMS_METADATA.GET_DDL('TABLE','MEDIAWIKI_CLOB',USER)
--------------------------------------------------------------------------------

  CREATE TABLE "MEDIAWIKI_CLOB"."MEDIAWIKI_CLOB"
   (    "ID" NUMBER,
        "XMLSTORE" "SYS"."XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "WIKIMEDIA_CLOB"
 XMLTYPE COLUMN "XMLSTORE" STORE AS BASICFILE CLOB (
  TABLESPACE "WIKIMEDIA_CLOB" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))


SQL> insert /*+ append */
  2  into mediawiki_clob
  3  (id, xmlstore)
  4  select id, xmlstore
  5  from "MEDIAWIKI".mediawiki_storage
  6  ;
into mediawiki_clob
     *
ERROR at line 2:
ORA-31196: XML nodes over 64K in size cannot be printed


SQL> select mw.xmlstore
  2   from MEDIAWIKI.mediawiki_storage mw
  3    where rownum =1;

XMLSTORE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<page xmlns="http://www.mediawiki.org/xml/export-0.4/" xsi:noNamespaceSchemaLocation="http://www.mediawiki.org/xml/export-0.4/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <title>Scheepstimmermanstraat</title>
  <id>3321328</id>
  <revision>
    <id>367176596</id>
    <timestamp>2010-06-10T10:17:19Z</timestamp>
    <contributor>
      <username>SmackBot</username>
      <id>433328</id>
    </contributor>
    <minor/>
    <comment>Date maintenance tags and general fixes: build 420:</comment>
    <text xml:space="preserve">{{multiple issues|wikify =April 2010|unreferenced =June 2010|orphan =November 2006}}


The &apos;&apos;&apos;Scheepstimmermanstraat&apos;&apos;&apos; (Shipwright&apos;s Street) in [[Amsterdam]] is famous {{Citation needed|date=June 2010}} for its 60 unique houses designed by architects
such as Herzberger (126), van Velsen (120), Höhne &amp; Rapp (62) and [[MVRDV]] (26 &amp; 40).

The landscape architect, Adriaan Geuze of the firm West 8, planned a street on which residents were free to design their own houses on the waterfront.

[[Image:stmsfront.jpg|thumb]]
[[Image:stmsback.jpg|thumb]]

==External links==
*[http://www.atcb.nl/en/home/Amsterdam+city+on+the+water/living/article/xp/content_artikel.Living+-+Scheepstimmermanstraat/default.aspx Amsterdam Tourism and Convention Board] (English)
*[http://www.amsterdam.nl/aspx/get.aspx?xdl=/views/amsterdamnl/xdl/catch&amp;ItmIdt=00004000&amp;SitIdt=00000002&amp;VarIdt=00000001&amp;popup=normal&amp;mode=mailcrew Official tourism site (Dutch)]
* [http://www.parool.nl/artikelen/HOM/1023166260021.html Parool (Dutch)]

[[Category:Streets in Amsterdam]]</text>
  </revision>
</page>


Elapsed: 00:00:00.49

Maybe using XMLSERIALIZE ?

Edited by: Marco Gralike on Nov 6, 2010 1:48 AM