error while executing

Question

What is the difference between 2 and '2' in Oracle?

Different datatype ?

select 1 from tab union select '2' from tab;

error occurred

• Clive: Yes, different data types

Answer 1

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;

• 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.

Answer 2

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>