Loading delimited text via PLSQL

Posted on 2009-07-16
Medium Priority
Last Modified: 2013-12-19
We receive delimited text files ('|') that we have typically loaded via a CTL file utilizing SQLloader to our 11G Oracle Server.  We then manipulate this data using PLSQL.
Is there any way to utilize PLSQL to either launch SQLLOADER or write the CTL file inside plsql?
The CTL files all have a "Truncate into ..." statement... can this be utilized in PLSQL?
What is the best way to roll this into one PLSQL package or function?
Question by:GNOVAK
LVL 29

Assisted Solution

MikeOM_DBA earned 200 total points
ID: 24871221
Create 'ORGANIZATION EXTERNAL' tables on those files.

Author Comment

ID: 24871381
Can you expand on that?
I'm unfamiliar with the term, how to create and how to address with PLSQL.
LVL 11

Assisted Solution

Andytw earned 800 total points
ID: 24873013
MikeOM_DBA is talking about Oracle External tables.  ORGANIZATION EXTERNAL is part of the syntax for creating an External Table.

In Oracle, an external table allows you to access a flat file from Oracle as though it were a normal table (with a few restrictions).  So you can then SELECT from this table, just as you would on a normal table.  

You define the file structure, using a similar syntax as SQL*Loader control file (so you shouldn't have much trouble migrating your existing CTL definition).  Once you've set up the table, you can then access/load/transform the contents of your flat text file directly in PL/SQL.  

The Oracle Adminstrator's guide gives a full example of how to create an external table:

Once you've created it, then in PL/SQL it will be as simple as:

 EXECUTE IMMEDIATE 'truncate table your_table';
 INSERT INTO your_table
 SELECT * FROM ext_table;

Open in new window

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 24873291
If I have a text file named MYTEXT.TXT that I recieve today and I set up the above, can I replace the file in the same directory with the same name next month and not have to change anything?

In other words, once set up, is there just a pointer to the file or does it hang on to something else?
LVL 11

Expert Comment

ID: 24873371
Yes, that's exactly right.  When you create the external table, you point it to a directory and a file.  Whenever you do SELECT ... FROM ext_table, then Oracle will read the records from your file.  

So, if you replace MYTEXT.TXT (in the same directory) every month, you don't need to change any code.  Just run your code and it will pick up the contents of whatever file is there at the time.
LVL 48

Accepted Solution

schwertner earned 1000 total points
ID: 24878794
This is an example ( OS directory 'c:\med'  should be created by OS statement!)

create directory med as 'c:\med' ;  



drop table lsmedical.USERMEDBASIC_EXT;

create table lsmedical.USERMEDBASIC_EXT (
                   PZN  VARCHAR2(33),
                   NAME VARCHAR2(111)   )
    organization external  
    (type oracle_loader  
     default directory med
     access parameters (records delimited by newline
     fields terminated by '|')
     location ('med-de-formatted.txt','med-de-insulin-formatted.txt',

Now you can (only SELECT !!!!!!) select rows

SQL> select * from lsmedical.USERMEDBASIC_EXT;

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question