OraFAQ Forum: Server Utilities » How to write a SQLLDR File that ignores the trailing records

Home » RDBMS Server » Server Utilities » How to write a SQLLDR File that ignores the trailing records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to write a SQLLDR File that ignores the trailing records [message #175114]

Wed, 31 May 2006 16:08

gs24x7
Messages: 8
Registered: May 2006

Junior Member

I have a SQLLDR file that is working for right now. But I need to add another line to the file that either ignores a specific kind of record or does not load the trailing records. Is there a way to put in some type of ignore statement so that it knows not to insert that row?

The last line will be something similar to this:

Quote:

20 rows selected.,NXX,BN1,,,,,YES ,,,,,,,,,,,,,

Here is the SQLLDR ctl file:

LOAD DATA (Skip 5)
INFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone.csv' 
BADFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone.bad'
DISCARDFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone.dsc'
INFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone_EBR_Date_I.csv' 
BADFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone_EBR_Date_I.bad'
DISCARDFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone_EBR_Date_I.dsc'
INFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone_New_Contacts_I.csv' 
BADFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone_New_Contacts_I.bad'
DISCARDFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_CCI_Phone_New_Contacts_I.dsc'
INFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone.csv' 
BADFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone.bad'
DISCARDFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone.dsc'
INFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone_EBR_Date_I.csv' 
BADFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone_EBR_Date_I.bad'
DISCARDFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone_EBR_Date_I.dsc'
INFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone_New_Contacts_I.csv' 
BADFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone_New_Contacts_I.bad'
DISCARDFILE '/siebgvsdas1.rccl.com/app/siebel/siebgvsd/logs/opt_out_from_ftp/Siebel_CVP_RCI_Phone_New_Contacts_I.dsc'

INTO TABLE "RCCL_OPT_OUT_TMP"
INSERT

FIELDS TERMINATED BY ','
TRAILING NULLCOLS

  (CHAN_ADDR
, 
   EBR_DATE
, 
   BRA
, 
   CHANN
, 
   SRC_ID
, 
   EBR_SOURCE
, 
   EBR_TYPE
, 
   CALLABLE_FLAG
, 
   ROYAL_FLAG
, 
   CELEBRITY_FLAG
, 
   STATE_FLAG
, 
   CANADA_DMA_TPS_FLAG
, 
   USA_DMA_TPS_FLAG
, 
   NATIONAL_FLAG
, 
   EBR_EXPIRE_DATE DATE 'MM-DD-YYYY'
, 
   DUPLICATE_NUMBER_FLA
, 
   TIER_C_LONG_DISTANCE
, 
   WIRELESS_FLAG
, 
   WIRELESS_N_PORT
, 
   NPA_NXX_FLAG
, 
   BAD_NUMBER
)

This is how the file looks:

4132223017,05/02/2006,RCI,Phone,1-2RT1RW ,New_CVP_Contact,Inquiry,No,,,,,,,,,,WIR,WIR,,
9184233705,05/02/2006,RCI,Phone,1-2RT1UB ,New_CVP_Contact,Inquiry,No,,,SDNC,,,NDNC,07/31/2006,,,,,,
2259250948,05/02/2006,RCI,Phone,1-2RT1TJ ,New_CVP_Contact,Inquiry,YES ,,,,,,,,,,,,,
9098643601,05/03/2006,RCI,Phone,1-2RT5VX ,New_CVP_Contact,Inquiry,No,,,,,,NDNC,06/02/2006,,,,,,
20 rows selected.,NXX,BN1,,,,,YES ,,,,,,,,,,,,,  THIS IS THE LINE THAT I DO NOT WANT TO LOAD

Report message to a moderator

Re: How to write a SQLLDR File that ignores the trailing records [message #175117 is a reply to message #175114]

Wed, 31 May 2006 16:42

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Check whether this helps
http://www.orafaq.com/forum/t/27004/0/
Regards

Report message to a moderator

Re: How to write a SQLLDR File that ignores the trailing records [message #175119 is a reply to message #175117]

Wed, 31 May 2006 17:03

gs24x7
Messages: 8
Registered: May 2006

Junior Member

It sort of helps but I will never know exactly how many records I am expecting back. So I would not be able to use coding that counts the number of lines.

Report message to a moderator

Re: How to write a SQLLDR File that ignores the trailing records [message #175124 is a reply to message #175119]

Wed, 31 May 2006 18:13

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Not sure what you are looking here.
The script will read the datafile and ignores the last line.
Whatever the count of lines may be. So as long as your records are not having a line feed, that would work.
It is also said
>>or you need to have a special record as trailer record and use a when clause.
So if know the magic word that would appear in last line, you can use the WHEN clause.

Report message to a moderator

Re: How to write a SQLLDR File that ignores the trailing records [message #175128 is a reply to message #175119]

Wed, 31 May 2006 21:13

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Mahesh provides a nice solution.

If his solution doesn't suffice, have you considered External tables?

Report message to a moderator

Previous Topic:	Where we need to use DIRECT = TRUE for direct path ?
Next Topic:	DBScripter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 00:37:44 CDT 2024