Solved

Loading delimited text via PLSQL

Posted on 2009-07-16
6
831 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

630 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