OraFAQ Forum: Server Utilities » Loading default value when exists null or blanks SQL Loader

Home » RDBMS Server » Server Utilities » Loading default value when exists null or blanks SQL Loader

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

Loading default value when exists null or blanks SQL Loader [message #188289]

Thu, 17 August 2006 17:36

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

Senior Member

Hi:

Are there any way with SQL Loader to load data by default in a specific field.

it's mean that if I have null or "", can i insert a defalut value for this field.

For example:

LOAD DATA
INFILE FILE1
into table A
APPEND

(
id integer external,
data1 boundfiller
data --and here insert a default value when exist a null or blank values??
)

Thanks to all for your answers
Alex

Report message to a moderator

Re: Loading default value when exists null or blanks SQL Loader [message #188293 is a reply to message #188289]

Thu, 17 August 2006 18:23

ebrian
Messages: 2794
Registered: April 2006

Senior Member

This should work for ya:

load data
infile file1
into table a
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl(:data,'default value')"
)

Report message to a moderator

Re: Loading default value when exists null or blanks SQL Loader [message #188455 is a reply to message #188293]

Fri, 18 August 2006 11:30

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

Senior Member

The value to insert is :data or :data1

load data
infile file1
into table a
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl(:data,'default value')"-- with this is not like apply a format to field data?? like a to_char or something similar??
)

and can i do something like this:

data "nvl(:data1||:data,:data1||'default value')"

*Concatenate the field data1 value plus default value or data field value??

Thanks ebrian
Alex.

Report message to a moderator

Re: Loading default value when exists null or blanks SQL Loader [message #188461 is a reply to message #188455]

Fri, 18 August 2006 12:37

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Is this what your requirements are:

load data
infile *
into table a
fields terminated by ','
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl(:data,:data1||'default value')"
)
BEGINDATA
1,Test data for 1,
2,Test data for 2,NOTNULL

SQL> select * from a;

        ID DATA
---------- -------------------------------
         1 Test data for 1default value
         2 NOTNULL

Report message to a moderator

Re: Loading default value when exists null or blanks SQL Loader [message #188480 is a reply to message #188461]

Fri, 18 August 2006 13:20

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

Senior Member

Its very similar only that i always want to insert the second value (data).

The result should be...

ID DATA
---------- -------------------------------
1 Test data for 1default value
2 Test data for 2NOTNULL

Thanks chief
Alex

Report message to a moderator

Re: Loading default value when exists null or blanks SQL Loader [message #188488 is a reply to message #188480]

Fri, 18 August 2006 13:46

ebrian
Messages: 2794
Registered: April 2006

Senior Member

load data
infile *
into table a
fields terminated by ','
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl2(:data,:data1||:data,:data1||'default value')"
)
BEGINDATA
1,Test data for 1,
2,Test data for 2,NOTNULL

SQL> select * from a;

        ID DATA
---------- --------------------------------------
         1 Test data for 1default value
         2 Test data for 2NOTNULL

Report message to a moderator

Re: Loading default value when exists null or blanks SQL Loader [message #188502 is a reply to message #188488]

Fri, 18 August 2006 15:36

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

Senior Member

thanks my sensei...

You are really a very nice guy.

Greetings
Alex

Report message to a moderator

Previous Topic:	PLS-00103: Encountered the symbol ";" when expecting one of the following:
Next Topic:	sqlldr how and where to execute

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 25 23:29:40 CST 2024