Home » RDBMS Server » Server Utilities » External Table - BADFILE overwrites every time.
External Table - BADFILE overwrites every time. [message #236102] Wed, 09 May 2007 01:03 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have a module, which loads data from flat(csv) files. We are using External talbles for this purpose.

Following is a sample code.

:
:
ORGANIZATION EXTERNAL
         (
          TYPE ORACLE_LOADER
          DEFAULT DIRECTORY EXT_CBS_DIR
          ACCESS PARAMETERS
             (
                RECORDS DELIMITED BY NEWLINE
                BADFILE     BOIDW_BAD_DIR:'bad_cbs_SAVINGS_ext'
                LOGFILE     BOIDW_LOG_DIR:'log_cbs_SAVINGS_ext'
                DISCARDFILE BOIDW_DISC_DIR:'disc_cbs_SAVINGS_ext'
                FIELDS TERMINATED BY ','
                RTRIM
                MISSING FIELD VALUES ARE NULL

:
:


And, Followig is the sample Code how we load the data. And, the below mentioned code runs ina loop.

ALTER TABLE EXT_TABLE LOCATION('file1.csv');
INSERT INTO TABLE1 SELECT * FROM EXT_TABLE;

ALTER TABLE EXT_TABLE LOCATION('file2.csv');
INSERT INTO TABLE1 SELECT * FROM EXT_TABLE;

ALTER TABLE EXT_TABLE LOCATION('file3.csv');
INSERT INTO TABLE1 SELECT * FROM EXT_TABLE;


My problem here is, the LOGFILE(log_cbs_SAVINGS_ext)contains all the log and error entries i,e. file1.csv, file2.csv and file3.csv.

But the BADFILE contains bad records of the last file only, i.e file3.csv. But, I want a incremenal of all the bad records. In this case its of file3.csv. I.e For every read it overwrites the BADFILE.

Can anybody help me.

Brayan.
Re: External Table - BADFILE overwrites every time. [message #236115 is a reply to message #236102] Wed, 09 May 2007 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try:
alter table ext_table access parameters (badfile 'new_badfile_each_time');

Regards
Michel
Re: External Table - BADFILE overwrites every time. [message #236141 is a reply to message #236115] Wed, 09 May 2007 03:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Look in the doco. There is a way to include a Process ID in the file name - this would give you unique file names for each load.

Ross Leishman
Re: External Table - BADFILE overwrites every time. [message #236178 is a reply to message #236102] Wed, 09 May 2007 04:25 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Hi,

Quote:
Did you try:
alter table ext_table access parameters (badfile 'new_badfile_each_time');



No, I did not run above said command.

Ross,

According to this document, Even if we specify %p it will overwrite in my case. Beause in one process, I have multiple files to be read. So, everytime I read a file, bad records are overwritten in the bad file.

Brayan.
Re: External Table - BADFILE overwrites every time. [message #236532 is a reply to message #236178] Thu, 10 May 2007 03:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are you going to change the contents of the EOT data file whilst the process is running?

If so, then the same process that replaces the data file should archive the old badfile.

If not, then the BADFILE is just going to contain one copy of the bad rows no matter how many times you read the EOT. Surely this is a good thing.

Ross Leishman
Re: External Table - BADFILE overwrites every time. [message #236545 is a reply to message #236102] Thu, 10 May 2007 04:22 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Ross,

Are you going to change the contents of the EOT data file whilst the process is running?
No, I'm changing the contents of the EOT(flat files) while the process is reading data.

If so, then the same process that replaces the data file should archive the old badfile.

If not, then the BADFILE is just going to contain one copy of the bad rows no matter how many times you read the EOT. Surely this is a good thing.

That's correct.
But, My requirement is to know how many bad records were there in an entire upload process.

E.g
ALTER TABLE EXT_TABLE LOCATION('file1.csv');
INSERT INTO TABLE1 SELECT * FROM EXT_TABLE;

ALTER TABLE EXT_TABLE LOCATION('file2.csv');
INSERT INTO TABLE1 SELECT * FROM EXT_TABLE;

ALTER TABLE EXT_TABLE LOCATION('file3.csv');
INSERT INTO TABLE1 SELECT * FROM EXT_TABLE;


Suppose in the above scenario, assume each file(file1.csv, file2.csv, file3.csv) contain 2 bad records. While I run the process I'll get only tow records instead of six.

I understand that this is a normal behaviour. Is there a way to do, what I want, instead of archive the BADFILE after each read of csv file.

Brayan.
Re: External Table - BADFILE overwrites every time. [message #236554 is a reply to message #236545] Thu, 10 May 2007 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why didn't you try what I suggested?

Regards
Michel
Re: External Table - BADFILE overwrites every time. [message #236593 is a reply to message #236102] Thu, 10 May 2007 05:40 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Michel,

That means I have to do as following.

begin
execute immediate('alter table ext_table access parameters ( 
						 	   			 				   RECORDS DELIMITED BY NEWLINE
							               				   BADFILE     BOIDW_BAD_DIR:''bad_ext_table_1'')
														   location(''01emp.csv'')');
insert into src_table select * from ext_table;

execute immediate('alter table ext_table access parameters ( 
						 	   			 				   RECORDS DELIMITED BY NEWLINE
							               				   BADFILE     BOIDW_BAD_DIR:''bad_ext_table_2'')
														   location(''02emp.csv'')');
insert into src_table select * from ext_table;
end;


I was trying to find will it be possible do wihout defining BADFILE everytime Embarassed .

Brayan.
Re: External Table - BADFILE overwrites every time. [message #236597 is a reply to message #236593] Thu, 10 May 2007 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you define input file each time, why not badfile?
Quite the opposite having a badfile dependent on the input file is a good practice.

Regards
Michel
Re: External Table - BADFILE overwrites every time. [message #236603 is a reply to message #236102] Thu, 10 May 2007 06:18 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

If you look at the LOGFILE file, its appending. I was thinking why not BADFILE ?

Anyways, Thanks a lot.

Brayan.
Re: External Table - BADFILE overwrites every time. [message #236610 is a reply to message #236603] Thu, 10 May 2007 06:37 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! I see.
Log file is just a log file.
Bad file is designed to be used as an input file as soon as the data are fixed or the problem is solved.

Regards
Michel
Previous Topic: PO Receipts Conversion
Next Topic: sql loader help
Goto Forum:
  


Current Time: Sat Dec 10 20:49:34 CST 2016

Total time taken to generate the page: 0.10106 seconds