0
\$\begingroup\$

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 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?

\$\endgroup\$
1
\$\begingroup\$

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
}

> 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
)
----------------------------------------------------------------------
%<...snip...>%
\$\endgroup\$
0
\$\begingroup\$

After looking back at it again, I realized I didn't need all those different dataFiles, as all those dataFiles hold only a single reference with the full path to the XML file.

Instead I could get the path for each XML file and overwrite the single dataFile with the correct file path I want to execute.

  • This makes adding another XML file to load, as easy as adding it to the directory I read the files from
  • And removes the need for multiple dataFiles

$xmlDirectories = @(
    "directory1",
    "directory2"
)
$dataFile = "$PSScriptRoot\filelist.dat"
$ctlFile = "$PSScriptRoot\load_data.ctl"

foreach ($dir in $xmlDirectories) {
    foreach ($f in Get-ChildItem -Path "$PSScriptRoot\$dir")  {
        $f.FullName  | Out-File $dataFile -Encoding ASCII
        sqlldr mckdev@$SecretDatabase/$SecretPassword control=$ctlFile
    }
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.