Restore Points and Flashback Query Examples

Another intersting and useful new feature in Oracle 10g (release 2) is the ability to create named restore points. These are similar to savepoints but unlike savepoints are not limited to a particular transaction.

They are used in conjunction with the database flash recovery area to rollback all transactions on a table (or database wide) to the point in time when the restore point was created.

Restore points are created using the SQL command CREATE RESTORE POINT and by default are stored in the database for 7 days or until the maxinum number of restore points is reached at which time the oldest one is dropped automatically by Oracle. They can also be dropped manually using the SQL command DROP RESTORE POINT. To see what restore points have been created in the database you can query the system view v$restore_point.

To revert to a restore point it's simply a matter of issuing the command FLASHBACK TABLE TO .

Restore Points and Flashback Query Examples

Let's look at an example using the hr schema that is created in the sample
database.

First create a restore point. SQL>CREATE RESTORE POINT one;

Next, query the employees table so that we will know whether or not the changes have been undone.

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randall

Now change some data

SQL> UPDATE employees SET first_name='Randy'
2 WHERE employee_id=191;
SQL>COMMIT;

re-query just to be sure.

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randy

Next wait a couple of minutes before reverting to our restore point

SQL>FLASHBACK TABLE employees TO RESTORE POINT one;

Flashback complete

(N.B. row movement has to be enabled before a table can be flashed back. To do this just issue the SQL command 'ALTER TABLE employees ENABLE ROW MOVEMENT')

Re-query again

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randall

The change we made has been undone, let's see if we can redo it by flashing forwards in time.

SQL>FLASHBACK TABLE employees TO TIMESATMP(SYSTIMESTAMP - INTERVAL '2' MINUTE);

Flashback complete

Let's see what data we have in our Oracle database now.

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randy

Magnificent! We went back to the time before we'd made any changes then came forwards to the time after we'd made the change. Could be a great tool for playing tricks on your colleagues!

You can also use the Oracle SQL flashback commands and restore points to rewind the whole database - just replace table with database (the database must be mounted but not open for this).

There are two more things you can do with flashbacks:

1. you can see all the versions of the data in a table (or particular column) that existed between two timestamps or system change numbers (scn)
2. you can restore tables that have been dropped

Let's look at an example of each of these.

Querying versions of data

To do this we need to use the new flashback query clause in the Oracle SQL SELECT statement.

In this example we display all the versions that we have of employee #142s first name as well as the start and end tiime of each version (using the pseudo columns versions_starttime and versions_endtime). Notice that the results are stored in reverse chronological order.

SELECT versions_starttime,versions_endtime,first_name FROM employees VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE employee_id=143
/

VERSIONS_STARTTIME VERSIONS_ENDTIME FIRST_NAME
-------------------- -------------------- --------------------
19-MAY-09 16.13.44 sandy
19-MAY-09 16.13.23 19-MAY-09 16.13.44 Randy
19-MAY-09 16.13.02 19-MAY-09 16.13.23 Randall
19-MAY-09 16.13.02 Mandy

We can replace timestamp with SCN and/or use the pseudo columns versions_startscn and versions_endscn as well.

Retrieving dropped tables

This is achieved with an alternative clause of the SQL command FLASHBACK TABLE and the recycle bin introduced with Oracle 10g release 1.

sql>DROP TABLE employees; -- table goes into recycle bin
sql>FLASHBACK TABLE employees TO BEFORE DROP; -- table restored

One thing to note is that the table's extents are not released until the table is purged from the recycle bin which can be done manually and is done automatically by Oracle when more space is required in the database.

SQL FOR CALENDAR DAY OUTPUT

