| Navi |
Posted: Jul 15, 2007 05:27:55 AM |
Total Post: 60
Joined: Jun, 2007
|
Hi,
I have some doubts regarding NULL values. Can any one help me in clearing my doubts.
I have created a table:-
create table suppliers
(
supplier_id number,
supplier_name varchar2(10)
);
Next, I will insert two records into this table.
insert into suppliers (supplier_id, supplier_name )
values ( 10565, null );
insert into suppliers (supplier_id, supplier_name )
values ( 10566, '' );
Q 1) Can any one tell which is the null value, whether it is supplier_name having 10565 or 10506.
2) Empty String is null value
or
NULL is null value
3) Because of this Null value doubts i have another doubt . How many null values can be their in a column if that column is defined as UNIQUE?
Regards,
Naveen
|
|
|
 gps |
| Posted: Jul 15, 2007 11:05:46 PM |  |
|
Total Post: 38
Joined: May, 2006
|
Hi Naveen, Here are the answers to your questions.
Ans 1. Both NULL and '' (Quotes without space in between) are equivalent. So both yours insertions will have null values in supplier_name columns.
Ans 2. There is no restriction of having NULL values in column defined as UNIQUE. So you can have as many NULL values as there are rows in the table.
Gurwinder
|
|
|
|
|
| chinna |
| Posted: Jul 16, 2007 03:01:28 AM | |
|
Total Post: 28
Joined: May, 2007
|
hi gps,
as u said NULL and '' are equal. it's a false statement.
and we can insert multiple NULL values within a UNIQUE column ,
but we can't insert multiple '' values.
if u not agree with this jst try it once.
byeee
regards
srawan
|
|
|
|
|
| gps |
| Posted: Jul 16, 2007 05:11:15 AM | |
|
Total Post: 38
Joined: May, 2006
|
I am sure of what I have written. I am just writing few statements for your reference. Execute this and confirm it yourself...
create table test (id varchar2(10) unique)
/
insert into test values (null)
/
insert into test values (null)
/
insert into test values (null)
/
insert into test values (null)
/
insert into test values (null)
/
commit
/
insert into test values ('')
/
insert into test values ('')
/
insert into test values ('')
/
insert into test values ('')
/
insert into test values ('')
/
commit
/
select rownum, id from test
/
Gurwinder
|
|
|
|
|
| chinna |
| Posted: Jul 16, 2007 05:23:29 AM | |
|
Total Post: 28
Joined: May, 2007
|
hi gps,
i got it now.
thanks.
take care
byeeeee
|
|
|
|
|
| Dipu |
| Posted: Jul 19, 2007 02:22:31 AM | |
|
Total Post: 8
Joined: Nov, 2006
|
Hi gps/chinna,
I think Chinna is somehow correct.
Because null and ''(blank) are not same at all.
Null means it is an undefined,unauthorized,un-recognized data whereas '' represents blank only.
Suppose you create a table say x like this way:
Create table x(a varchar2(100));
Now you inserted two records into it like this way:
Insert into x
Values(null);
Insert into x
Values(' ');
Commit;
Now if you now execute the follwing statement , it will return only one values:
Select nvl(a,'A') from x;
it will return:
nvl(a,'A')
----------------------------------
A
Means nvl can only convert the null values not the blank values.
|
|
|
|
|
| gps |
| Posted: Jul 20, 2007 02:32:47 AM | |
|
Total Post: 38
Joined: May, 2006
|
THERE IS CERTAINLY A DIFFERENCE BETWEEN ' ' (QUOTES WITH BLANK SPACE IN BETWEEN) and '' (QUOTES WITHOUT BLANK SPACE). NOTE AGAIN ''(WITHOUT BLANK) IS EQUIVALENT TO NULL. FOR FURTHER CLARRIFICATION OF YOUR DOUBTS BETTER YOU CONTACT ORACLE CORPORATION.
GURWINDER
|
|
|
|
|
| Dipu |
| Posted: Jul 23, 2007 07:33:04 AM | |
|
Total Post: 8
Joined: Nov, 2006
|
Thanks GPS. I got your point. Actually messed up between ''(without blank) and ' '(with blank).
Thanks a lot.
Regards,
Dipu.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
