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'