OraFAQ Forum: Server Utilities » When clause SQL Loader

Home » RDBMS Server » Server Utilities » When clause SQL Loader

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

When clause SQL Loader [message #185776]

Thu, 03 August 2006 09:51

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

I'm trying to use WHEN clause in a control file, I'm following the documentation but it doesn't work still.

This is my control file:

LOAD DATA
CHARACTERSET UTF8
INTO TABLE DM08_CONDICIONADA
WHEN (STATUS_DESC='A')
APPEND
REENABLE DISABLED_CONSTRAINTS
FIELDS
TERMINATED BY '|'

(
"ID_COND" POSITION (1) INTEGER EXTERNAL ,
"DESC_COND" CHAR,
"STATUS_DESC" CHAR
)

I have the follow error:

SQL*Loader-350: Syntax error at line 26.
Expecting "(", found keyword append.
APPEND
^

I have tried with
WHEN STATUS_DESC='A'

but the error is the same. Do you have any ideas why is that?? I'm working with oracle 9i.

´m using the documentation...

-->full_fieldname or pos_spec-->operator--> char_string or Blanks --->

but it doesn't tell me where the when clause goes... i'm probing too with the forum information but i don't know what happens

I'm desperate

Report message to a moderator

Re: When clause SQL Loader [message #185801 is a reply to message #185776]

Thu, 03 August 2006 12:17

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What happens if you substitute WHEN and APPEND?

INTO TABLE DM08_CONDICIONADA
APPEND
WHEN (STATUS_DESC='A')

Report message to a moderator

Re: When clause SQL Loader [message #185832 is a reply to message #185801]

Thu, 03 August 2006 16:09

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

with:

LOAD DATA
CHARACTERSET UTF8
INTO TABLE DM08_CONDICIONADA
APPEND
WHEN STATUS_DESC='A' --->or with (STATUS_DESC='A') neither
REENABLE DISABLED_CONSTRAINTS
FIELDSTERMINATED BY '|'

I have:

SQL*Loader-350: Syntax error at line 27.
Expecting "(", found keyword reenable.
REENABLE DISABLED_CONSTRAINTS

With:

LOAD DATA
CHARACTERSET UTF8
INTO TABLE DM08_CONDICIONADA
APPEND
REENABLE DISABLED_CONSTRAINTS
WHEN (STATUS_DESC ='A')

i have:

SQL*Loader-403: Referenced column STATUS_DESC not present in table DM08_CONDICIONADA.

Thanks for your time

Report message to a moderator

Re: When clause SQL Loader [message #185835 is a reply to message #185832]

Thu, 03 August 2006 16:25

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

No problem with my time, but what's wrong with your eyes?

SQL*Loader-403: Referenced column STATUS_DESC not present in table DM08_CONDICIONADA.

In English: you don't have STATUS_DESC column in DM08_CONDICIONADA table.

Report message to a moderator

Re: When clause SQL Loader [message #185838 is a reply to message #185835]

Thu, 03 August 2006 16:38

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

here is a desc of my table

SQL> desc dm08_condicionada;
Name Null? Type
----------------------------------------- -------- ----------------------------

ID_COND NOT NULL NUMBER(2)
DESC_COND VARCHAR2(10)
STATUS_DESC VARCHAR2(1)

thanks again

Report message to a moderator

Re: When clause SQL Loader [message #185843 is a reply to message #185838]

Thu, 03 August 2006 17:03

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Remove the quotes around your column names in the control file:

options (direct=true)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE DM08_CONDICIONADA
REENABLE DISABLED_CONSTRAINTS
WHEN STATUS_DESC='A'
FIELDS TERMINATED BY '|'
(
ID_CON 		POSITION (1) INTEGER EXTERNAL ,
DESC_COND 	CHAR,
STATUS_DESC 	CHAR
)

Report message to a moderator

Re: When clause SQL Loader [message #185848 is a reply to message #185843]

Thu, 03 August 2006 17:32

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

You are right!! thank you very much. but why is that??

I have 2 or 3 control files with quotations and they works with them.

Greetings

[Updated on: Thu, 03 August 2006 17:33]

Report message to a moderator

Re: When clause SQL Loader [message #185852 is a reply to message #185848]

Thu, 03 August 2006 17:39

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Well it appears that the WHEN clause needs to match the column name listed in the control file EXACTLY. For example, the following will also work with quotes:

options (direct=true)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE DM08_CONDICIONADA
REENABLE DISABLED_CONSTRAINTS
WHEN "STATUS_DESC"='A'
FIELDS TERMINATED BY '|'
(
"ID_CON" 	POSITION (1) INTEGER EXTERNAL ,
"DESC_COND" 	CHAR,
"STATUS_DESC" 	CHAR
)

[Updated on: Fri, 04 August 2006 08:16]

Report message to a moderator

Re: When clause SQL Loader [message #185948 is a reply to message #185852]

Fri, 04 August 2006 07:57

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

ok, it's a little detail but it's the difference between it works and not.

Thanks men.
Greetings
Alex.

Report message to a moderator

Previous Topic:	data migration
Next Topic:	Error import

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 06:11:56 CDT 2024