INTERVAL conversion functions

There are 4 convertions functions that deal with INTERVAL datatypes, as listed below.
The two first converts strings, the later two converts numbers. All functions returns data of datatype INTERVAL.

TO_DSINTERVAL(sql_format)

* Converts a string into a value of the INTERVAL DAY TO SECOND datatype
* sql_format is a character string in the required format, which is 'DAYS HH24:MI:SS.FF'
* For example, the string '2 8:30:15.444' would equal 2 days, 8 hours, 30 minutes, 15.444 seconds.

Output: A value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT TO_DSINTERVAL(INTERVAL '4' HOUR) "HOUR"
FROM DUAL;

HOUR
+00 04:00:00.000000

TO_YMINTERVAL('y-m')
* Converts y and m into the years and months in a format recognized by the INTERVAL YEAR TO MONTH datatype
* y and m are numbers within a string

Output: A value of the INTERVAL YEAR TO MONTH datatype

Example:
SELECT TO_YMINTERVAL(INTERVAL '36' MONTH) "MONTH"
FROM DUAL;

MONTH
+03-00

NUMTOYMINTERVAL(n, u)

* Converts a number into a value that represents the u unit amount of time
n = number
u = 'YEAR' or 'MONTH'
* Output: a value of the INTERVAL YEAR TO MONTH datatype

Example:

SELECT NUMTOYMINTERVAL(4, 'MONTH') "MONTH"
FROM DUAL;

MONTH
+00-04

NUMTODSINTERVAL(n, u)
Converts a number into a value that represents the u unit amount of time
n = number
u = 'DAY', 'HOUR', 'MINUTE', 'SECOND'
Output: a value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT NUMTODSINTERVAL(2,'MINUTE') "MINUTE"
FROM DUAL;

MINUTE
+00 00:02:00.000000

SYSTIMESTAMP and SYSDATE VS. CURRENT_TIMESTAMP and CURRENT_DAY

Most DBAs I've spoken to are familiar with the function SYSDATE, but there are some other functions, too, that may prove to be useful, even for DBAs that aren't directly involved in developing applications.
For example, when querying the data dictionary, it can certainly be an advantage to be familiar with some other built-in date functions that comes with the Oracle database.

The two first functions reflect the datetime settings of the operating system on which the database runs:

* The SYSTIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE datatype. It includes fractional seconds and time zone.
* the SYSDATE function returns a value of the DATE datatype.It includes timestamp, but not fractional seconds, nor time stamp.

The three functions below reflects the settings of your session:

* The CURRENT_DATE fuction returns a value of the DATE datatype within the session time zone
* The CURRENT_TIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE data type within the session time zone
* The LOCALTIMESTAMP function returns a value of the TIMESTAMP data type, within the session time zone

Sitting in Oslo and querying a database with dbtimezone set to +2:00:

select dbtimezone from dual;

DBTIME
+02:00

I get the following default output when I execute the queries at 11:45:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual

   
SYSDATE
   
SYSTIMESTAMP
   
CURRENT_DATE
   
CURRENTTIMESTAMP
   
LOCALTIMESTAMP
   
13.07.2016 11:45:52
   
13.07.2016 11.45.52,597707 +02:00 
   
13.07.2016 11:45:52 
 13.07.2016 11.45.52,59771 +02:00 
  
 13.07.2016 11.45.52,598

Now I will change my session time zone:

alter session set time_zone='America/New_York';

Session altered.

Execute the functions again, and the difference between the function becomes apparent:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual;

SYSDATE	SYSTIMESTAMP	CURRENT_DATE	CURRENTTIMESTAMP	LOCALTIMESTAMP
13.07.2016 11:49:15	13.07.2016 11.49.15,381888 +02:00	13.07.2016 05:49:15	13.07.2016 05.49.15,38189 AMERICA/NEW_YORK	13.07.2016 05.49.15,382

Current_date now returns the date and time as it would be in New York
Current_timestamp does the same, but adds the region name as the time zone indicator
Localtimestamp returns the timestamp as it would be in New York

The precision in the current_timestamp and localtimestamp (5 and 3, respectively) are set to override the default precision of 6 for fractional seconds.

Overview of role privileges

The following UNION can be handy to get an overview of what system privileges, object privileges and other roles a specific role may contain:

Select  Role || ' contains role:' "role privilege type", Granted_Role "privilege"   From Role_Role_Privs Where Role='ONLINE_USERS'
Union
Select Role || ' contains system priv:', Privilege      From Role_Sys_Privs  Where Role='ONLINE_USERS'
Union
Select Role || ' contains object priv:',  Privilege || ' ON ' || owner || '.' || table_name From Role_Tab_Privs Where Role='ONLINE_USERS'
order by 1;

Output:

role privilege type	privilege
ONLINE_USERS contains object priv:	UPDATE ON SCOTT.EMP
ONLINE_USERS contains role:	RESOURCE
ONLINE_USERS contains system priv:	UPDATE ANY TABLE

How does Oracle decide which roles should be enabled in a session?

Oracle uses the concept of default roles to decide whether or not a role should be enabled when a user establishes a session.

CREATE USER ERIC
IDENTIFIED BY pass1w0rd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

ALTER USER ERIC QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO ERIC; 

Oracle states in its documentation

"When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles."

So we should have two default roles enabled for our user ERIC, confirmed below:

SQL> connect eric/pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
RESOURCE

Let's create a new role:

CREATE ROLE online_users NOT IDENTIFIED;

Role created.

Because the user ERIC was created with ALL roles enabled by default, all subsequent sessions established by ERIC will now have access to the role online_users:

GRANT online_users to ERIC; 

Grant succeeded.

connect eric/Pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
RESOURCE
ONLINE_USERS

If you want to prevent this behavior, you can alter the user with a limited default setting:

ALTER USER ERIC DEFAULT ROLE CONNECT;

User altered.

connect eric/Pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT

User ERIC must now explicitly enable the required roles from within his session:

set role connect, online_users;

Role set.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
ONLINE_USERS

These features can be incorporated into a security-oriented application setup, where specific people should only be granted specific privileges through database roles.

A workaround for ORA-02287: sequence number not allowed here when using GROUP BY in a subquery

When you use GROUP BY in the subquery of an INSERT statement, Oracle will throw

ORA-02287: sequence number not allowed here 

if you try to reference a sequence's NEXTVAL pseudocolumn directly in the insert statement, like this:

INSERT INTO mytable
        (
        entry_id,   
        order_date, 
        sum(quantity)
        )
        MYSEQ.NEXTVAL, <-- INCORRECT
        SELECT  
            TRUNC(SYSDATE),
            SUM(quantity)
        FROM    orders
        WHERE   
        GROUP BY order_date
        ORDER BY 2 ASC, 3 DESC;

Solution is to create a trigger that will fire on INSERT statements, before a new row is inserted. Note that I am overriding the default correlation name NEW with NewID, by using the REFERENCING clause of the "CREATE TRIGGER" command:

CREATE OR REPLACE TRIGGER MYTRG
BEFORE INSERT ON mytable REFERENCING NEW as NewID
    FOR EACH ROW
        BEGIN
            SELECT myseq.NEXTVAL
            INTO :NewID.ENTRY_ID
            FROM DUAL;
        END;

Change your insert statement slightly:

INSERT INTO mytable
        (
        order_date, 
        sum(quantity)
        )
        SELECT  
            TRUNC(SYSDATE),
            SUM(quantity)
        FROM    orders
        WHERE   
        GROUP BY order_date
        ORDER BY 2 ASC, 3 DESC;