One Stop Shopping for Date/Timestamp Queries

Assumptions

[1] Under this work, ALL_OBJECTS view will be used, as it usually has many rows.
[2] New queries will be added to address new requirements.This post will grow with time.
[3] Reader should have basic knowledge of built-in SQL functions.

[4] Following are not the only ways to solve the questions.

Queries

1) Given a date, Display n subsequent days including days name ?

[Solution]

Given date='28-AUG-01' and n=8

SQL> select to_char(SUB_DATE,'Day') Day,SUB_DATE SUBSEQUENT_DATE from
(select to_date('28-AUG-01') + rownum-1 SUB_DATE
from ALL_OBJECTS where rownum<=8)


DAY SUBSEQUENT_DATE
--------- ---------
Tuesday 28-AUG-01
Wednesday 29-AUG-01
Thursday 30-AUG-01
Friday 31-AUG-01
Saturday 01-SEP-01
Sunday 02-SEP-01
Monday 03-SEP-01
Tuesday 04-SEP-01

8 rows selected.

SQL>

2) Given two dates, Provide days between them including days name ?

[Solution]

Given dates='28-JAN-01','05-FEB-01'

SQL> select
to_char(BETWEEN_DATES,'Day') Day, BETWEEN_DATES DATES from
(
select to_date('28-JAN-01')+rownum-1 BETWEEN_DATES from ALL_OBJECTS
where rownum<=(to_date('05-FEB-01')-to_date('28-JAN-01')+1)
)
/

DAY DATES
--------- ---------
Sunday 28-JAN-01
Monday 29-JAN-01
Tuesday 30-JAN-01
Wednesday 31-JAN-01
Thursday 01-FEB-01
Friday 02-FEB-01
Saturday 03-FEB-01
Sunday 04-FEB-01
Monday 05-FEB-01

9 rows selected.

SQL>

3) How many sundays came in the month of december 2006?

[Solution]

SQL> select count(*) sundays from
(
select to_char(to_date('01-DEC-06')+rownum-1,'Day') MONTH_DAYS
from ALL_OBJECTS
where rownum<=(last_day(to_date('01-DEC-06'))-to_date('01-DEC-06')+1)
)
where trim(MONTH_DAYS)='Sunday'
/

SUNDAYS
----------
5

SQL>

4) Fetch the data from a table for the current month and the next two months.

[Solution]

SQL> desc xxx;
Name Null? Type
----------------------------------------- -------- ----------------------------

DATE1 DATE

SQL> select * from xxx;
DATE1
---------
15-MAR-07
13-MAR-07
13-APR-07
29-MAY-07
29-JUN-07
29-JAN-07

SQL> select sysdate from dual;

SYSDATE
----------
27-MAR-07

SQL> select * from xxx
where date1
between trunc(sysdate,'mm') and
trunc(add_months(sysdate,3),'mm')-1;

DATE1
---------
15-MAR-07
13-MAR-07
13-APR-07
29-MAY-07

SQL>

New queries will come very soon...

A practical approach to understand Oracle number datatype

Oracle Database Version: 9iR2

Oracle number datatype houses fixed and floating point numbers. A number datatype can be decked by specifying p=precision (total number of digits) and s=scale(number of digits to the right of the decimal point).

If specifying, precision can go up to 38 digits.


SQL> create table table1(col1 number(39,2));
create table table1(col1 number(39,2))
*
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)



If precision is not specified, Number data type can house a value up to 9.99E125.

Max Value

SQL> create table table1(col1 number);

Table created.

SQL> insert into table1 values(9999999999999999999999999999
9999999999999999999999999999999999999999999999999999
999999999999999999999999999999999999999999999);

1 row created.

SQL> select length('999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999') Length from dual;

LENGTH
----------
125


Beyond Max Value

Wrote file afiedt.buf

1* insert into table1 values(999999999999999999999999999999
9999999999999999999999999999999999999999999999999999
99999999999999999999999999999999999999999991)
SQL> /
insert into table1 values(99999999999999999999999999999999
999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999991)
*
ERROR at line 1:
ORA-01426: numeric overflow