select b.*,
substr(to_char(to_date(a."1"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "1",
substr(to_char(to_date(a."2"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "2",
substr(to_char(to_date(a."3"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "3",
substr(to_char(to_date(a."4"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "4",
substr(to_char(to_date(a."5"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "5",
substr(to_char(to_date(a."6"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "6",
substr(to_char(to_date(a."7"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "7",
substr(to_char(to_date(a."8"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "8",
substr(to_char(to_date(a."9"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "9",
substr(to_char(to_date(a."10"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "10",
substr(to_char(to_date(a."11"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "11",
substr(to_char(to_date(a."12"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "12",
substr(to_char(to_date(a."13"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "13",
substr(to_char(to_date(a."14"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "14",
substr(to_char(to_date(a."15"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "15",
substr(to_char(to_date(a."16"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "16",
substr(to_char(to_date(a."17"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "17",
substr(to_char(to_date(a."18"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "18",
substr(to_char(to_date(a."19"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "19",
substr(to_char(to_date(a."20"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "20",
substr(to_char(to_date(a."21"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "21",
substr(to_char(to_date(a."22"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "22",
substr(to_char(to_date(a."23"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "23",
substr(to_char(to_date(a."24"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "24",
substr(to_char(to_date(a."25"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "25",
substr(to_char(to_date(a."26"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "26",
substr(to_char(to_date(a."27"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "27",
substr(to_char(to_date(a."28"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "28",
case when b.seq=2 then
case when &year/4=round(&year/4) then
substr(to_char(to_date(a."29"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end
else
substr(to_char(to_date(a."29"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
end "29",
case when b.seq<>2 then
substr(to_char(to_date(a."30"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end "30",
case when b.seq in (1,3,5,7,8,10,12) then
substr(to_char(to_date(a."30"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end "31"
from
(
Select
1 "1",
2 "2",
3 "3",
4 "4",
5 "5",
6 "6",
7 "7",
8 "8",
9 "9",
10 "10",
11 "11",
12 "12",
13 "13",
14 "14",
15 "15",
16 "16",
17 "17",
18 "18",
19 "19",
20 "20",
21 "21",
22 "22",
23 "23",
24 "24",
25 "25",
26 "26",
27 "27",
28 "28",
29 "29",
30 "30",
31 "31"
from dual
)
a,
(
select
rownum seq,
case when rownum=1 then 'January'
else
case when rownum=2 then 'February'
else
case when rownum=3 then 'March'
else
case when rownum=4 then 'April'
else
case when rownum=5 then 'May'
else
case when rownum=6 then 'June'
else
case when rownum=7 then 'July'
else
case when rownum=8 then 'August'
else
case when rownum=9 then 'September'
else
case when rownum=10 then 'October'
else
case when rownum=11 then 'November'
else
'December'
end
end
end
end
end
end
end
end
end
end
end
"Months"
from dual connect by level<=12
)
b

SQL FOR CALENDAR OUTPUT

SELECT TO_DATE ('2008-01', 'yyyy-mm') - 1 + LEVEL AS daterange
FROM DUAL
CONNECT BY LEVEL <= 366 ---------------------------------------------------------------------------------------------

select to_date('&date','MM-YYYY')-1 + level as DateRange
from dual
where (to_date('&date','MM-YYYY')-1+level) <= last_day(to_date('&Date','MM-YYYY')) connect by level<=31; ---------------------------------------------------------------------------------------------

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')) GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' )) ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week); ---------------------------------------------------------------------------------------------

with t as (
select to_date('200905','yyyymm') ym from dual)
SELECT * FROM (
SELECT MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '1', LEVEL)) SUN
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '2', LEVEL)) MON
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '3', LEVEL)) TUE
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '4', LEVEL)) WED
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '5', LEVEL)) THU
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '6', LEVEL)) FRI
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '7', LEVEL)) SAT
FROM T
CONNECT BY LEVEL <= LAST_DAY (YM) - YM + 1 GROUP BY TRUNC (YM + LEVEL, 'iw') ORDER BY 7) ---------------------------------------------------------------------------------------------

PL/SQL PACKAGES WITH VARIOUS OPTIONS

----------------------------------------------------------------
---CREATE PACKAGE
----------------------------------------------------------------

CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE add_test (col1_in NUMBER, col2_in CHAR);
PROCEDURE del_test (col1_in NUMBER);
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS

PROCEDURE add_test(col1_in IN NUMBER,col2_in IN CHAR) AS
BEGIN
INSERT INTO test VALUES (col1_in,col2_in);
END add_test;

PROCEDURE del_test(col1_in IN NUMBER) AS
BEGIN
DELETE FROM test WHERE col1_in = col1;
END del_test;

END test_pkg;
/

======================
ALTER PACKAGE test_pkg COMPILE;


======================

DROP PACKAGE Header AND BODY

DROP PACKAGE test_pkg;


======================

DROP PACKAGE BODY Only

DROP PACKAGE BODY test_pkg;

=================================
---PACKAGES: initialization
=================================

CREATE OR REPLACE PACKAGE init IS
g_var VARCHAR2(50);
FUNCTION dummy RETURN VARCHAR2;
END init_sect;
/

CREATE OR REPLACE PACKAGE BODY init IS
FUNCTION f_test1 RETURN VARCHAR2 IS
BEGIN
RETURN g_var;
END f_test1;
BEGIN
SELECT 'Harod Stassen'
INTO g_var
FROM dual;
END init;
/

=======================================
-- Packages can hold functions as well as procedures
=======================================

CREATE OR REPLACE PACKAGE test2 AS
FUNCTION GetUser RETURN VARCHAR2;
END test2;
/

CREATE OR REPLACE PACKAGE BODY test2 AS
FUNCTION getuser RETURN VARCHAR2 IS
vOSUser user_users.username%TYPE;
BEGIN
SELECT osuser
INTO vUser
FROM sys.v_$session
WHERE sid = (
SELECT sid
FROM sys.v_$mystat
WHERE ROWNUM = 1);
RETURN vUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Not Known';
END getuser;
END test2;
/


======================================
---This is a bare-bones package example with both the package specification and the package body.
======================================

CREATE OR REPLACE PACKAGE test1 AS
PROCEDURE get_table_names(number_in IN PLS_INTEGER);
END test1;
/

CREATE OR REPLACE PACKAGE BODY test1 AS
PROCEDURE get_table_names(number_in IN PLS_INTEGER) IS
tabname user_tables.table_name%TYPE;
BEGIN
SELECT table_name
INTO tabname
FROM user_tables
WHERE ROWNUM < style="font-weight: bold;">=================================================
---- PACKAGE OVERLOADING
=================================================
Packages can contain multiple iterations of procedures with the same name using different parameters. This is called 'overloading'.


CREATE OR REPLACE PACKAGE overload_pkgIS
PROCEDURE insby(namein VARCHAR2);
PROCEDURE insby(numbin PLS_INTEGER);
END overload_pkg;
/



CREATE OR REPLACE PACKAGE BODY overload_pkg IS
PROCEDURE local_proc(namein VARCHAR2, nameout OUT VARCHAR2) IS
BEGIN
nameout := TRANSLATE(namein,'AEIOUaeiou','EIOUAeioua');
END local_proc;

PROCEDURE test_proc (namein VARCHAR2) IS
y PLS_INTEGER;
BEGIN
SELECT MAX(seqno)+1
INTO y
FROM persons;

INSERT INTO persons
(seqno, name)
VALUES
(y, namein);
COMMIT;
END test_proc;

PROCEDURE test_proc (numbin PLS_INTEGER) IS
y VARCHAR2(30);
BEGIN
SELECT MAX(name)
INTO y
FROM persons;

local(y, y);

INSERT INTO persons
(seqno, name)
VALUES
(numbin, y);
COMMIT;
END test_proc;

END overload_pkg;
/



======================================
--PACKAGE WITH 2 PROCEDURES
=========================================
CREATE OR REPLACE PACKAGE DEPTPKG
AS
PROCEDURE ADDREC(DEPTNO NUMBER,
DNAME VARCHAR2,
LOC VARCHAR2);
PROCEDURE DELREC(DNO NUMBER);
END DEPTPKG;
/
CREATE OR REPLACE PACKAGE BODY DEPTPKG
AS
PROCEDURE ADDREC(DEPTNO NUMBER,
DNAME VARCHAR2,
LOC VARCHAR2) IS
BEGIN
INSERT INTO DEPT VALUES(DEPTNO, DNAME, LOC)
END ADDRES;

PROCEDURE DELREC(DNO NUMBER) IS
BEGIN
DELETE FROM DEPT WHERE DEPTNO=DNO;
END DELREC;
END DEPTPKG;
/

--- TO TEST THE ABOVE PACKAGE.

EXEC DEPTPKG.ADDREC(50,'HR','DELHI');

EXEC DEPTPKG.DELREC(50);

PL/SQL FUNCTIONS WITH VARIOS OPTIONS

------------------------------------------------------------
-Functions - debug
-------------------------------------------------------------

To examine a function in debug mode

DESC user_plsql_object_settings

SELECT name, TYPE, plsql_debug
FROM user_plsql_object_settings;

ALTER FUNCTION test1 COMPILE DEBUG;

------------------------------------------------------------
-Function without Parameter
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_easy RETURN VARCHAR2 IS
BEGIN
RETURN 'This is a simple function';
END fn_easy;
/

------------------------------------------------------------
-Function - IN Parameter
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_ssn (string_in IN VARCHAR2)
RETURN BOOLEAN IS
-- validating ###-##-#### format
incorrect EXCEPTION;
delim CHAR(1);
part1 NUMBER(3,0);
part2 NUMBER(2,0);
part3 NUMBER(4,0);
BEGIN
IF LENGTH(string_in) <> 11 THEN
RAISE incorrect;
END IF;

part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');

delim := SUBSTR(string_in,4,1);
IF delim <> '-' THEN
RAISE incorrect;
END IF;

part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');

delim := SUBSTR(string_in,7,1);
IF delim <> '-' THEN
RAISE incorrect;
END IF;

part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');

RETURN TRUE;
EXCEPTION
WHEN incorrect THEN
RETURN FALSE;
WHEN OTHERS THEN
RETURN FALSE;
END is_socsecno;
/

SET serveroutput ON

BEGIN
IF is_ssn('123-45-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123-A5-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123=45-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123-A5-67890') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/



-----------------------------------------------------------------
--FUNCTIONS: OUT parameters
-----------------------------------------------------------------

CREATE OR REPLACE FUNCTION out_fn (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'out param';
RETURN 'return param';
END out_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
outval VARCHAR2(20);
BEGIN
retval := out_fn(outval);
DBMS_OUTPUT.put_line(outval);
DBMS_OUTPUT.put_line(retval);
END;
/



-----------------------------------------------------------
-- FUNCTIONS: IN OUT parameter
----------------------------------------------------------
CREATE OR REPLACE FUNCTION inout_fn (outparm IN OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'Coming out';
RETURN 'return param';
END inout_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
ioval VARCHAR2(20) := 'Going in';
BEGIN
DBMS_OUTPUT.put_line('In: ' || ioval);
retval := inout_fn(ioval);
DBMS_OUTPUT.put_line('Out: ' || ioval);
DBMS_OUTPUT.put_line('Return: ' || retval);
END;
/

--------------------------------------------------------------------------------
--FUNCTION OUT PARAMTER
-------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION out_fn (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'out param';
RETURN 'return param';
END out_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
outval VARCHAR2(20);
BEGIN
retval := out_fn(outval);
DBMS_OUTPUT.put_line(outval);
DBMS_OUTPUT.put_line(retval);
END;
/
----------------------------------------------------------------
--Function to determine if a number is even:
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_even(num_in NUMBER) RETURN BOOLEAN IS
BEGIN
IF MOD(num_in, 2) = 0 THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END is_even;
/

-----------------------------------------------------------------------
-- Function to determine if a number is odd:
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_odd(num_in NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN MOD(num_in, 2) = 1;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END is_odd;
/


---------------------------------------------------------------------
-- Sample IF Statement Function:
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_price(pProd VARCHAR2)
RETURN PLS_INTEGER IS
tabPrice discounts.prodprice%TYPE;
tabDisc discounts.proddisc%TYPE;
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM discounts
WHERE prodname = pProd;

IF i <> 0 THEN
SELECT prodprice, proddisc
INTO tabPrice, tabDisc
FROM discounts
WHERE prodname = pProd;

RETURN tabPrice - (tabPrice * tabDisc/100);
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fn_price;



------------------------------------------------------------------
-- Function - time difference
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
time_diff(DATE_1 IN DATE, DATE_2 IN DATE)
RETURN NUMBER IS
NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5, 0);
NSECOND_2 NUMBER(5, 0);
BEGIN
-- Get Julian date number from
-- first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from
-- second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight
-- from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight
-- from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1)*86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

PL/SQL PROCEDUERS WITH VARIOUS COMBINATIONS

-------------------------------------------------------
-- Stored Procedure - No Parameters
-------------------------------------------------------
CREATE OR REPLACE PROCEDURE no_param IS
BEGIN
dbms_output.put_line('No Params');
END no_param;
/

set serveroutput on

exec no_param;


-------------------------------------------------------------
-- Stored Procedure - IN Parameter
-------------------------------------------------------------

CREATE OR REPLACE PROCEDURE in_param (mesg VARCHAR2) IS
BEGIN
dbms_output.put_line(mesg);
END in_param;
/

set serveroutput on

exec in_param('Single IN Parameter');


------------------------------------------------------------------
-- Stored Procedure - OUT Parameter
------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE out_param(mesg OUT VARCHAR2) IS
BEGIN
mesg := 'Single OUT Parameter';
END out_param;
/

set serveroutput on

DECLARE
s VARCHAR2(50);
BEGIN
out_param(s);
dbms_output.put_line(s);
END;
/

HOST VARIABLE TESTING THE ABOVE PROCEDURE

var x VARCHAR2(30)
exec out_param(:x)
print x

---------------------------------------------------------------------
-- Stored Procedure - IN OUT Parameter
---------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE inout_param (mesg IN OUT VARCHAR2) IS
BEGIN
mesg := mesg || ' an IN OUT Parameter';
END inout_param;
/

set serveroutput on

DECLARE
s VARCHAR2(50) := 'This procedure uses';
BEGIN
inout_param(s);
dbms_output.put_line(s);
END;

---------------------------------------------------------------------
-- Stored Procedure - Multiple Parameters
---------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE many_params (
mesg1 IN VARCHAR2,
mesg2 OUT VARCHAR2,
mesg3 IN OUT VARCHAR2) IS
BEGIN
mesg2 := mesg1 || 'Parameter As The OUT';
mesg3 := mesg3 || 'Returned';
END many_params;
/

set serveroutput on

DECLARE
iparm VARCHAR2(50) := 'This is the IN ';
oparm VARCHAR2(50);
ioparm VARCHAR2(50) := 'And This is the IN OUT ';
BEGIN
many_params(iparm, oparm, ioparm);
dbms_output.put_line(oparm || ' ' || ioparm);
END;
/

create or replace procedure testpr (n1 in number, n2 in number, tot out number, mul out number)
is
begin
tot := n1 + n2;
mul := n1 * n2;
end;

---- TO TEST THE ABOVE PROCEDURE
variable t1 number
variable t2 number
execute testpr(5,6, :t1,t2 )
print t



------------------------------------------------------------------------
-- Stored Procedure - Procedure Without Default
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE no_default(num_rows INTEGER) IS

BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < style="font-weight: bold;">------------------------------------------------------------------------
-- Stored Procedure - Procedure With Default
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE with_default (
num_rows PLS_INTEGER DEFAULT 20) IS
BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < style="font-weight: bold;">------------------------------------------------------------------------
-- Stored Procedure - Positional Notation
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE positional (
min_nr PLS_INTEGER DEFAULT 100,
max_nr PLS_INTEGER DEFAULT 1000) IS
BEGIN
FOR r IN (SELECT table_name FROM user_tables
WHERE num_rows BETWEEN min_nr AND max_nr)
LOOP
dbms_output.put_line(r.table_name);
END LOOP;
END positional;
/

set serveroutput on

exec positional;

exec positional(1);

exec positional(1000, 500000);


------------------------------------------------------------------------
-- Stored Procedure - Named Notation
------------------------------------------------------------------------

exec positional;

exec positional(min_nr => 1);

exec positional(max_nr => 500);

exec positional(max_nr => 5000);

exec positional(max_nr => 10000);

exec positional(max_nr => 50000);

exec positional(max_nr => 999999);


------------------------------------------------------------------------
-- Stored Procedure - Mixed Notation
------------------------------------------------------------------------

exec positional(10, max_nr => 1000);




---------------------------------------------------------------------------------------------------------------------------------------------
-- Dynamic SQL Procedure that Accepts Table Name and WHERE Clause - Execute immediate
---------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE employees_temp AS SELECT * FROM employees;

CREATE OR REPLACE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ' WHERE ' || condition;
v_table VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
IF condition IS NULL THEN where_clause := NULL; END IF;
EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/
BEGIN
delete_rows('employees_temp', 'employee_id = 111');
END;
/


-----------------------------------------------------------------------------------------------
--EXECUTE IMMEDIATE
------------------------------------------------------------------------------------------------
When to Use or Omit the Semicolon with Dynamic SQL

When building up a single SQL statement in a string, do not include any semicolon at the end.

When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. For example:

BEGIN
EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;';
END;
/

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/
----------------------------------------------------------------------------------
-- this procedure can drop any kind of database object
----------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
/


-----------------------------------------------------------------
--- Avoiding Deadlocks with Dynamic SQL
-----------------------------------------------------------------
In a few situations, executing a SQL data definition statement results in a deadlock. For example, the following procedure causes a deadlock because it attempts to drop itself. To avoid deadlocks, never try to ALTER or DROP a subprogram or package while you are still using it.

CREATE OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- deadlock!
END;
/

ADVANCE FUNCTION

BFilename Function

First, we need to create a directory object called exampleDir that points to /example/totn on the file server.

CREATE DIRECTORY exampleDir AS 'C:\TEMP\';

Then we can use the exampleDir directory object in the bfilename function as follows:

SELECT bfilename('exampleDir', 'totn_logo.jpg') FROM dual;


CASE - It has the functionality of an IF-THEN-ELSE statement.

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;


select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;

select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;

select
CASE
WHEN a < style="font-weight: bold;">COALSCE - function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

SELECT coalesce( address1, address2, address3 ) result FROM SCMSUPPLIERSITES


DECODE - Function has the functionality of an IF-THEN-ELSE statement.
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;


LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table.

select headerid,itemid, requesteddate,
lag (requesteddate,1) over (ORDER BY requesteddate) AS prev_order_date
from POSLINES
where itemid = 38780;


LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table

select headerid,itemid, requesteddate,
LEAD (requesteddate,1) over (ORDER BY requesteddate) AS NEXT_order_date
from POSLINES
where itemid = 38780;


LNNVL - unction is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value.

select * from STOCKITEMS where LNNVL(qty >= reorder_level);

Condition Evaluates To LNNVL Return Value
TRUE FALSE
FALSE TRUE
UNKNOWN TRUE

NULLIF

SELECT NULLIF(12, 12) FROM DUAL -- would return NULL
SELECT NULLIF(12, 13) FROM DUAL -- would return 12
SELECT NULLIF('apples', 'apples') FROM DUAL -- would return NULL
SELECT NULLIF('apples', 'oranges') FROM DUAL -- would return 'apples'
SELECT NULLIF(NULL, 12) FROM DUAL -- would return an ORA-00932 error because expr1 can not be the literal NULL


NVL Function lets you substitute a value when a null value is encountered.
select HEADERID, NVL(CUSTOMERORDERREFERENCE, 'not found') cust_order_ref
from POSHEADERS


NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

select HEADERID, NVL2(CUSTOMERORDERREFERENCE, 'AVAILABLE','not found') cust_order_ref from POSHEADERS


sys_context function can be used to retrieve information about the Oracle environment.

SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'OS_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USERID') FROM DUAL
SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'LANGUAGE') FROM DUAL
SELECT sys_context('USERENV', 'IP_ADDRESS') FROM DUAL
SELECT sys_context('USERENV', 'HOST') FROM DUAL
SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') FROM DUAL



uid function returns the id number for a user's session (the user who is currently logged in).
user function returns the user_id from the current Oracle session.

select uid,USER from dual;

SELECT userenv('TERMINAL') FROM DUAL -- would return FALSE
SELECT userenv('LANGUAGE') FROM DUAL -- would return 'AMERICAN_AMERICA.WE8DEC'

Mathematical Functions

ABS - function returns the absolute value of a number.
SELECT abs(-23) FROM DUAL --would return 23
SELECT abs(-23.6) FROM DUAL --would return 23.6
SELECT abs(-23.65) FROM DUAL --would return 23.65
SELECT abs(23.65) FROM DUAL --would return 23.65
SELECT abs(23.65 * -1) FROM DUAL --would return 23.65


ACOS - function returns the arc cosine of a number.
SELECT acos(1) FROM DUAL -- would return 0
SELECT acos(0) FROM DUAL -- would return 1.5707963267949
SELECT acos(-1) FROM DUAL -- would return 3.14159265358979

asin function returns the arc sine of a number.
SELECT asin(0.2) FROM DUAL -- would return 0.201357920790331
SELECT asin(0.35) FROM DUAL -- would return 0.35757110364551
SELECT asin(-0.15) FROM DUAL -- would return -0.150568272776686

atan function returns the arc tangent of a number.
SELECT atan(0.2) FROM DUAL -- would return 0.197395559849881
SELECT atan(0.35) FROM DUAL -- would return 0.336674819386727
SELECT atan(-0.15) FROM DUAL --would return -0.148889947609497


atan2 function returns the arc tangent of n and m.
SELECT atan2(0.2, 0.3) FROM DUAL -- would return 0.588002603547568
SELECT atan2(0.35, -0.15) FROM DUAL --would return 1.97568811307998
SELECT atan2(-0.3, -0.4) FROM DUAL --would return -2.49809154479651


Avg function returns the average value of an expression.
SELECT Avg(sal) as "Avg Salary" FROM emp WHERE sal > 25000;
SELECT Avg(DISTINCT sal) as "Avg Salary" FROM emp WHERE sal > 25000;

BIT_TO_NUM - function converts a bit vector to a number.
SELECT bin_to_num(1) FROM DUAL -- would return 1
SELECT bin_to_num(1,0) FROM DUAL --would return 2
SELECT bin_to_num(1,1) FROM DUAL --would return 3
SELECT bin_to_num(1,1,1,0) FROM DUAL --would return 14
SELECT bin_to_num(1,1,1,1) FROM DUAL --would return 15

CEIL - ceil function returns the smallest integer value that is greater than or equal to a number.

SELECT ceil(32.02) FROM DUAL --would return -33
SELECT ceil(32.65) FROM DUAL --would return 33.
SELECT ceil(32) FROM DUAL --would return 32.
SELECT ceil(-32.65) FROM DUAL --would return -32.
SELECT ceil(-32) FROM DUAL --would return -32


floor function returns the largest integer value that is equal to or less than a number.

SELECT floor(5.9) FROM DUAL -- would return 5
SELECT floor(34.29) FROM DUAL -- would return 34
SELECT floor(-5.9) FROM DUAL --would return -6



cos - function returns the cosine of a number.
SELECT cos(0.2) FROM DUAL -- would return 0.980066577841242
SELECT cos(0.35) FROM DUAL -- would return 0.939372712847379
SELECT cos(-3.15) FROM DUAL -- would return -0.999964658471342
SELECT cos(200) FROM DUAL -- would return 0.487187675007006

cosh -function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86

cosh - function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86


COUNT - function will only count those records in which the field in the brackets is NOT NULL

SELECT COUNT(*) as "Number of employees" FROM emp
SELECT COUNT(distinct dept) FROM emp

extract
select extract(YEAR FROM DATE '2003-08-22') from dual -- would return 2003
select extract(MONTH FROM DATE '2003-08-22') from dual -- would return 8
select extract(DAY FROM DATE '2003-08-22') from dual --would return 22


greatest function returns the greatest value in a list of expressions.

SELECT greatest(2, 5, 12, 3) FROM DUAL -- would return 12
SELECT greatest('2', '5', '12', '3') FROM DUAL -- would return '5'
SELECT greatest('apples', 'oranges', 'bananas') FROM DUAL --would return 'oranges'
SELECT greatest('apples', 'applis', 'applas') FROM DUAL --would return 'applis'

least function returns the smallest value in a list of expressions.

SELECT least(2, 5, 12, 3) FROM DUAL -- would return 2
SELECT least('2', '5', '12', '3') FROM DUAL --would return '12'
SELECT least('apples', 'oranges', 'bananas') FROM DUAL -- would return 'apples'
SELECT least('apples', 'applis', 'applas') FROM DUAL --would return 'applas'
SELECT least('apples', 'applis', 'applas', null) FROM DUAL -- would return NULL




LOG function returns the logarithm of n base m.
SELECT log(10, 20) FROM DUAL -- would return 1.30102999566398
SELECT log(2, 15) FROM DUAL -- would return 3.90689059560852
SELECT log(100, 1) FROM DUAL --would return 0


MAX function returns the maximum value of an expression.

SELECT MAX(SAL) FROM EMP


MIN function returns the minimum value of an expression.

SELECT MIN(salary) as "Lowest salary" FROM employees;


median function returns the median of an expression.

select median(salary) from employees where department = 'Marketing';


mod function returns the remainder of m divided by n.

SELECT mod(15, 4) FROM DUAL -- would return 3
SELECT mod(15, 0) FROM DUAL -- would return 15
SELECT mod(11.6, 2) FROM DUAL -- would return 1.6
SELECT mod(11.6, 2.1) FROM DUAL -- would return 1.1
SELECT mod(-15, 4) FROM DUAL -- would return -3
SELECT mod(-15, 0) FROM DUAL -- would return -15


power function returns m raised to the nth power.

select power(3, 2) from dual -- would return 9
select power(5, 3) from dual --would return 125
select power(-5, 3) from dual -- would return -125
select power(6.2, 3) from dual -- would return 238.328
select power(6.2, 3.5) from dual -- would return 593.431934277892



rank function returns the rank of a value in a group of values (view must create )

select customerid, ordertotalamount,
rank() OVER (PARTITION BY saleschannelid ORDER BY ordertotalamount) rank
from posheaders


remainder function returns the remainder of m divided by n.

select remainder(15, 6) from dual -- would return 3
select remainder(15, 5) from dual -- would return 0
select remainder(15, 4) from dual -- would return -1
select remainder(11.6, 2) from dual -- would return -0.4
select remainder(11.6, 2.1) from dual -- would return -1
select remainder(-15, 4) from dual -- would return 1


round function returns a number rounded to a certain number of decimal places.
select round(125.315) from dual -- would return 125
select round(125.315, 0) from dual -- would return 125
select round(125.315, 1) from dual -- would return 125.3
select round(125.315, 2) from dual -- would return 125.32
select round(125.315, 3) from dual -- would return 125.315
select round(-125.315, 2) from dual -- would return -125.32

select round(to_date ('22-AUG-03'),'YEAR') from dual -- would return '01-JAN-04'
select round(to_date ('22-AUG-03'),'Q') from dual --would return '01-OCT-03'
select round(to_date ('22-AUG-03'),'MONTH') from dual -- would return '01-SEP-03'
select round(to_date ('22-AUG-03'),'DDD') from dual -- would return '22-AUG-03'
select round(to_date ('22-AUG-03'),'DAY') from dual -- would return '24-AUG-03'


sin function returns the sine of n.

select sin(3) from dual -- would return 0.141120008059867
select sin(5.2) from dual -- would return -0.883454655720153
select sin(-5.2) from dual -- would return 0.883454655720153


sqrt function returns the square root of n.
select sqrt(9) from dual -- would return 3
select sqrt(37) from dual -- would return 6.08276253029822
select sqrt(5.617) from dual -- would return 2.37002109695251

SUM function returns the summed value of an expression.

SELECT SUM(salary) as "Total Salary" FROM employees WHERE salary > 25000;

tan function returns the tangent of n.
select tan(3) from dual -- would return -0.142546543074278
select tan(5.2) from dual -- would return -1.88564187751976
select tan(-5.2) from dual -- would return 1.88564187751976


trunc function returns a number truncated to a certain number of decimal places.

select trunc(125.815) from dual -- would return 125
select trunc(125.815, 0) from dual -- would return 125
select trunc(125.815, 1) from dual -- would return 125.8
select trunc(125.815, 2) from daul -- would return 125.81
select trunc(125.815, 3) from daul -- would return 125.815
select trunc(-125.815, 2) from daul -- would return -125.81
select trunc(125.815, -1) from dual -- would return 120
select trunc(125.815, -2) from dual -- would return 100
select trunc(125.815, -3) from dual -- would return 0


select trunc(to_date('22-AUG-03'), 'YEAR') from dual -- would return '01-JAN-03'
select trunc(to_date('22-AUG-03'), 'Q') from dual -- would return '01-JUL-03'
select trunc(to_date('22-AUG-03'), 'MONTH') from dual -- would return '01-AUG-03'
select trunc(to_date('22-AUG-03'), 'DDD') from dual -- would return '22-AUG-03'
select trunc(to_date('22-AUG-03'), 'DAY') from dual -- would return '17-AUG-03'

DATE FUNCTION

ADD_MONTH function returns a date plus n months.

SELECT add_months('01-Aug-03', 3) FROM DUAL -- would return '01-Nov-03'
SELECT add_months('01-Aug-03', -3) FROM DUAL -- would return '01-May-03'
SELECT add_months('21-Aug-03', -3) FROM DUAL -- would return '21-May-03'
SELECT add_months('31-Jan-03', 1) FROM DUAL -- would return '28-Feb-03'


current_date
function returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command.

select current_date from dual;

ALTER SESSION SET TIME_ZONE = '-7:0';

select current_date from dual;

ALTER SESSION SET TIME_ZONE = '5:30';


CURRENT_TIMESTAMP

select current_timestamp from dual;


from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.

select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual;
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual;


LAST_DAY

SELECT last_day(to_date('2003/03/15', 'yyyy/mm/dd')) FROM DUAL -- would return Mar 31, 2003
SELECT last_day(to_date('2003/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 28, 2003
SELECT last_day(to_date('2004/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 29, 2004



select sessiontimezone from dual;
select localtimestamp from dual;

months_between

SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return -2.41935483870968

SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return 3.58064516129032

SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 0

SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 2


new_time function returns a date in time zone1 to a date in time zone2.

SELECT new_time (to_date ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') FROM DUAL


NEXT_DAY function returns the first weekday that is greater than a date.
SELECT next_day('01-Aug-03', 'TUESDAY') FROM DUAL -- would return '05-Aug-03'
SELECT next_day('06-Aug-03', 'WEDNESDAY') FROM DUAL --would return '13-Aug-03'
SELECT next_day('06-Aug-03', 'SUNDAY') FROM DUAL --would return '10-Aug-03'


ROUND
SELECT round(to_date ('22-AUG-03'),'YEAR') FROM DUAL -- would return '01-JAN-04'
SELECT round(to_date ('22-AUG-03'),'Q') FROM DUAL --would return '01-OCT-03'
SELECT round(to_date ('22-AUG-03'),'MONTH') FROM DUAL --would return '01-SEP-03'
SELECT round(to_date ('22-AUG-03'),'DDD') FROM DUAL --would return '22-AUG-03'
SELECT round(to_date ('22-AUG-03'),'DAY') FROM DUAL --would return '24-AUG-03'


SELECT SYSDATE FROM DUAL

select systimestamp from dual;

to_char
SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'

to_date
SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.

trunc
SELECT trunc(to_date('22-AUG-03'), 'YEAR') FROM DUAL -- would return '01-JAN-03'
SELECT trunc(to_date('22-AUG-03'), 'Q') FROM DUAL -- would return '01-JUL-03'
SELECT trunc(to_date('22-AUG-03'), 'MONTH') FROM DUAL -- would return '01-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DDD') FROM DUAL -- would return '22-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DAY') FROM DUAL -- would return '17-AUG-03'

CHARACTER & STRING FUNCTIONS

ascii

SELECT ascii('t') FROM DUAL -- would return 116.
SELECT ascii('T') FROM DUAL -- would return 84.
SELECT ascii('T2') FROM DUAL -- would also return 84.

chr
select chr(116) from dual -- would return 't'.
select chr(84) from dual -- would return 'T'.


UNISTR
Convert String To The National Character Set (either UTF8 or UTF16)
Unistring Value Resulting character
unistr('\0300') grave accent ( ` )
unistr('\0301') acute accent ( ´ )
unistr('\0302') circumflex ( ^ )
unistr('\0303') tilde ( ~ )
unistr('\0308') umlaut ( ¨ )


compose function returns a Unicode string.

select compose('o' || unistr('\0308') ) from dual -- would return ö
select compose('a' || unistr('\0302') ) from dual -- would return â
select compose('e' || unistr('\0301') ) from dual -- would return é


concat

select concat('Tech on', ' the Net') from dual -- would return 'Tech on the Net'.
select concat('a', 'b') from dual -- would return 'ab'.

|| Operator

select 'Tech on' || ' the Net' from dual -- would return 'Tech on the Net'.
select 'a' || 'b' || 'c' || 'd' from dual -- would return 'abcd'.


dump function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression.

select dump('Tech') from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 10) from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 16) from dual -- would return 'Typ=96 Len=4: 54,65,63,68'
select dump('Tech', 1016) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'
select dump('Tech', 1017) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'

Value Explanation
8 octal notation
10 decimal notation
16 hexadecimal notation
17 single characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name


initcap

select initcap('tech on the net') from dual -- would return 'Tech On The Net'
select initcap('GEORGE BURNS') from dual -- would return 'George Burns'

upper

select upper('Tech on the Net') from dual -- would return 'TECH ON THE NET'
select upper('george burns 123 ') from dual -- would return 'GEORGE BURNS 123

lower

select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123 '


instr

select instr('Tech on the net', 'e') from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 1) from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 2) from dual -- would return 11; the second occurrence of 'e'
select instr('Tech on the net', 'e', 1, 3) from dual -- would return 14; the third occurrence of 'e'


length

select length(NULL) from dual -- would return NULL.
select length('') from dual -- would return NULL.
select length('Tech on the Net') from dual -- would return 15.
select length('Tech on the Net ') from dual -- would return 16.

lower

select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123

lpad function pads the left-side of a string with a specific set of characters.

select lpad('tech', 7) from dual -- would return ' tech'
select lpad('tech', 2) from dual -- would return 'te'
select lpad('tech', 8, '0') from dual-- would return '0000tech'
select lpad('tech on the net', 15, 'z') from dual --would return 'tech on the net'
select lpad('tech on the net', 16, 'z') from dual -- would return 'ztech on the net'

rpad function pads the right-side of a string with a specific set of characters.

select rpad('tech', 7) from dual -- would return 'tech '
select rpad('tech', 2) from dual -- would return 'te'
select rpad('tech', 8, '0') from dual -- would return 'tech0000'
select rpad('tech on the net', 15, 'z') from dual -- would return 'tech on the net'
select rpad('tech on the net', 16, 'z') from dual -- would return 'tech on the netz'


trim

select trim(' tech ') from dual -- would return 'tech'
select trim(' ' from ' tech ') from dual -- would return 'tech'
select trim(leading '0' from '000123') from dual -- would return '123'
select trim(trailing '1' from 'Tech1') from dual -- would return 'Tech'
select trim(both '1' from '123Tech111') from dual -- would return '23Tech'

ltrim

select ltrim(' tech') from dual -- would return 'tech'
select ltrim(' tech', ' ') from dual -- would return 'tech'
select ltrim('000123', '0') from dual -- would return '123'
select ltrim('123123Tech', '123') from dual -- would return 'Tech'
select ltrim('123123Tech123', '123') from dual -- would return 'Tech123'
select ltrim('xyxzyyyTech', 'xyz') from dual -- would return 'Tech'
select ltrim('6372Tech', '0123456789') from dual -- would return 'Tech'

rtrim

select rtrim('tech ') from dual -- would return 'tech'
select rtrim('tech ', ' ') from dual -- would return 'tech'
select rtrim('123000', '0') from dual -- would return '123'
select rtrim('Tech123123', '123') from dual -- would return 'Tech'
select rtrim('123Tech123', '123') from dual -- would return '123Tech'
select rtrim('Techxyxzyyy', 'xyz') from dual -- would return 'Tech'
select rtrim('Tech6372', '0123456789') from dual -- would return 'Tech'


replace function replaces a sequence of characters in a string with another set of characters.

select replace('123123tech', '123') from dual -- would return 'tech'
select replace('123tech123', '123') from dual -- would return 'tech'
select replace('222tech', '2', '3') from dual -- would return '333tech'
select replace('0000123', '0') from dual -- would return '123'
select replace('0000123', '0', ' ') from dual -- would return ' 123'

translate function replaces a sequence of characters in a string with another set of characters. . However, it replaces a single character at a time

select translate('1tech23', '123', '456') from dual -- would return '4tech56'
select translate('222tech', '2ec', '3it') from dual -- would return '333tith'

substr

select substr('This is a test', 6, 2) from dual -- would return 'is'
select substr('This is a test', 6) from dual -- would return 'is a test'
select substr('TechOnTheNet', 1, 4) from dual -- would return 'Tech'
select substr('TechOnTheNet', -3, 3) from dual -- would return 'Net'
select substr('TechOnTheNet', -6, 3) from dual -- would return 'The'
select substr('TechOnTheNet', -8, 2) from dual -- would return 'On'


CASE

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;

select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;


TO_DSINTERVAL

SELECT SYSDATE, SYSDATE+ TO_DSINTERVAL('10 00:00:00') FROM DUAL

CONVERSION FUNCTIONS

CAST - function converts one datatype to another.

SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;

SELECT 1 + CAST(3.14 * 0.152 AS NUMBER(10,7)) FLOATING FROM dual;


from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.

select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual --11-Sep-05 01.50.42.000000 AM +05:00
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual --11-Sep-05 01.50.42.000000 AM -07:00


hextoraw function converts a hexadecimal value into a raw value.

select hextoraw('45D') from dual -- would return '045D' as a raw value
select hextoraw('7E') from dual -- would return '7E' as a raw value
select hextoraw('9FA') from dual -- would return '7E' as a raw value

rawtohex
SELECT rawtohex('AB') FROM DUAL -- would return '4142' if run as an SQL function and 'AB' if run as a PLSQL function.
SELECT rawtohex('7E') FROM DUAL -- would return '3745' if run as an SQL function and '7E' as a PLSQL function.

to_char

SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'

to_date

SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.

LOB functions
select to_clob(nclob_column) from suppliers;
select to_nclob(lob_column) from suppliers;
select to_lob(long_column) from suppliers;

to_number function converts a string to a number.

select to_number('1210.76', '99999.99') from dual -- would return the number 1210.73
select to_number('546', '999') from dual -- would return the number 546
select to_number('23', '99') from dual -- would return the number 23

to_timestamp
select to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

select to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.


to_timestamp_tz

select to_timestamp_tz('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

select to_timestamp_tz('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

to_yminterval

select sysdate+to_yminterval('03-11') from dual -- would return 3 years 11 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('01-05') from dual -- would return 1 year 5 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('00-01') from dual -- would return 0 years 1 month as an INTERVAL YEAR TO MONTH type