OraFAQ Forum: Server Administration » SQL Loader Question - Keeping blanks as blanks instead of nulls

Home » RDBMS Server » Server Administration » SQL Loader Question - Keeping blanks as blanks instead of nulls

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

SQL Loader Question - Keeping blanks as blanks instead of nulls [message #372046]

Fri, 05 January 2001 15:01

Mike Oakes
Messages: 25
Registered: December 2000

Junior Member

Hello,
I am loading data from an ascii text file into an oracle database using SQL Loader. The data in some of the columns is = ''(quote,quote). When I load this data into oracle it is converted to NULL. Is there a way to keep the value assigned to ''. I have set up a sql string to use nvl(col,'') but this doesn't seem to work. ANy help would greatly be appreciate.

Thanks

Report message to a moderator

Re: SQL Loader Question - Keeping blanks as blanks instead of nulls [message #372060 is a reply to message #372046]

Mon, 08 January 2001 13:27

Bala
Messages: 205
Registered: November 1999

Senior Member

Hi,

Check the syntex of the control file you are using
The ctl file should be something like this

load data
replace into table temp_table
FIELDS TERMINATED BY "~"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID,
Col1 "nvl(:col1, '"')",
col2,
col3
)

Bala

Report message to a moderator

Re: SQL Loader Question - Keeping blanks as blanks instead of nulls [message #372067 is a reply to message #372060]

Mon, 08 January 2001 16:54

Mike Oakes
Messages: 25
Registered: December 2000

Junior Member

Thanks for responding Bala,
I tried this solution and thought it was working but when I go into SQL plus and run a simple select statement such as

Select column1 from table1 where column1='';

It returns no rows for the query. It looks as though ORACLE cannot use the ''. Also if i just create a record and assign ''(quote,quote) into it and then do a select statement on ''(quote,quote) it doesn;t find it.

Can ORACLE handle '' as a valid data and store it the same way so that it can be retried the same way it was loaded?

Example
insert into table1 (column1,column2) values('TEST','');

select * from table1 where column2='';

This would not pull the record I just added.

Thanks for your help,
Mike O.

Report message to a moderator

Re: SQL Loader Question - Keeping blanks as blanks instead of nulls [message #372069 is a reply to message #372060]

Mon, 08 January 2001 18:03

Bala
Messages: 205
Registered: November 1999

Senior Member

Hi Mike
If you want to insert two single quotes ''
you have to put them inside two pair of singel quotes.
Like this

SQL> create table table1(cloumn1 varchar2(10), column2 varchar2(10));
table created.
SQL> insert into table1 (column1,column2) values('TEST',''''''); ---> totally six quotes.

1 row inserted.

SQL> select * from table1 where column2='''''''; --> again six single quotes
column1 column2
Test ''

If you insert only two single quotes like your example
SQL> insert into table1 (column1,column2) values('TEST','');

1 row inserted.

Then only null will be inserted for column2...
so you have to
SQL> select * from table1 where column2 is null;

column1 column2
Test

Bala.

Report message to a moderator

Previous Topic:	composite primary keys
Next Topic:	Trigger question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 00:33:21 CDT 2024