Topic: SQL*Plus and PL/SQL >> Small doubt about Loader/control file --Its Urgent
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Small doubt about Loader/control file --Its Urgent |
| prasad |
Posted: Feb 22, 2008 04:23:05 AM |
Total Post: 35
Joined: Jan, 2007
|
I want to load the raw data into a table through SQL*Loader file. The raw data is not fixed length data, It is comma separated.
While loading the data into a table, I want to check whether a feild is blank or not. If a particular field is BLANK then i do not want to laod that data into the table.
I am stuck with the WHEN clause in the control file. (when NAME!=BLANKS)
Can anybody hel me out please...? Its urgent.
See Code:
load data
infile '/data8/vtcht/LLC.TXT'
into table vtcht_llc_wk
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
when NAME!=BLANKS
(FN_PREFIX char"(:FN_PREFIX)",
FILE_NO char"(:FILE_NO)",
STATUS char"upper(:STATUS)",
TLC_EXPIRE char"to_char(to_date(:TLC_EXPIRE,'mm/dd/yyyy hh24:mi:ss'),'YYYYMMDD')"
)
Thanks in advance
Prasad Kshirsagar |
|
|
 Mohammed Taj |
| Posted: Feb 23, 2008 12:05:42 AM |  |
|
Total Post: 746
Joined: Jul, 2007
|
use "when is not NULL" instead of blank.
|
|
|
|
|
| Muhammed El Hedi |
| Posted: Feb 29, 2008 10:19:40 AM | |
|
Total Post: 2
Joined: Feb, 2008
|
Try this code:
load data
infile '/data8/vtcht/LLC.TXT'
into table vtcht_llc_wk
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(FN_PREFIX char"(:FN_PREFIX)" NULLIF FN_PREFIX=BLANKS,
FILE_NO char"(:FILE_NO)" NULLIF FILE_NO=BLANKS,
STATUS char"upper(:STATUS)" NULLIF STATUS=BLANKS,
TLC_EXPIRE char"to_char(to_date(:TLC_EXPIRE,'mm/dd/yyyy hh24:mi:ss'),'YYYYMMDD')" NULLIF TLC_EXPIRE=BLANKS
)
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