1* select length('99999999999999999999999999999999999999
999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999991') Beyond Length from
dual
SQL> /

BEYOND LENGTH
----------
126

SQL>


If specifying, scale can be in the range (-84 to 127).


SQL> create table table1(col1 number(7,-88));
create table table1(col1 number(7,-88))
*
ERROR at line 1:
ORA-01728: numeric scale specifier is out of range (-84 to 127)


SQL> create table table1(col1 number(7,127));

Table created.

SQL> create table table1(col1 number(7,128));
create table table1(col1 number(7,128))
*
ERROR at line 1:
ORA-01728: numeric scale specifier is out of range (-84 to 127)



Let us take p=3 and s=1. Out of 3 digits 1 digit can come to the left of decimal point. Thus only 2 digits can come to the right of decimal point.

SQL> create table table1(col1 number(3,1));

Table created.

If you specify a value .26 on right side, then Oracle will round it to .3 . You can insert a two digit positive value. Oracle takes (p-s)=(3-1) = 2 digits on right side of decimal point. You cannot store 126.55555 value in this case.

SQL> insert into table1 values(12);

1 row created.

SQL> insert into table1 values(12.1);

1 row created.

SQL> insert into table1 values(12.1555); // .1555 will be rounded to .2

1 row created.

SQL> select * from table1;

COL1
----------
12
12.1
12.2

SQL> insert into table1 values(126.1555);
insert into table1 values(126.1555)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

SQL> insert into table1 values(100); // Only two digits allowed before decimal
insert into table1 values(100)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

Let us take now p=7 and s=-9. If negative scale value is specified then digits on right side of the decimal point will be rounded. Anything after the decimal will be ignored.

SQL> create table table1(col1 number(7,-9));

Table created.

SQL> insert into table1 values(1234567.12); // 7 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0

SQL> insert into table1 values(12345678.12); // 8 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0
0

SQL> insert into table1 values(123456781.12); // 9 digits

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0

SQL> insert into table1 values(1234567812.12); // 10 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0
0
1000000000

SQL> insert into table1 values(1734567812.12); // 10 digits on left, should round to 2000000000

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0
0
1000000000
2000000000

6 rows selected.

SQL>


How many digits can be there in this case ? (p-s) = (7-(-9))=16 digits can be stored on right side.


SQL> insert into table1 values(17345678125.12);

1 row created.

SQL> insert into table1 values(173456781255555.12);

1 row created.

SQL> insert into table1 values(1734567812555555.12);

1 row created.

SQL> select length('1734567812555555') from dual;

LENGTH('1734567812555555')
--------------------------
16

SQL>

Increasing one more digit on left side will lead to a complain as follows.

SQL> insert into table1 values(17345678125555555.12);
insert into table1 values(17345678125555555.12)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


As an another example, consider p=3 and s=-1.

SQL> create table table1(col1 number(3,-1));

Table created.

SQL> insert into table1 values(1.2);

1 row created.

SQL> insert into table1 values(99.2);

1 row created.

SQL> select * from table1;

COL1
----------
0
100

SQL> insert into table1 values(999.2);

1 row created.

SQL> select * from table1;

COL1
----------
0
100
1000

Can it house a value of 9999.2 ? No, because as scale is negative, it will try to round 9999 value. After rounding result will be 10000, which is a five digit number. It cannot be housed, because total digits can be p-s=3-(-1)=4.

SQL> insert into table1 values(9999.2);
insert into table1 values(9999.2)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


SQL> insert into table1 values(9999);
insert into table1 values(9999)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


SQL> insert into table1 values(9998);
insert into table1 values(9998)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


Can it house a value of 9498 ? Yes as follows.


SQL> insert into table1 values(9498); //As value is below 9500 no need to round

1 row created.

Conclusion: Various examples have been taken into consideration for understanding number data type. This practice will give more insight for beginners as they always confuse with precision and scale attributes of number data types. I welcome any comment on this.

Restoring Oracle database on different Windows Platforms

Purpose of this post is to answer the question of restoring oracle database on different windows platform. Three oracle databases have been created on Windows 2000 Advanced Server (Oracle SID=Win2000),Windows 2003 Enterprise Server (Oracle SID=Win2003) and Windows XP (Oracle SID=WinXP). On all the boxes Oracle 10gR2 have been installed.

Steps of restoring the database from Windows 2000 (Source Server) to Windows 2003 (Destination Server) are enumerated as follows...

1) Take the cold backup of the database present in source server. Also backup spfile.

