Solved

Loading delimited text via PLSQL

Posted on 2009-07-16
6
818 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 48

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

685 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