Home » RDBMS Server » Server Utilities » When clause SQL Loader
When clause SQL Loader [message #185776] Thu, 03 August 2006 09:51 Go to next message
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
Re: When clause SQL Loader [message #185801 is a reply to message #185776] Thu, 03 August 2006 12:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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')
Re: When clause SQL Loader [message #185832 is a reply to message #185801] Thu, 03 August 2006 16:09 Go to previous messageGo to next message
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

Re: When clause SQL Loader [message #185835 is a reply to message #185832] Thu, 03 August 2006 16:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: When clause SQL Loader [message #185838 is a reply to message #185835] Thu, 03 August 2006 16:38 Go to previous messageGo to next message
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
Re: When clause SQL Loader [message #185843 is a reply to message #185838] Thu, 03 August 2006 17:03 Go to previous messageGo to next message
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
)
Re: When clause SQL Loader [message #185848 is a reply to message #185843] Thu, 03 August 2006 17:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: data migration
Next Topic: Error import
Goto Forum:
  


Current Time: Sun Dec 11 02:40:44 CST 2016

Total time taken to generate the page: 0.29965 seconds