OraFAQ Forum: Server Utilities » SQL*Loader problem

Home » RDBMS Server » Server Utilities » SQL*Loader problem

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

SQL*Loader problem [message #140470]

Tue, 04 October 2005 07:16

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

I am facing problem while loading data into multiple tables from same INPUT file through single control file using WHEN clause. In the first table "FPD_STG_Errors_Messages"
the data gets loaded correctly but for the other table "FPD_STG_LogDetails", it is inserting NULL into the log_Description field. The number of records are correctly loaded but the field value remains NULL.

If I put the <> condition first then it loads data into "FPD_STG_LogDetails" table and in other table the field description comes as NULL.

Where I am being wrong?

CTL File
Options ( Direct = True)
LOAD DATA
INFILE 'C:\Factory Planner Data\Log Files\fperr602.txt'
BADFILE 'C:\Factory Planner Data\Log Files\FPErr.bad'
DISCARDFILE 'C:\Factory Planner Data\Log Files\FPErr.dis'
TRUNCATE
INTO TABLE FPD_STG_Errors_Messages
WHEN (01:07) = 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)
INTO TABLE FPD_STG_LogDetails
WHEN (01:07) <> 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)

Data in file
START: read routing_data (NULL) (03:01:00)
END: read routing_data (NULL) (03:01:00): 0.000 sec
Warning: deleting mfg order 515763300_1-001 which is no longer pegged.
Warning: deleting mfg order 515885400_1-001 which is no longer pegged.
Warning: deleting mfg order 518833100_1-001 which is no longer pegged.
Warning: deleting mfg order 519245700_1-001 which is no longer pegged

Thanks in advance.

Regards
Himanshu Gupta

Report message to a moderator

Re: SQL*Loader problem [message #140646 is a reply to message #140470]

Wed, 05 October 2005 01:25

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

Adding POSITION(1) where shown should fix it.

Options ( Direct = True)
LOAD DATA
INFILE 'C:\Factory Planner Data\Log Files\fperr602.txt'
BADFILE 'C:\Factory Planner Data\Log Files\FPErr.bad'
DISCARDFILE 'C:\Factory Planner Data\Log Files\FPErr.dis'
TRUNCATE
INTO TABLE FPD_STG_Errors_Messages
WHEN (01:07) = 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)
INTO TABLE FPD_STG_LogDetails
WHEN (01:07) <> 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description POSITION(1) Char(4000),
LogFile_ID Constant '1'
)

Report message to a moderator

Re: SQL*Loader problem [message #140855 is a reply to message #140470]

Wed, 05 October 2005 23:01

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

Thanks a lot. It worked.

But I have one more question regarding CTL file. I want to use OR condition in WHEN clause while loading the data. Such as

WHEN (01:05) = 'START' OR (01:03) = 'END'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)
INTO TABLE FPD_STG_LogDetails
WHEN (01:05) != 'START' AND (01:03) != 'END'

(
Log_Index SEQUENCE(1,1),
Log_Description POSITION(1) Char(4000),
LogFile_ID Constant '1'
)

The AND clause works but the OR clause is not supported. Any syntax error or this is not supported? I have worked around it and splitted the OR condition into 2 when clause but just wanted to know whether the usage of 'OR' clause is allowed or not.

Thanks in advance.

Report message to a moderator

Re: SQL*Loader problem [message #141279 is a reply to message #140855]

Sat, 08 October 2005 01:47

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

OR is not supported, as you suspected.

Report message to a moderator

Previous Topic:	how to export only database information
Next Topic:	data base conversion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 13:21:49 CDT 2024