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.

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.

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)