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.

No comments:

Post a Comment

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.