Bulkloading xml's into Oracle table Intro
We have some old(in my eyes) software at the place I work at the moment,
Some stuff needs to be done manually, for instance we need to insert a XML file into a Oracle table, which will trigger... stuff
I got annoyed by having to do this manually all the time,
So I decided to automate the process a bit with powershell and Oracle's sqlldr
My knowledge of Oracle is limited hence the asking for a review,
What I did

I have created a separate table, to which I am inserting my file.
That table has a trigger, to get the specified information, and insert it into the correct table (which is not up for review)
Created a powershell script to be able to load files in bulk into the table

Code
The sql script for creating the table
CREATE TABLE mckdev.ogd_xml_table (
  id        NUMBER(10),
  xml       XMLTYPE,
  &quot;IN_CON_LOG&quot; NUMBER(1,0), 
  &quot;ZIS_EVENT&quot; VARCHAR2(35 BYTE), 
);

ALTER TABLE mckdev.ogd_xml_table ADD (
  CONSTRAINT ogd_xml_table_pk PRIMARY KEY (id)
);

CREATE SEQUENCE mckdev.ogd_xml_table_seq;

The load_data.ctl
load data
infile ''
append
into table ogd_xml_table
fields
(  
    filename FILLER CHAR(100),
    xml lobfile( filename) terminated by eof
)

The bulkloader.ps1
$dataFiles = @(
    &quot;filelist21.dat&quot;
    &quot;filelist22.dat&quot;,
    &quot;filelist23.dat&quot;,
    &quot;filelist25.dat&quot;,
    &quot;filelist121a.dat&quot;,
    &quot;filelist121b.dat&quot;,
    &quot;filelist122a.dat&quot;,
    &quot;filelist122b.dat&quot;
)
$ctlFile = &quot;$PSScriptRoot\load_data.ctl&quot;

foreach ($f in $dataFiles) {
    (Get-Content $ctlFile) -Replace &quot;infile '[^']*'&quot;, &quot;infile '$($PSScriptRoot)\$($f)'&quot; | Out-File $ctlFile -Encoding ASCII
    sqlldr mckdev@$SecretDatabase/$SecretPassword control=$ctlFile
}

An example filelist.dat only holds a reference to another XML file.
Questions

Currently I need to have multiple .dat file to be able to load them in bulk.
One for each XML file I want to load into the table. Is there any way around this?

Is this a correct approach or would you have done things differently?
As the ctl file can't be parameterized you'll have to create them for each infile.

As it is quite small, I'd create it on the fly from a here string with the format operator.

This sample script just echoes to screen, the commands to write/execute sqlldr are commented out.

## Q:\Test\2019\01\24\CR_212133.ps1
$dataFiles = @(
    "filelist21.dat"
    "filelist22.dat",
    "filelist23.dat",
    "filelist25.dat",
    "filelist121a.dat",
    "filelist121b.dat",
    "filelist122a.dat",
    "filelist122b.dat"
)

$ctlFile = "$PSScriptRoot\load_data.ctl"

foreach ($dataFile in $dataFiles) {
@'
load data
infile '{0}'
append
into table ogd_xml_table
fields
(
    filename FILLER CHAR(100),
    xml lobfile( filename) terminated by eof
)
'@ -f $dataFile  #| Out-File $ctlFile -Encoding ASCII
    #sqlldr mckdev@$SecretDatabase/$SecretPassword control=$ctlFile
}




&gt; Q:\Test\2019\01\24\CR_212133.ps1
load data
infile 'filelist21.dat'
append
into table ogd_xml_table
fields
(
    filename FILLER CHAR(100),
    xml lobfile( filename) terminated by eof
)
----------------------------------------------------------------------
load data
infile 'filelist22.dat'
append
into table ogd_xml_table
fields
(
    filename FILLER CHAR(100),
    xml lobfile( filename) terminated by eof
)
----------------------------------------------------------------------
%&lt;...snip...&gt;%