Decode Function in Oracle

It is possible, though quite complicated, to write a pipelined table function that returns a variable structure. Your pipeline table function could take the two arguments and use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime. You can then use that in subsequent SQL statements as if it was a table, i.e.

SELECT *
  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

• Dynamic SQL Pivoting
• The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide

DATEis the Oracle datetime data type for storing a datetime value with a precision of one second. The function TO_DATE converts a character type (VARCHAR2,CHAR,...) to the DATE type. The first string is the string to convert, the secons string tell the convert function which format is used to code the date in the first string. So the result of TO_DATE('2014-04-18 10:33', 'YYYY-MM-DD HH24:MI') is the DATE equivalent to '2014-04-18 10:33:00'. The seconds are set to 0.

The function SYSDATE returns a DATE value so it must not be converted further. The column test_time is of data type DATE, too, it also must not be converted further. To get the DATEdifference you have only to calculate test_time-SYSDATE This difference of two DATE values gives the number of days. Therefore you have to multiply the above value by 1440 (=24*60, the number of minutes per day). You end up with

select (test_time - sysdate)*24*60
  from test;

This value is negative if test_time is lies in the past. If you want an integer value then round the result

select round((test_time - sysdate)*24*60,0)
  from test;