blog.gralike.com About Oracle, XMLDB and other interests

[oracle@localhost ~]$ sqlplus / AS sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production ON Thu Jul 29 09:20:24 2010
 
Copyright (c) 1982, 2009, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
 
SQL> CREATE user otn IDENTIFIED BY otn account UNLOCK;
 
User created.
 
SQL> GRANT dba TO otn;
 
GRANT succeeded.
 
SQL> conn otn/otn
Connected.
 
SQL> sho user
USER IS "OTN"
 
SQL> SET pages 5000
SQL> SET LINES 1000
SQL> SET long 10000
 
SQL> SELECT xmltype(cursor(SELECT owner, schema_url, LOCAL FROM all_xml_schemas ORDER BY owner)) FROM dual;
 
XMLTYPE(CURSOR(SELECTOWNER,SCHEMA_URL,LOCALFROMALL_XML_SCHEMASORDERBYOWNER))
-----------------------------------------------------------------------------------------------------------
< ?xml version="1.0"?>
<rowset>
  <row>
    <owner>EXFSYS</owner>
    <schema_url>http://xmlns.oracle.com/rlmgr/rclsprop.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>EXFSYS</owner>
    <schema_url>http://xmlns.oracle.com/rlmgr/rulecond.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>MDSYS</owner>
    <schema_url>http://www.opengis.net/gml/feature.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
...
  </row><row>
    <owner>XDB</owner>
    <schema_url>http://xmlns.oracle.com/xdb/dav.xsd</schema_url>
    <local>NO</local>
  </row>
</rowset>
 
51 rows selected.

 
SQL> sho user
USER IS "OTN"
 
SQL> CREATE OR REPLACE directory XMLDIR AS '/tmp/';
 
Directory created.
 
SQL> declare
  2     rc sys_refcursor;
  3  begin
  4     open rc FOR SELECT * FROM (SELECT owner, schema_url, LOCAL FROM all_xml_schemas ORDER BY owner);
  5  dbms_xslprocessor.clob2file(xmltype(rc).getClobVal(),'TMPDIR','otn_dev_xsd_schema.xml');
  6  end;
  7 ;
 
PL/SQL procedure successfully completed.
 
SQL> commit;
 
commit complete
 
SQL> ! ls -ltra /tmp/*.xml
 
-rw-rw-r-- 1 oracle oracle 6563 Jul 29 09:36 /tmp/otn_dev_xsd_schema.xml