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?