2) Shift these files to the destination server. In source server the database files are present in c:\oracle\product\10.2.0\oradata\Win2000. In this experiment, these files will be restored in in D:\oracle\product\10.2.0\oradata\Win2000. So renaming of files is required here.

3) Create the default folders bdump,cdump,adump,dpdump and pfile in d:\oracle\product\10.2.0\admin\Win2000 folder.

4) Create the windows service for oracle. Specifying startmode as manual, will not start the instance.

D:\Documents and Settings\Administrator>oradim -new -sid Win2000 -startmode manual

5) CONTROL_FILES parameter is not modifiable through ALTER SYSTEM SET command. Create pfile from this spfile.

D:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:20:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.
SQL> create pfile='d:\oracle\product\10.2.0\db_1\database\initWin2000.ora' from spf
ile;

File created.

6) Change the values of CONTROL_FILES,DB_RECOVERY_FILE_DEST,BACKGROUND_DUMP_DEST and other location specific parameters, pointing to D drive.

7) Again connect to SQL and create the spfile.

D:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:20:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

8) Execute startup command (instance will come throught spfile) in nomount stage and check
the value of CONTROL_FILES parameter.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 2945024 bytes
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\ORACLE\PRODUCT\10.2.0\ORADA
TA\WIN2000\CONTROL01.CTL, D:\ORAC
LE\PRODUCT\10.2.0\ORADATA\WIN2000
\CONTROL02.CTL, D:\ORACLE\PROD
UCT\10.2.0\ORADATA\WIN2000\CONTRO
L03.CTL

9) Mount the database.

SQL> alter database mount;

Database altered.

10) Rename the datafiles and online redo log files. One example shown below. If restoring in the same location then this step can be skipped.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\EXAMPLE01.DBF


SQL> ed
Wrote file afiedt.buf

1 alter database rename file
2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF' to
3* 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF'
SQL> /

Database altered.



SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO01.LOG

SQL> alter database rename file
2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG'
3 to
4 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG';

Database altered.


11) Double check the new file names/locations and start the database.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\EXAMPLE01.DBF

SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO02.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO01.LOG

SQL> alter database open;

Database altered.

12) Take the hot/cold backup of the database.

In the same fashion, database from Windows 2000 box have been restored to Windows XP box. In addition to that following restore combinations have been tried.

Database from Windows XP box to Windows 2003 box.
Database from Windows XP box to Windows 2000 box.
Database from Windows 2003 box to Windows XP box.
Database from Windows 2003 box to Windows 2000 box.

In all the above combinations successful restoration of database has been achieved.

Conclusion:- Thus among 2000, XP and 2003 Windows, databases can be restored. Experiments have been done for oracle 10gR2 version. This will help to prepare test machines regardless of specified Windows Platforms.

PL/SQL block abnormal termination

In OTN discussion forum it has been asked when a PL/SQL block got terminated abnormally what will happen?

couple of experiments have been done on this issue to understand it clearly.

C:\> sqlplus scott/tiger

SQL> select * from dept;


DEPTNO DNAME LOC
---------- -------------- -------------
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Table dept1 has the structure as that of dept.

SQL> ed
Wrote file afiedt.buf

1 declare
2 begin
3 insert into dept1 select * from dept;
4 dbms_lock.sleep(20); -- sleeping for 20 secs
5 insert into dept1 select * from dept;
6 dbms_lock.sleep(20); -- sleeping for 20 secs
7 insert into dept1 select * from dept;
8* end;
SQL>/

While the above code was running the session has been terminated abnormally. Immediately the v$session view was checked as a DBA. It has shown active session for user SCOTT. When the block execution finished v$session result in no session for SCOTT. Select * from dept1 result in no rows for the table dept1 when fetched from a new session for SCOTT.

