Home » RDBMS Server » Server Utilities » SQL Loader for XML File (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
SQL Loader for XML File [message #674536] Thu, 31 January 2019 14:46 Go to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
HI All,

I just want to load a XML file into a table which we recieve on daily basis.he File_ID value should increment everytime a new value comes and File Name should be populated from File as the File name will have date stamp attached to it.

OPTIONS ( skip=1)  
LOAD DATA   
infile 'C:\Users\HVentrap\Desktop\INVCTL\STATUS*.xml'   
INTO TABLE PQINF.XXPQ_EDI_INV_STS_FILE                                  
(FILE_ID         "XXPQ_EDI_INV_STS_F.NEXTVAL"  
,FILE_NAME  
,DELIVERY_FILE  
,PROCESS_FLAG       "N"  
,CREATION_DATE      "SYSDATE"  
,CREATED_BY         "FND_GLOBAL.USER_ID"  
,LAST_UPDATE_DATE   "SYSDATE"  
,LAST_UPDATED_BY   "FND_GLOBAL.USER_ID"  
)  


CREATE TABLE XXPQ_EDI_INV_STS_FILE

(

  FILE_ID           NUMBER,

  FILE_NAME         VARCHAR2(200 BYTE),

  DELIVERY_FILE     SYS.XMLTYPE,

  PROCESS_FLAG      VARCHAR2(1 BYTE),

  CREATION_DATE     DATE,

  CREATED_BY        NUMBER,

  LAST_UPDATE_DATE  DATE,

  LAST_UPDATED_BY   NUMBER

)


Attached sample XML File for reference.PLease suggest how Can I do this
Re: SQL Loader for XML File [message #674537 is a reply to message #674536] Thu, 31 January 2019 15:16 Go to previous messageGo to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Tried Below but still does not work


LOAD DATA
INFILE 'PROQUEST_STATUS_12_12_2018.xml' 
APPEND
INTO TABLE PQINF.XXPQ_EDI_INV_STS_FILE
xmltype(DELIVERY_FILE) 
(FILE_ID         "XXPQ_EDI_INV_STS_F.NEXTVAL"
,FILE_NAME     "PROQUEST_STATUS_12_12_2018.xml"
,DELIVERY_FILE    (filename)
,PROCESS_FLAG       "N"
,CREATION_DATE      "SYSDATE"
,CREATED_BY         "FND_GLOBAL.USER_ID"
,LAST_UPDATE_DATE   "SYSDATE"
,LAST_UPDATED_BY   "FND_GLOBAL.USER_ID"
)
Re: SQL Loader for XML File [message #674538 is a reply to message #674537] Thu, 31 January 2019 18:08 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
see a picture of my car.
It does not work.
Tell me how to make my car go.

I just paraphrased this thread.

Stating "it does not work" while I suspect it is true, it is 100% & totally devoid of any actionable detail.

use COPY & PASTE to actually show us exactly what you do & how Oracle responded.
Re: SQL Loader for XML File [message #674553 is a reply to message #674536] Fri, 01 February 2019 05:58 Go to previous messageGo to next message
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
I am no expert with SQL*Loader, but even I can see so many mistakes in your controlfile. You are not even telling it the format of the infile.

Some more really obvious ones:

infile 'C:\Users\HVentrap\Desktop\INVCTL\STATUS*.xml'
You can't use an asterisk wildcard in release 11

,CREATION_DATE "SYSDATE"
You can't put the word SYSDATE in the column CREATION_DATE, those double quotes have to go.



Re: SQL Loader for XML File [message #674569 is a reply to message #674538] Fri, 01 February 2019 16:53 Go to previous messageGo to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Below is the CTL Code i'm using now to load:

LOAD DATA
INFILE *
APPEND INTO TABLE PQINF.XXPQ_EDI_INV_STS_FILE
TRAILING NULLCOLS
   (FILE_ID                "PQINF.EDI_STS_FILES_S.NEXTVAL"
   ,FILE_NAME              CONSTANT   'PROQUEST_STATUS_12_12_2018.xml'
   ,DELIVERY_FILE  LOBFILE(PROQUEST_STATUS_12_12_2018.xml) TERMINATED BY EOF
   ,PROCESS_FLAG                       CONSTANT 'N'
   ,CREATION_DATE                        "SYSDATE"
   ,CREATED_BY                           "APPS.FND_GLOBAL.USER_ID"
   ,LAST_UPDATE_DATE                     "SYSDATE"
   ,LAST_UPDATED_BY                      "APPS.FND_GLOBAL.USER_ID"
   )



Below is the error it shows :
--------------------------------------

SQL*Loader-416: SDF clause for field DELIVERY_FILE in table PQINF.XXPQ_EDI_INV_STS_FILE references a non existent field.

Loader is finished
Re: SQL Loader for XML File [message #674898 is a reply to message #674569] Thu, 21 February 2019 06:08 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Your xml file names need to either be listed at the end of the control file after BEGINDATA, as in the example below or listed in a separate data file and that data file name would either need to be after INFILE in the control file or in the SQL*Loader command line after DATA=. Either way, the names of the xml files would be obtained via the file_name, making sure you indicate some sort of delimiter and set the position appropriately. The xml file names obtained via the file_name would then be reference in the lobfile. You should not be attempting to use the xml file names as literal values in the field descriptions of file_name or lobfile. Please see the example below. I have removed the schema names and functions in the example below, since I do not have your schemas or functions.


-- xml files for testing:
SCOTT@orcl_12.1.0.2.0> HOST TYPE PROQUEST_STATUS_12_12_2018.xml
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
 </ROW>
</ROWSET>


SCOTT@orcl_12.1.0.2.0> HOST TYPE PROQUEST_STATUS_12_13_2018.xml
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
 </ROW>
</ROWSET>

-- SQL*Loader control file:
SCOTT@orcl_12.1.0.2.0> HOST TYPE test.ctl
LOAD DATA
INFILE *
APPEND INTO TABLE XXPQ_EDI_INV_STS_FILE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(FILE_ID                "EDI_STS_FILES_S.NEXTVAL"
,FILE_NAME              POSITION(1)
,DELIVERY_FILE  LOBFILE(file_name) TERMINATED BY EOF
,PROCESS_FLAG                       CONSTANT 'N'
,CREATION_DATE                        "SYSDATE"
,LAST_UPDATE_DATE                     "SYSDATE"
)
BEGINDATA
PROQUEST_STATUS_12_12_2018.xml
PROQUEST_STATUS_12_13_2018.xml

-- table:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE XXPQ_EDI_INV_STS_FILE
  2  (
  3    FILE_ID           NUMBER,
  4    FILE_NAME         VARCHAR2(200 BYTE),
  5    DELIVERY_FILE     SYS.XMLTYPE,
  6    PROCESS_FLAG      VARCHAR2(1 BYTE),
  7    CREATION_DATE     DATE,
  8    CREATED_BY        NUMBER,
  9    LAST_UPDATE_DATE  DATE,
 10    LAST_UPDATED_BY   NUMBER
 11  )
 12  /

Table created.

-- sequence:
SCOTT@orcl_12.1.0.2.0> CREATE SEQUENCE EDI_STS_FILES_S
  2  /

Sequence created.

-- load:
SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Feb 21 03:55:28 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table XXPQ_EDI_INV_STS_FILE:
  2 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM XXPQ_EDI_INV_STS_FILE
  2  /

   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
DELIVERY_FILE
--------------------------------------------------------------------------------
P CREATION_DATE   CREATED_BY LAST_UPDATE_DAT LAST_UPDATED_BY
- --------------- ---------- --------------- ---------------
         1
PROQUEST_STATUS_12_12_2018.xml
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
  </ROW>
  <ROW>
    <DEPTNO>20</DEPTNO>
    <DNAME>RESEARCH</DNAME>
  </ROW>
</ROWSET>
N Thu 21-Feb-2019            Thu 21-Feb-2019

         2
PROQUEST_STATUS_12_13_2018.xml
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <DEPTNO>30</DEPTNO>
    <DNAME>SALES</DNAME>
  </ROW>
  <ROW>
    <DEPTNO>40</DEPTNO>
    <DNAME>OPERATIONS</DNAME>
  </ROW>
</ROWSET>
N Thu 21-Feb-2019            Thu 21-Feb-2019


2 rows selected.
Previous Topic: "ORA-00904 POLTYP: invalid identifier" when export
Next Topic: ORA-19032 Expected XML tag but go no content
Goto Forum:
  


Current Time: Wed Dec 11 18:59:35 CST 2019