-- These directories necessary to read the XML documents
create directory clobxml as '/tmp/xml/clobData'

/
create directory strucxml as '/tmp/xml/strucData'
/

-- This function takes a directory name and filename and returns a clob

create or replace function getdocument(
  p_directory in varchar2,
  p_filename  in varchar2)
  return clob
is
  l_bfile bfile;

  l_clob  clob;
begin
  l_bfile := bfilename(p_directory, p_filename);
  dbms_lob.open(l_bfile);
  dbms_lob.createtemporary(l_clob, true, dbms_lob.session);
  dbms_lob.loadfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile));
  dbms_lob.close(l_bfile);

  return l_clob;
end getdocument;
/

-- This temporary table will hold the contents of a directory 
-- for the duration of a transaction
create global temporary table dir_list(filename varchar2(255))
on commit delete rows

/

grant javauserpriv to sdillon
/

-- This is a Java stored procedure for getting all those files in
-- a directory and inserting them into our DIR_LIST table
create or replace and compile java source named "DirList"
as

import java.io.*;
import java.sql.*;
public class DirList {
  public static void getList(String directory)
                     throws SQLException {
    File path = new File(directory);
    String[] list = path.list();
    String element;

    for(int i = 0; i < list.length; i++) {

      element = list[i];
      #sql { INSERT INTO DIR_LIST (FILENAME)
             VALUES (:element) };
    }
  }
}
/

-- This is a PL/SQL wrapper around a Java stored procedure. This 
-- lets us call Java from PL/SQL.
create or replace procedure get_dir_list( p_directory in varchar2 )

as language java
name 'DirList.getList(java.lang.String)';
/

-- Create a table for the non XML SchemaÐbased XML content
create table clob_xml(
  filename varchar2(200),
  po       xmltype
)
/

-- Create a table for the XML SchemaÐbased XML content. Notice the xmltype column

-- specification that tells Oracle to use a particular XML Schema definition for 
-- the storage of this column's XML content.
create table structured_xml(
  filename varchar2(200),
  po       xmltype
)
xmltype column po
xmlschema "http://asktom.oracle.com/~sdillon/xsd/purchaseOrder.xsd"
element "PurchaseOrder"
/