Again the same experiment was done with the following PL/SQL block..

SQL> ed
Wrote file afiedt.buf

1 declare
2 begin
3 insert into dept1 select * from dept;
4 dbms_lock.sleep(20);
5 insert into dept1 select * from dept;
6 dbms_lock.sleep(20);
7 insert into dept1 select * from dept;
8 commit;
9* end;
SQL>/


when fetched from dept1 the following output was found.

SQL> select * from dept1;

DEPTNO DNAME LOC
---------- -------------- -------------
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT LONDON

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

15 rows selected.

SQL>

Conclusion: PL/SQL block will continue to execute upon abnormal termination. Only committed execution will be preserved and uncommitted execution will be purged out.

Behavior of Boolean Datatype in SQL and PL/SQL

Oracle provides boolean datatype for PL/SQL. Boolean datatype can house three literals i.e, TRUE, FALSE and NULL. The main rationale for coming with such datatype was to evaluate comparison expressions.

The value of boolean datatype variable cannot be printed using DBMS_OUTPUT.PUT_LINE


SQL> declare
2 b_variable boolean;
3 begin
4 b_variable:=true;
5 dbms_output.put_line(b_variable);
6 end;
7 /
dbms_output.put_line(b_variable);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

A simple if statement can act as a remedy for the above code.

SQL> declare
2 b_variable boolean;
3 begin
4 b_variable:=true;
5 if (b_variable) then
6 dbms_output.put_line('true');
7 else
8 dbms_output.put_line('false');
9 end if;
10 end;
11 /
true

PL/SQL procedure successfully completed.

SQL>


Functions returning boolean data cannot be used in select statements.

SQL> create or replace function is_it_equal_2_oracle(instring varchar2)
2 return boolean is
3 begin
4 if (instring='oracle') then
5 return true;
6 else
7 return false;
8 end if;
9 end;
10 /

Function created.

SQL> select is_it_equal_2_oracle('sqlserver') from dual;
select is_it_equal_2_oracle('sqlserver') from dual
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type


The same function can be used in PL/SQL as follows.


1 declare
2 database char(10) :='sqlserver';
3 begin
4 if is_it_equal_2_oracle(database) then
5 dbms_output.put_line('Yes it is oracle');
6 else
7 dbms_output.put_line('No it is not equal to oracle');
8 end if;
9* end;
SQL> /
No it is not equal to oracle

PL/SQL procedure successfully completed.

SQL>

People might confuse with the following SQL statement.

SQL> select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from dual;

As in SQL there is no boolean datatype, true will not be identified by SQL. Output is as follows.

SQL> select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from d
ual;
select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from dual
*
ERROR at line 1:
ORA-00904: "TRUE": invalid identifier


Conclusion: Boolean datatype is exclusive for PL/SQL. A care should be taken while doing implementation.

Power of Oracle's NVL and Decode function

Suppose I have the following data in a table as shown below. R_ID is related to ID.
A
.....B
.....C
.....D
E
.....F
.....G
.....H
I
.....J
.....K
.....L
.....M

SQL> select * from example order by ID;

ID D R_ID
---------- - ----------
1 A
2 E
3 I
4 B 1
5 C 1
6 D 1
7 F 2
8 G 2
9 H 2
10 J 3
11 K 3
12 L 3
13 M 3

It has been asked to order by both ID and R_ID. The following output is required.


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

There are may ways to achieve this from which two are as follows.

1) Decode function

SQL> select id,details D,decode(r,id,null,r) R_ID
2 from
3 (select id,details,decode(r_id,null,id,r_id) r from example)
4 order by r,id


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

In the inner query null value of R_ID is being replaced by corresponding ID column value. In the outer query reverse is being done. If ID and r are equal then null is assigned.

2) NVL function


SQL> select * from example
2 order by nvl(r_id,id),id


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

This method is very simple. The null values of R_ID are being replaced by ID value and then these are used for ordering purpose.


Second method is efficient than first one as nvl is being called only once while in the first method decode function is being called two times. I found this ordering to be interesting.

References:
1) Decode function
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions33a.htm#1017439
2) NVL function
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions88a.htm#91645