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

1. I have created a separate table, to which I am inserting my file.
2. That table has a trigger, to get the specified information, and insert it into the correct table (which is not up for review)
3. 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,
 "IN_CON_LOG" NUMBER(1,0), 
 "ZIS_EVENT" 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 = @(
 "filelist21.dat"
 "filelist22.dat",
 "filelist23.dat",
 "filelist25.dat",
 "filelist121a.dat",
 "filelist121b.dat",
 "filelist122a.dat",
 "filelist122b.dat"
)
$ctlFile = "$PSScriptRoot\load_data.ctl"
foreach ($f in $dataFiles) {
 (Get-Content $ctlFile) -Replace "infile '[^']*'", "infile '$($PSScriptRoot)\$($f)'" | 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?

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

1. I have created a separate table, to which I am inserting my file.
2. That table has a trigger, to get the specified information, and insert it into the correct table (which is not up for review)
3. 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,
 "IN_CON_LOG" NUMBER(1,0), 
 "ZIS_EVENT" 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 = @(
 "filelist21.dat"
 "filelist22.dat",
 "filelist23.dat",
 "filelist25.dat",
 "filelist121a.dat",
 "filelist121b.dat",
 "filelist122a.dat",
 "filelist122b.dat"
)
$ctlFile = "$PSScriptRoot\load_data.ctl"
foreach ($f in $dataFiles) {
 (Get-Content $ctlFile) -Replace "infile '[^']*'", "infile '$($PSScriptRoot)\$($f)'" | 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?

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

1. I have created a separate table, to which I am inserting my file.
2. That table has a trigger, to get the specified information, and insert it into the correct table (which is not up for review)
3. 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,
 "IN_CON_LOG" NUMBER(1,0), 
 "ZIS_EVENT" 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 = @(
 "filelist21.dat"
 "filelist22.dat",
 "filelist23.dat",
 "filelist25.dat",
 "filelist121a.dat",
 "filelist121b.dat",
 "filelist122a.dat",
 "filelist122b.dat"
)
$ctlFile = "$PSScriptRoot\load_data.ctl"
foreach ($f in $dataFiles) {
 (Get-Content $ctlFile) -Replace "infile '[^']*'", "infile '$($PSScriptRoot)\$($f)'" | Out-File $ctlFile -Encoding ASCII
 sqlldr mckdev@$SecretDatabase/$SecretPassword control=$PSScriptRoot\load_data.ctl
}

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?

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

1. I have created a separate table, to which I am inserting my file.
2. That table has a trigger, to get the specified information, and insert it into the correct table (which is not up for review)
3. 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,
 "IN_CON_LOG" NUMBER(1,0), 
 "ZIS_EVENT" 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 = @(
 "filelist21.dat"
 "filelist22.dat",
 "filelist23.dat",
 "filelist25.dat",
 "filelist121a.dat",
 "filelist121b.dat",
 "filelist122a.dat",
 "filelist122b.dat"
)
$ctlFile = "$PSScriptRoot\load_data.ctl"
foreach ($f in $dataFiles) {
 (Get-Content $ctlFile) -Replace "infile '[^']*'", "infile '$($PSScriptRoot)\$($f)'" | 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?