Skip to Main Content
  • Questions
  • Loading External Data, Flat file, into an Oracle Table.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Chat.

Asked: July 02, 2024 - 4:10 pm UTC

Last updated: July 22, 2024 - 12:37 pm UTC

Version: 19.1.0.094

Viewed 1000+ times

You Asked

Hi Tom,
I need to come up with an automated method to insert data (xml_type) from an external flat file into to a table in Oracle Database using SQL Developer.

Read some articles which shows how to load the files by using a control file, but I couldn't find any article about how to automate the process.

Your help is greatly appreciated.

Thanks,
Chat

and Chris said...

You need to use a job scheduler.

To call a SQL Loader script this could be using the operating system's scheduler - cron on *nix and the task scheduler on Windows.

You can also use the database scheduler to run shell scripts on the database server. This then runs inside the database. You could also use the scheduler to run SQL or PL/SQL, e.g. to use an external table to load the data.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Loading Data Using sqlldr

Chat, July 10, 2024 - 10:27 pm UTC

Hello Chris,
Thank you for the reply.
I am using a control file to load my xml data. It works as expected, but it adds an empty row/record to the Oracle Table. Eg. I have 3 records in my data file. When I insert these 3 records into the table, it gives me 4 records. Last record doesn't have any data in it. How can I stop inserting the fourth record.

I copied the control file I used below. Please let me know your thoughts.
load data
infile 'C:\Chat\chat_SORTS_data.xml' -- "str '</CRIME>'"
append
into table XML_DATA_IMPORT_CHAT_TMP
TRAILING NULLCOLS
( XHSQ_XML_MESSAGE LOBFILE( CONSTANT 'C:\Chat\chat_SORTS_data.xml') TERMINATED BY '[XML]' )

Thanks,
Chat
Chris Saxon
July 11, 2024 - 12:35 pm UTC

What exactly is in the XML file?

useful

A reader, July 22, 2024 - 2:08 am UTC

Could you provide an example SQL Loader script that I can use as a starting point for my automation process?
Chris Saxon
July 22, 2024 - 12:37 pm UTC