| Proper way to create this procedure [message #246366] |
Wed, 20 June 2007 13:40  |
tekline
Messages: 6
Registered: May 2007
|
Junior Member |
|
|
CREATE OR REPLACE PROCEDURE FILE_IMPORT IS
/* Display the contents of the file to DBMS output window*/
/* This cursor holds all ext_table_txt rows containing valid emails*/
CURSOR file_data_rows IS SELECT * FROM ext_table_txt;
rowdata file_data_cur%ROWTYPE;
BEGIN
/* define external directory*/
create or replace directory ext_dir as '/usr/local/ol_inv';
create or replace directory bad_dir as '/usr/local/ol_inv/bad_dir';
create or replace directory log_dir as '/usr/local/ol_inv/log_dir';
/* whom ever is logged in when code is exicuted must have permissions (ex: system)*/
/* Development permissions*/
grant read, write on directory ext_dir to ol_inv_app;
grant read, write on directory bad_dir to ol_inv_app;
grant read, write on directory log_dir to ol_inv_app;
/* Production Permissions*/
-- grant read, write on directory ext_dir to ol_inv_app;
-- grant read, write on directory bad_dir to ol_inv_app;
-- grant read, write on directory log_dir to ol_inv_app;
create table ext_table_txt (
/* define the table elements */
Client_id varchar2(5),
Account_id varchar2(20),
User_id varchar2(30),
First_name varchar2(30),
Last_name varchar2(30),
M_initial varchar2(2),
Email_address varchar2(30),
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
badfile bad_dir:'badfile.bad'
logfile log_dir:'logfile.log'
fields terminated by '|'
missing field values are null
)
location ('contact_source_file.txt')
)
reject limit unlimited;
/* End the external table load process */
/* Open the cursor*/
OPEN file_data_rows;
LOOP
FETCH file_data_rows INTO rowdata
IF file_data_rows%NOTFOUND
THEN
EXIT;
END LOOP;
dbms_output.put_line('File Contents: ');
dbms_output.put_line('-------------- ');
dbms_output.put_line(' ');
dbms_output.put_line('File Contents: ');
dbms_output.put_line('Client ID: '||rowdata.Client_id
dbms_output.put_line('Acct ID: ' ||rowdata.Account_id);
dbms_output.put_line('User ID: ' ||rowdata.User_id);
dbms_output.put_line('First Name:'||rowdata.First_name);
dbms_output.put_line('Last Name: '||rowdata.Last_name);
dbms_output.put_line('Initial: ' ||rowdata.M_initial);
dbms_output.put_line('Email Addr:'||rowdata.Email_address);
dbms_output.put_line('Active Record: '||rowdata.Active);
CLOSE file_data_rows;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END FILE_IMPORT;
/
|
|
|
|
|
|
|
|
| Re: Proper way to create this procedure [message #246662 is a reply to message #246379] |
Thu, 21 June 2007 10:31  |
tekline
Messages: 6
Registered: May 2007
|
Junior Member |
|
|
Maybe I don't quite understand how the external table works in conjuction with what I am attempting to accomplish. I want to load the data from the same external text file (contents change daily) to the external table on a daily basis. I understand creating the table and loading it the first time but what if I want to load the same external table many times after the intial creation and text file load?
Please help me clarify. All the documentation and research I have done thus far does not discuss this type of useage.
|
|
|
|
| Re: Proper way to create this procedure [message #246673 is a reply to message #246366] |
Thu, 21 June 2007 11:05 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Quote: | I want to load the data from the same external text file (contents change daily) to the external table on a daily basis.
|
This is the part I got lost and do not understand at all.
How does the source content change? Are only new rows added with(out) possible change of previous rows? Or does the file contain totally new rows?
Also it is not clear what "external table" you are talking. The only external table I see is ext_table_txt, which is based on content of the source file "contact_source_file.txt". No need to do anything, this external table will everytime contain the up-to-date content of the source file (hope you found this during research).
In your first example you were just printing something to output. I do not see the usage database in this process at all (as you do not store anything and it seems to me you are developing just a text convertor). Maybe it was just a draft demo, but I cannot deduce from this the requirement for the final version.
Could you describe it more in detail (with all database objects involved)?
|
|
|
|
| Re: Proper way to create this procedure [message #246680 is a reply to message #246673] |
Thu, 21 June 2007 11:22 |
tekline
Messages: 6
Registered: May 2007
|
Junior Member |
|
|
Sure. ext_table_txt is just some test code to import and print file contents to output window making sure the process works. The actual process will include an external text file that is replaced daily/weekly by a process that will ftp the file to the same directory location so it can be processed (this is a data extraction from another database and yes, the data will change on a daily basis). Then this external text file will be loaded into an external table where the data can be processed before it is inserted into several different permanent database tables in another database which is not the same as the original database.
This is a daily executed ETL. Does that make sense?
|
|
|
|
| Re: Proper way to create this procedure [message #246686 is a reply to message #246673] |
Thu, 21 June 2007 11:29 |
tekline
Messages: 6
Registered: May 2007
|
Junior Member |
|
|
so essentially a daily data extraction from database 1 will be ftp'd to a directory destination then that same file will be imported (loaded) into an external table -> processed and inserted into permanent database tables of database 2.
The extraction data will be different daily because of the nature of the data.
|
|
|
|
| Re: Proper way to create this procedure [message #246830 is a reply to message #246366] |
Fri, 22 June 2007 02:35 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Finally I hope I got you.
| Quote: | I understand creating the table and loading it the first time but what if I want to load the same external table many times after the intial creation and text file load?
|
So load it the same way as the first time. No need to drop/create the table. It is just a rule how to get data. Actual data will be fetched from file every time you call the procedure.
|
|
|
|
| Re: Proper way to create this procedure [message #246969 is a reply to message #246830] |
Fri, 22 June 2007 11:39 |
tekline
Messages: 6
Registered: May 2007
|
Junior Member |
|
|
Thanks so much for answering my questions. Sorry if they seem a bit novice. All I am doing is chaning the source file that is being accessed by the external table and every time I perform a select on that table a full table scan is being performed and accessible via a select (Oracle 9i and above).
A great way to keep a huge data file separated from the actual database but accessible like it is a table within the database!
I understand in 9i the external table is read only and in 10g it is read and write (UPDATE AND INSERT) as well.
Thanks again for the help!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
