2011-04-13

Date variable in sqlplus

Having a huge query including several temporal joins and sum over time and several date type binds. I want to execute that query using sqlplus.


SQL> variable til date
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]


Using binds in sqlplus by Tanel Poder and askTom discussing missing date variable support in sqlplus. Putting those together.


SQL> variable fro varchar2(10)
SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro < sysdate;

1
----------
1

SQL> print fro

FRO
--------------------------------
13.04.2011



Be aware about possible changes in execution plan caused by datatype conversions. Read the askTom discussion.

Changing NLS_DATE_FORMAT


SQL> alter session set nls_territory=america;

Session altered.

SQL> select 1 from dual where :fro < sysdate;
select 1 from dual where :fro < sysdate
*
ERROR at line 1:
ORA-01843: not a valid month


SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro < sysdate;

1
----------
1

SQL> print fro

FRO
--------------------------------
13-APR-11


And yet another aspect about date formats


SQL> select 1 from dual where :fro = trunc(sysdate);

1
----------
1

SQL> alter session set nls_date_format='yyyymmdd hh24mi';

Session altered.

SQL> exec select sysdate into :fro from dual;
BEGIN select sysdate into :fro from dual; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


SQL> variable fro varchar2(13)
SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro = trunc(sysdate);

no rows selected

SQL> print fro

FRO
--------------------------------
20110413 0902

2011-04-01

11.2 XE beta and SQL Developer 3

11.2 XE beta available.

And seems like not a april fool Tanel Poder describing features.

Documentation available. And a dedicated discussion forum.

SQL Deveoper 3 beta days seems to be over

Descending index is function based

Descendin index is implemented as function based index in Oracle. This is documented and discussed.
Nulls may lead to a surprise with a desc unique index.


SQL> create table a(a varchar2(2 char), b varchar2(2 char) );

Table created.

SQL>
SQL> select table_name,column_name,data_type,data_length from user_tab_cols where table_name = 'A';

TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ --------------------------------------
A A VARCHAR2
A B VARCHAR2

SQL>
SQL> create unique index a_idx on a (a,b desc);

Index created.

SQL>
SQL> select table_name,column_name,data_type,data_length from user_tab_cols where table_name = 'A';

TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ --------------------------------------
A A VARCHAR2
A B VARCHAR2
A SYS_NC00003$ RAW

SQL>
SQL> select index_type from user_indexes where index_name = 'A_IDX';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL

SQL>
SQL> select * from user_ind_columns where index_name = 'A_IDX';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
A_IDX A
A
1 8 2 ASC

A_IDX A
SYS_NC00003$
2 13 0 DESC


SQL>
SQL> select * from USER_IND_EXPRESSIONS where index_name = 'A_IDX';

INDEX_NAME TABLE_NAME COLUMN_EXPRESSION
------------------------------ ------------------------------ --------------------------------------
A_IDX A "B"

SQL>
SQL> insert into a values(null,null);

1 row created.

SQL>
SQL> insert into a values(null,null);
insert into a values(null,null)
*
ERROR at line 1:
ORA-00001: unique constraint (KIOS.A_IDX) violated


SQL>
SQL> drop index a_idx;

Index dropped.

SQL>
SQL> create unique index a_idx on a (a,b);

Index created.

SQL>
SQL> insert into a values(null,null);

1 row created.

SQL>
SQL> insert into a values(null,null);

1 row created.

Installing

Just to remember. Have to try next time when installing a new computer.
http://ninite.com

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.