Solved

Loading delimited text via PLSQL

Posted on 2009-07-16
6
803 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Outer Query not returning data - SQL HELP 16 50
Oracle Subquery bad Join 11 59
Oracle SQL 6 47
ITERATE THROUGH DATES 11 9
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

932 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