6 Replies Latest reply on Mar 28, 2013 7:52 PM by Richard Harrison .

    Load XML files using SQL Loader

    beta32c
      Hi,

      I came across something new while loading the XML files using SQL Loader. Can you team help.

      I am using SQL Loader to load the XML files which are saved in a .dat file that has the filenames that needs to loaded. The below script works fine.

      load data
      infile '/dbfs/FS1/BI/Xml_Dir/PwaySchema/scripts/PWXML_Payload_filelist.dat05'
      append
      into table PWAYWORKFILE_TABLE
      *(*
      filename filler char(120),
      WORKFILE lobfile(filename) terminated by eof)


      But recently i wanted to do parallel load like running multiple .ctl files and each of this .ctl file will use a seperate file and load the data into a single table PWAYWORKFILE_TABLE. But while doing so I wanted to also load the sequence Numbers. So I changed the .ctl file to look like below,

      load data
      infile '/dbfs/FS1/BI/Xml_Dir/PwaySchema/scripts/PWXML_Payload_filelist.dat05'
      append
      into table PWAYWORKFILE_TABLE
      (
      SEQUENCEID "PWAYWORKFILE_TABLE_SEQ.nextval",
      filename filler char(120),
      WORKFILE lobfile(filename) terminated by eof)

      The contents of the /dbfs/FS1/BI/Xml_Dir/PwaySchema/scripts/PWXML_Payload_filelist.dat05 were like below,

      /dbfs/FS1/BI/Xml_Dir/APSXPWA1.S03
      /dbfs/FS1/BI/Xml_Dir/02g40u8l.S01
      /dbfs/FS1/BI/Xml_Dir/APSXYH4W.E01
      /dbfs/FS1/BI/Xml_Dir/1l1x0poc.E01
      /dbfs/FS1/BI/Xml_Dir/0e2t02nq.S01
      /dbfs/FS1/BI/Xml_Dir/0jjc1jet.E01
      /dbfs/FS1/BI/Xml_Dir/2b4n02uv.S01
      /dbfs/FS1/BI/Xml_Dir/211l0i5r.E01
      /dbfs/FS1/BI/Xml_Dir/15gi2bqb.E01
      /dbfs/FS1/BI/Xml_Dir/APSXYJTY.E01


      When i ran the control file I received error messages like below,

      SQL*Loader-509: System error: No such file or directory
      SQL*Loader-502: unable to open data file 'data/dw/xml/Admin_comp_Xmls/0o6j0ugv.E01' for field WORKFILE table PWAYWORKFILE_TABLE
      SQL*Loader-553: file not found


      But when i changed the input file to have the file name starting with two slashs(//) they worked fine.

      //dbfs/FS1/BI/Xml_Dir/APSXPWA1.S03
      //dbfs/FS1/BI/Xml_Dir/02g40u8l.S01

      My questions

      *1) Why is that? why is oracle not recognizing the first slash? Why is it accepting the file when it has only two slashes?*
      *2) Can i use a single sequence and use them in two control files which will be running parallelly from UNIX to load XML files. Will all my sequence numbers be unique. Or is it advisable to create two sequence to handle the numbering?*

      Thanks,
      Rakesh

      Edited by: beta32c on Mar 27, 2013 6:07 AM
        • 1. Re: Load XML files using SQL Loader
          Marco Gralike
          "*dbfs*" as in Oracle Database File System...???
          • 2. Re: Load XML files using SQL Loader
            beta32c
            Yes. Its were we receive the files currently and we are supposed to load these XML files into a XMLType table which is built around a Schema.

            Edited by: beta32c on Mar 27, 2013 6:12 AM
            • 3. Re: Load XML files using SQL Loader
              Marco Gralike
              I was asking, because I was thinking that this might be an DBFS implementation issue, maybe even a bug. Is the problem reproducable if you use a more common file system like EXT3/4 or maybe ZFS.

              To test this you could mount temporarily a "normal" filesystem on top of the /dbfs directory (after unmounting dbfs on this directory locator of course)... This would give you insight if it is a SQL*Loader problem ofr a "file/directory" handling issue in DBFS (user space related?).

              Edited by: Marco Gralike on Mar 27, 2013 3:09 PM
              • 4. Re: Load XML files using SQL Loader
                Marco Gralike
                BTW I don't have that much experience with DBFS.

                Question: while mounting it on top of the database will the XML files also automatically be shredded in a XMLType tables (Object Relational or Binary XML based storage)...?

                I know, and tested if often but based on XMLDB / XDB protocol server mechanisms developed by the XMLDB Development team. As you might know, DBFS is part of the Securefile Development team and I would be positively surprised if they develop along side...
                • 5. Re: Load XML files using SQL Loader
                  beta32c
                  I am not sure if the issue was understood correctly.

                  The files are all xml files...
                  The thing i m curous is why does oracle accept the file name with the two slashes and not with one slash.. if i use file name with only one slash in the beginning it tries to search for file without the starting slash and yes.. without the slash oracle cannot find the file.

                  Sorry if you guys actually understood my issue totally.. Anyways regarding your question... Yes these XML files are going to get loaded into an Object Relational table PWAYWORKFILE_TABLE. After the register schema the create table script will look like below. Yes since i have created the table with the Schema they are shredded into XMLType tables..


                  CREATE TABLE "PWAYWORKFILE_TABLE"
                  (
                  SequenceID NUMBER,
                  DL_CLM_FOLDER_ID VARCHAR2(30),
                  CUST_CLM_REF_ID VARCHAR(25),
                  EMS_FILE_NM VARCHAR(30),
                  REC_DT DATE DEFAULT SYSDATE,
                  WORKFILE XMLTYPE
                  )
                  XMLTYPE COLUMN WORKFILE
                  STORE AS OBJECT RELATIONAL
                  XMLSCHEMA "workfile.xsd" ELEMENT "PwayWorkfile"



                  All those /dbfs/FS1/BI/Xml_Dir/ are all folders in UNIX and the XML files are with the file name as ...

                  0s2n09tp.E01
                  1l5y3uuv.E01
                  0c0c1xki.E01
                  APSXYFTA.E01
                  1qmf2knr.S01
                  • 6. Re: Load XML files using SQL Loader
                    Richard Harrison .
                    Hi,
                    Seems like a bug. Maybe something specific to dbfs if that is what your are using. Do you have a different filesystem type you can try this with to prove it?

                    To answer your second question the sequence number will be unique between parallel sessions.

                    Harry