Solved

Loading delimited text via PLSQL

Posted on 2009-07-16
6
797 Views
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?
0
Comment
Question by:GNOVAK
6 Comments
 
LVL 29

Assisted Solution

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

Author Comment

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

Assisted Solution

by:Andytw
Andytw earned 200 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:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2448  

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

BEGIN

 EXECUTE IMMEDIATE 'truncate table your_table';
 

 INSERT INTO your_table

 SELECT * FROM ext_table;

END;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:GNOVAK
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?
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24873371
GNOVAK:
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.
0
 
LVL 47

Accepted Solution

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

create directory med as 'c:\med' ;  

GRANT READ  ON DIRECTORY med TO lsmedical;

GRANT WRITE ON DIRECTORY med TO lsmedical;

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',
               'med-us-formatted.txt','med-us-insulin-formatted.txt'));


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

SQL> select * from lsmedical.USERMEDBASIC_EXT;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now