What is the difference between 2 and '2' in Oracle?
Different datatype ?
select 1 from tab union select '2' from tab;
error occurred
-
Yes, different data typesClive– Clive2015-11-08 12:38:44 +00:00Commented Nov 8, 2015 at 12:38
Add a comment
|
2 Answers
Yes, different data types; 2 is an integer, where '2' is a string literal, so they are in different data types, thats why you are getting an error.
When you use UNION the data types should be matching; they should be either integer or string literals like this:
select 1 from tab union select 2 from tab;
3 Comments
Gordon Linoff
Intriguiingly, Oracle documentation (docs.oracle.com/cd/B19306_01/server.102/b14200/…) is inconsistent on whether the literal
1 is a number or integer. The documentation has examples of both for strings of digits with no decimal point. (This detail doesn't really change the answer.)
Mahmoud Gamal
@GordonLinoff - Aha, thanks for the note. I am not aware of Oracle terminology.
Alex Poole
Bit odd that it distinguishes at all here as integer isn't really a seperate type. The bit about using text literals is even stranger as it's recommending implicit conversion. Even if the NLS setting has comma as the decimal seperator a number literal still always uses a period.
bit more info to @Mahmoud Gamal answer see the simple test
create table t1 as select 1 as col1 from dual;
create table t2 as select '1' as col1 from dual;
and then in SQL Plus we can see that t1.col1 is numeric, but t2.col1 is CHAR(1)
Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0
Connected as ***
SQL> desc t1;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
COL1 NUMBER Y
SQL> desc t2;
Name Type Nullable Default Comments
---- ------- -------- ------- --------
COL1 CHAR(1) Y
SQL>
Comments
