2010-02-14

Data types in a view

Is there actually a TIME data type in Oracle? What is the data type of null? Boolean is a number or is it?


SQL> create view v as
2 select null nul
3 , time '12:00:00' tim
4 , dbms_session.is_role_enabled('CREATE SESSION') boo
5 from dual
6 ;

View created.

SQL>
SQL> select column_name,data_type,data_length
2 from user_tab_columns
3 where table_name = 'V'
4 order by 1
5 ;

COLUMN_NAME DATA_TYPE DATA_LENGTH
------------ ------------ -----------
BOO NUMBER 22
NUL VARCHAR2 0
TIM TIME(9) 20

SQL>
SQL> select nul from v;

N
-


SQL>
SQL> select tim from v;

TIM
--------------------------------------------------------------
12:00:00,000000000

SQL>
SQL> select boo from v;
select boo from v
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type



null and time types from Laurent Schneider.

2010-02-12

Open position

It has been a while since Pirjo Salo wrote about job opportunities for database specialist in Finland. Be aware something to be available soon.

2010-02-08

Equality -comparing text

Should we use case clause or still use decode? Yet again someting to be aware. Seems like case clause is trimming before comparing. And the same with DB2 minus. Postgres does not trim.

Oracle


SQL> select case when 'a' = 'a ' then 'same' else 'different' end as test from dual;

TEST
---------
same

SQL> select 'a' from dual minus select 'a ' from dual;

'A
--
a

SQL> select decode('a','a ','same','different') testdecode from dual;

TESTDECOD
---------
different




DB2



db2 => select case when 'a' = 'a ' then 'same' else 'different' end as test from sysibm.sysdummy1

TEST
---------
same

1 record(s) selected.

db2 => select 'a' from sysibm.sysdummy1 minus select 'a ' from sysibm.sysdummy1

1
--

0 record(s) selected.



Postgres



postgres=# select case when 'a' = 'a ' then 'same' else 'different' end as test ;
test
-----------
different
(1 row)

postgres=# select 'a' as a except select 'a ' as a;
a
---
a
(1 row)



SQL Server



1> select case when 'a' = 'a ' then 'same' else 'different' end as test ;
2> go
test
----
same

(1 rows affected)
1>
2> select 'a'
3> except
4> select 'a '
5> go

--

(0 rows affected)

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.