Schema level Comparison with TOAD

Go to DATABASE à COMPARE à SCHEMAS

Select the Source Schema

Add the Target Schema

Click Compare

Sync script will be generated.

Issue: (If Toad is not DBA Version)

By making a scheme level comparison between two schemes in toad. it 
gives the difference with a Note, but  unable to copy the same in the 
notepad and execute it in the SQL PLUS. 

The message is 

"Script is view only because you do not have access to dba module functions 
in Toad" 

Solution :

Press

Ctrl + A ( Select All)

Ctrl + F ( Search )

Complete text will be copied to the test box on search dialog box.

Ctrl + C ( copy )

Ctrl + v ( paste ) in the editor. 

TRUNC ON DATE FIELD

TRUNC ( DATAFIELD,'<format>')

Default is 'dd' -- Truncate at the date level , meaning start of the day (Time: 00:00:00)


1. 'dd'  (First hour of the day) 

-- Start of the day

Example:
Select TRUNC(SYSDATE,'dd') from dual;

2. 'mm' (First day of the month) + (First hour of the day)

-- Start of the month

Example:
Select TRUNC(SYSDATE,'mm') from dual;


3. 'yyyy' (First month of the year With First Date) + (First day of the month) + (First hour of the day)

-- Start of the year

Example:
Select TRUNC(SYSDATE,'yyyy') from dual;


4. 'hh' ( Minutes part will be truncated)


 -- Start of the hour

Example:
Select to_char(TRUNC(SYSDATE,'hh'),'mm/dd/yyyy hh24:mi:ss') from dual;

5. 'mi'  (Seconds part will be truncated) 

-- Start of the minute

Example:
Select to_char(TRUNC(SYSDATE,'mi'),'mm/dd/yyyy hh24:mi:ss') from dual;

DBMS_APPLICATION_INFO (Package)

The DBMS_APPLICATION_INFO package allows programs to add information to the V$SESSION to make tracking of session activities more accurate.

Select module, action from v$session;


procedure set_module(module_name varchar2, action_name varchar2);

--  Sets the name of the module that is currently running to a new module.

procedure set_action(action_name varchar2);

-- Sets the name of the current action within the current module.

procedure read_module(module_name out varchar2, action_name out varchar2);

--  Reads the values of the module and action fields of the current session.

procedure set_client_info(client_info varchar2);

-- Sets the client info field of the v$session.

procedure read_client_info(client_info out varchar2);

-- Reads the value of the client_info field of the current session.


Example:

BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => 'add_order',
                                   action_name => 'insert into orders');
 
  -- Do insert into table.
END;
/


BEGIN
  DBMS_APPLICATION_INFO.set_action(action_name => 'insert into orders');
  DBMS_APPLICATION_INFO.set_client_info(client_info => 'Issued by Web Client');
 
  -- Do insert into ORDERS table.
END;
/

SQL DEVELOPER TOOL (FREE)

Oracle has released the free SQL DEVELOPER TOOL & can be download from oracle website.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b31695/intro.htm

Installation:

SQL DEVELOPER installs by simple "unzipping" the file downloaded from oracle.

Note: Not an windows installed program.


Run: Execute the sqldeveloper.exe in the unzipped folder.


1. Connection:

We can open multiple connections in a single instance.

Click New connection & then enter the connection name, host,port,sid,username,password.


2. After connecting to a schema (user):

we see three subpanels

1. Expandable list of objects associated with the user.
2. SQL Worksheet.
3. Results


Snippets: Drag & drop or code completion.


DBMS_OUTPUT : Will be displayed in DBMS_OUTPUT tab.


SQL Developer allows:

Setting and removal of breakpoints.

Complie for Debug : Must be executed to make an object available for debugging.

Once compiled for Debugging,When executed in Debug mode the code will stop at the break point.


ShortCut Keys:

F5 -- Execute as a script.

F9 -- Execute statement.

F10 -- Explain Plan

F11 -- Commit

F12 -- Rollback

Ctrl + F7 -- Formating.

F6 -- Auto Trace.

F7 -- Step by Step execution.

Shift + f4 -- Describe object in popup.

Ctrl + D -- Clear SQL WorkSheet.

Execute immediate - Dynamic SQL

NATIVE DYNAMIC SQL (NDS) - Execute immediate


DDL's must be executed via dynamic sql within the pl/sql.


Syntax:

EXECUTE IMMEDIATE <SQL_string>
   [INTO {define_variable[, define_variable]... | record}]
   [USING [IN | OUT | IN OUT] bind_argument
       [, [IN | OUT | IN OUT] bind_argument]...];



Note: <SQL_string> can be an SQL statement or PL/SQL block.



If <SQL_string> is a pl/sql block, then it ends with a semi colon(;).

If <SQL_string> is a sql statement(DML or DDL), then don't end with a semi colon(;).

Note: DDL statement via execute immediate will issue an implicit commit.


Method 1:

No queries; just DDL statements and UPDATEs, INSERTs, or DELETEs, which have no bind variables.

Example:
Begin
  Execute immediate ('create table test_table(rno number,name varchar2(100))');
End;
/




Method 2: (USING clause)

No queries; just UPDATEs, INSERTs, or DELETEs, with a fixed number of bind variables.

Example:


Begin

  Execute immediate 'update scott.emp set sal=sal*:percent where deptno=:deptno' using 0.2,20;
  Dbms_output.put_line(sql%rowcount);

End;
/



Method 3: 

A.  Single row data (INTO & USING clauses)

  Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving a single row of data


Example:

Declare
  p_deptno number;
  p_sal number;
  p_hike number;
Begin
  Execute immediate 'select deptno,max(sal),:hike_pct * 100 from scott.emp
                               where deptno=:deptno
                               group by deptno'
                         into p_deptno,p_sal,p_hike
                         using 0.2,20;
   Dbms_output.put_line('Deptno:'|| p_deptno || ' Max salary:'|| p_sal || ' Hike perct:' || p_hike);

End;
/


B.  Multiple row data  ( BULK COLLECT INTO & USING clauses) / (OPEN FOR)

  Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving or more rows of data.

Example:

Declare
  Type emp_tab_col is table of scott.emp%rowtype;
  my_emp_tab emp_tab_col:=emp_tab_col();
Begin
 Execute immediate 'select * from scott.emp
                   where deptno=:deptno'
 Bulk collect into my_emp_tab
 using 20;

 For i in my_emp_tab.first..my_emp_tab.last
 loop
  dbms_output.put_line(my_emp_tab(i).empno || ':' || my_emp_tab(i).sal || ':' || my_emp_tab(i).deptno);
 end loop;
End;
/


Method 4:


A statement in which the numbers of columns selected (for a query) or the number of bind variables set are not known until runtime.

(will be executing a dynamic PL/SQL block, rather than a SQL string).

Example:


CREATE OR REPLACE PROCEDURE
get_rows (
   table_in   IN   VARCHAR2,
   where_in   IN   VARCHAR2
                )
IS
BEGIN
   EXECUTE IMMEDIATE
      'DECLARE
          l_row ' || table_in || '%ROWTYPE;
       BEGIN
          SELECT * INTO l_row
            FROM ' || table_in || ' WHERE ' || where_in || ';
       END;';
END get_rows;



BIND VARIABLES:

We can bind into our SQL statement only those expressions (literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. We cannot bind in the names of schema elements (tables, columns, etc.) or entire chunks of the SQL statement (such as the WHERE clause).

For those parts of our string, we must use concatenation.

IN / OUT / IN OUT : (Argument types)

When we are executing a dynamic query, all bind arguments must be of mode IN,except when we are taking advantage of the RETURNING clause & dynamic PL/SQL.

Example:

PROCEDURE analyze_new_technology (
   tech_name IN VARCHAR2,
   analysis_year IN INTEGER,
   number_of_adherents IN OUT NUMBER,
   projected_revenue OUT NUMBER,
   ) ... ;


DECLARE
   devoted_followers NUMBER;
   est_revenue NUMBER;
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN
          analyze_new_technology (:p1, :p2, :p3, :p4);       END;'
   USING 'oracle', 2010, devoted_followers, est_revenue;
END;


NOTE: OUT & IN OUT variable type and procedure parameters must be of the same data type.


Duplicate Placeholders:

When we are executing a dynamic SQL string (DML or DDLin other words, the string does not end in a semicolon), we must supply an argument for each placeholder, even if there are duplicates.



Example:

CREATE OR REPLACE PROCEDURE updnumval (
   col_in     IN   VARCHAR2,
   start_in   IN   DATE, end_in  IN   DATE,
   val_in     IN   NUMBER)
IS
   dml_str VARCHAR2(32767) :=
      'UPDATE emp SET ' || col_in || ' = :val
        WHERE hiredate BETWEEN :lodate AND :hidate
        AND :val IS NOT NULL';
BEGIN
   EXECUTE IMMEDIATE dml_str
  USING val_in, start_in, end_in, val_in;
END;




When we are executing a dynamic PL/SQL block (the string ends in a semicolon), we must supply an argument for each unique placeholder.

Example:

CREATE OR REPLACE PROCEDURE updnumval (
   col_in     IN   VARCHAR2,
   start_in   IN   DATE, end_in IN   DATE,
   val_in     IN   NUMBER)
IS
   dml_str VARCHAR2(32767) :=
      'BEGIN
          UPDATE emp SET ' || col_in || ' = :val
           WHERE hiredate BETWEEN :lodate AND :hidate
           AND :val IS NOT NULL;
       END;';
BEGIN
   EXECUTE IMMEDIATE dml_str
  USING val_in, start_in, end_in;
END;



Passing NULL Values:

We can't directly pass NULL via USING.

We will encounter an error, if we try to pass NULL directly. Because NULL doesn't have any datatype.

EXECUTE IMMEDIATE
   'UPDATE employee SET salary = :newsal
     WHERE hire_date IS NULL'
   USING NULL;


So we need to pass an uninitialized variable (default null) or to_number(NULL).


BEGIN
    EXECUTE IMMEDIATE
      'UPDATE employee SET salary = :newsal
        WHERE hire_date IS NULL'
      USING TO_NUMBER (NULL);
END;


NOTE:


It turns out that a dynamically constructed and executed PL/SQL block is not treated as a nested block;
instead, it is handled as if it were a procedure or function called from within the current block.
So any variables local to the current or enclosing blocks are not recognized in the dynamic PL/SQL block;
we can make references only to globally defined programs and data structures. These PL/SQL elements include standalone functions and procedures and any elements defined in the specification of a package.

Video Classes - (ORACLE DBA) - Part 9

Class - 17

Class - 18

Video Classes - (ORACLE DBA) - Part 8

Class - 15

Class - 16

Video Classes - (ORACLE DBA) - Part 7

Class - 13

Class - 14

Video Classes - (ORACLE DBA) - Part 6

Class - 11

Class - 12

Video Classes - (ORACLE DBA) - Part 5

Class - 9

Class -10

Video Classes - (ORACLE DBA) - Part 4

Class - 7

Class -8

Video Classes - (ORACLE DBA) - Part 3

Class - 5

Class - 6

Video Classes - (ORACLE DBA) - Part 2

Class - 3

Class - 4

Video Classes - (ORACLE DBA) - Part 1

Class - 1

Class - 2

Forward Declarations

"Declare elements before using them".


PL/SQL supports the forward declaration of local modules, which means that modules are declared in advance of the actual definition of that program. This declaration makes that program available to be called by other programs even before the program definition.

A forward declaration consists simply of the program header followed by a semicolon (;).This construction is called the module header.


Example:

PROCEDURE MY_TEST_PROCEDURE
IS

   FUNCTION X (..)  RETURN NUMBER;

   FUNCTION Y (..)  RETURN NUMBER;

   FUNCTION X (..) RETURN NUMBER  
   IS
   BEGIN
      -- Uses Function Y
   END X;

   FUNCTION Y(..)  RETURN NUMBER  
   IS
   BEGIN
      -- Uses Function X  
   END Y;
BEGIN
      -- Main logic
END MY_TEST_PROCEDURE;
/

Note:

We cannot make forward declarations of a variable or cursor.
This technique works only with modules (procedures and functions).


The definition for a forwardly declared program must be contained in the declaration section of the same PL/SQL block.

LOCAL MODULES

A local module is a procedure or function that is defined in the declaration section of a PL/SQL block.

Example:

DECLARE
   PROCEDURE show_date (date_in IN DATE) IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (date_in, 'Month DD, YYYY');
   END;
BEGIN
   ...
END;


Note:
Local modules must be located after all of the other declaration statements in the declaration section.
we must declare our variables, cursors, exceptions, types, records, tables, and so on before we type in the
first PROCEDURE or FUNCTION keyword.

Benefits:

1.To reduce the size of the module by stripping it of repetitive code.
2.To improve the readability of your code.


Local modules can be called only from within the block in which they are defined.

Example:

Declare
  my_avg_1 number;
  my_avg_2 number;
  my_avg_3 number;

  Function Calculate_averages (n1 number,n2 number,n3 number,n4 number,n5 number)
    return number
  is
  begin

  return ((n1+n2+n3+n4+n5)/5);

  end Calculate_averages;

Begin

  my_avg_1 := calculate_averages(10,20,30,40,50);

  my_avg_2 := calculate_averages(100,200,300,400,500);

  my_avg_3 := calculate_averages(1000,2000,3000,4000,5000);

  dbms_output.put_line ('my_avg_1:'|| my_avg_1);
  dbms_output.put_line ('my_avg_2:'|| my_avg_2);
  dbms_output.put_line ('my_avg_3:'|| my_avg_3);

END;
/

NOCOPY Parameter Mode (Hint)

There are two ways to pass parameter values:

1.By reference : Pointer of the actual parameter is passed to the formal parameter.

2.By value : value of the actual parameter is passed to the formal parameter & if program
                     terminates without an exception,the formal value is copied back to actual parameter.


                    IN : BY reference

OUT / IN OUT : By value ( If the called block fails, the values are not copied back to actual parameter)


If a large data structure is passed as an OUT / IN OUT parameter, that structure will be passed by value, and our application could experience performance degradation (copying).

To avoid this, we use NOCOPY.


parameter_name [ IN | IN OUT | OUT | IN OUT NOCOPY | OUT NOCOPY ] parameter_datatype


Depending on your application, NOCOPY can improve the performance of programs with IN OUT or OUT parameters. As we might expect, these potential gains come with a tradeoff: if a program terminates with an unhandled exception, we cannot trust the values in a NOCOPY actual parameter.


NOCOPY is a hint, not a command. This means that the compiler might silently decide that it can't fulfill your request for a NOCOPY parameter treatment.

Example:

Create or Replace Procedure test_out_par
(my_out_var out NOCOPY varchar2,
 my_in_var in varchar2)
Is
  my_exception exception;
Begin
  my_out_var:=my_in_var;
  raise my_exception;
End;
/


Declare
  my_out_var_kk varchar2(200);
  my_in_var_kk  varchar2(200);
Begin
  my_in_var_kk :='kiran';
  my_out_var_kk :='kodavati';
  dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
  dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
  test_out_par (my_out_var_kk,my_in_var_kk);
Exception
  when others then
    dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
    dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
End;
/

Note: Even we got the exception the variable value got changed (MY_OUT_VAR_KK) because of NOCOPY.

Procedure / Function / Parameters

Procedure:

Syntax:

PROCEDURE [schema.]name [( parameter [, parameter ...] ) ]
   [AUTHID DEFINER | CURRENT_USER]
IS
   [declarations]


BEGIN
   executable statements


[ EXCEPTION
     exception handlers]


END [name];


Function:

Syntax:

FUNCTION [schema.]name [( parameter [, parameter ...] ) ]
   RETURN return_datatype
   [AUTHID DEFINER | CURRENT_USER]
   [DETERMINISTIC]
   [PARALLEL ENABLE ...]
   [PIPELINED]
IS
   [declaration statements]


BEGIN
   executable statements


[EXCEPTION
   exception handler statements]


END [ name ];


Note:

A function's execution section should have a RETURN statement in it,although it is not necessary for the function to compile. If, however, your function finishes executing without processing a RETURN statement, Oracle will raise the following error:

ORA-06503: PL/SQL: Function returned without value



PARAMETERS

1. A parameter has a passing mode.
2. A parameter declaration must be unconstrained.


Unconstrained means, the size or the upper limit of the datatype must not be set

Unconstrained Example: VARCHAR2
                                      NUMBER

Constrainted   Example : VARCHAR2(60)


The formal parameter and the actual parameter that corresponds to it (when called) must be of the same or compatible datatypes.

The actual parameter values are copied to the formal parameters in the function header and those will be used within the function.


Parameter Modes:

1. IN ( Read only ) : Default Mode

2. OUT ( Write only ):

Any assignments made to OUT parameters are rolled back when an exception is raised in the program. Because the value for an OUT parameter is not actually assigned until a program completes successfully, any intermediate assignments are therefore ignored. Unless an exception handler traps the exception and then assigns a value to the OUT parameter,no assignment is made to that parameter. The variable will retain the same value it had before the program was called.

Example:

Create or Replace Procedure test_out_par
(my_out_var out varchar2,
 my_in_var in varchar2)
Is
  my_exception exception;
Begin
  my_out_var:=my_in_var;
  raise my_exception;
End;
/


Declare
  my_out_var_kk varchar2(200);
  my_in_var_kk  varchar2(200);
Begin
  my_in_var_kk :='kiran';
  my_out_var_kk :='kodavati';
  dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
  dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
  test_out_par (my_out_var_kk,my_in_var_kk);
Exception
  when others then
    dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
    dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
End;
/

3. IN OUT ( Read / Write )

An IN OUT parameter cannot have a default value.


Matching Actual and Formal Parameters:


1. Positional notation
2. Named notation : (formal parameter => actual parameter)

Note: If we do mix notation, however, we must list all of our positional parameters before any named notation parameters. Positional notation has to have a starting point from which to keep track of positions, and the only starting point is the first parameter.


Advantages of Named notation:

--  We can list the parameters in any order we want.

--  We can also include only the parameters we want or need in the parameter list.

--  Self documenting.


As a general rule, named notation is the best way to write code that is readable and more easily maintained.


Default values:

There are two ways to specify a default value:

1.with the keyword DEFAULT
2.with the assignment operator (:=)

DATE / TIMESTAMP DATATYPES.

We have two options to store the date in oracle table.
1. DATE DATATYPE
2. TIMESTAMP DATATYPE

1. DATE DATATYPE:
It has the ability to store the month, day, year, century, hours, minutes, and seconds.
The problem with the DATE datatype is it's granularity when trying to determine a time interval
between two events when the events happen within a second of each other.

2. TIMESTAMP DATATYPE:
Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.

Trunc can't be used on TIMESTAMP DATATYPE columns.

Example:

select TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') from table;

Where FF, represents the Fractional seconds part.

Calculating the time difference between two TIMESTAMP datatypes is much easier than the DATE datatype.

When we just do straight subtraction of the columns,we see, the results are much easier to recognize,  X days, Y hours, Z minutes, and F seconds.


In DATE we use : SYSDATE
IN TimeStamp we use: SYSTIMESTAMP

Example:

SELECT SYSTIMESTAMP FROM DUAL;


Oracle's TIME ZONE feature of the TIMESTAMP datatype:

1. WITH TIME ZONE
2. WITH LOCAL TIME ZONE

We have two options when setting the timezone of a database.

1. By specifying the displacement (hh:mi) from GMT/UTC.
2. By specifying the name in V$TIMEZONE.

The timezone of the database is set at the time of creation & it can be altered.

select DBTIMEZONE from dual;

ALTER database SET TIME_ZONE = '-04:00';

We need to bounce back the database to make the alter affective.

We can set at session level

Alter session set TIME_ZONE='-03:00';

Select SESSIONTIMEZONE from dual;


SYSTEMTIMESTAMP : This will return the sysdate with time zone information.

SELECT SYSTIMESTAMP FROM DUAL;

10/4/2010 4:13:56.200918 PM -04:00

If we have set the time zone info at the session level, we need to use CURRENT_TIMESTAMP

select CURRENT_TIMESTAMP from dual;

Example:

Create table date_table (
                                      time_stamp_tz   TIMESTAMP WITH TIME ZONE,
                                      time_stamp_ltz  TIMESTAMP WITH LOCAL TIME ZONE
                                     );


Select dbtimezone,sessiontimezone from dual;

Insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP);

Alter session set time_zone='-03:00';

Select dbtimezone,sessiontimezone from dual;

Insert into date_table values (LOCALTIMESTAMP,LOCALTIMESTAMP );

Insert into date_table values (CURRENT_TIMESTAMP ,SYSTIMESTAMP);

COMMIT;

CURSOR VARIABLES & REF CURSORS

SYNTAX: 


-- Define a Cursor type
TYPE cursor_type_name IS REF CURSOR [ RETURN return_type ];


-- Declare a Cursor variable
cursor_name cursor_type_name;


-- Open a Cursor object and attach to a Cursor variable
OPEN cursor_name FOR select_statement;


-- Fetch data from Cursor 
FETCH cursor_variable_name INTO [record_name|variable_name, variable_name,..];


A cursor variable is a reference to the work area, but the explicit cursor names the work area.

A cursor varaiable can be opened of any query.


Similar to collections, we need to define the type and then declare a variable of that type.

Example:

Declare
  Type my_ref is ref cursor return source_daily%rowtype;
  my_ref_var my_ref;
Begin
  null;
End;
/


We can classify into two catergories.


1. Strong Ref Cursor :  The return type is defined at the type declaration level

Example:


Declare
  Type my_ref is ref cursor return source_daily%rowtype;
  my_ref_var my_ref;
Begin
  Open my_ref_var for select * from source_daily;
End ;
/



2. Weak Ref Cursor : This can be opened for any query.

Example:

Declare
  Type my_ref is ref cursor;
  my_ref_var my_ref;
Begin
  Open my_ref_var for select * from source_daily;
  Open my_ref_var for select * from target_monthly;
End ;
/



SYS_REFCURSOR:

Starting with Oracle9i Database, Oracle provides a predefined weak REF CURSOR type named SYS_REFCURSOR.

we no longer need to define our own weak type; just use Oracle's

DECLARE
       my_cursor SYS_REFCURSOR;


Note : No need Type statement (Defining Ref Cursor Type).


When ever a cursor variable is opened for the first time, it create a cursor object, which points to the query(result set) & when we re-open the same variable for the second time, it will use the same object, but it changes the query(result set).

If we open a cursor variable and then do assignment to new cursor variable,the newly assigned variable points to the same object.
What ever is done on one variable will reflect to the second.

Example:

Declare
  my_ref_var     sys_refcursor;
  my_ref_var_dup sys_refcursor;
  source_daily_rec source_daily%rowtype;
Begin
  open my_ref_var for select * from source_daily;
  my_ref_var_dup := my_ref_var;
  fetch my_ref_var into source_daily_rec;
  dbms_output.put_line(my_ref_var%rowcount);
  dbms_output.put_line(my_ref_var_dup%rowcount);
  fetch my_ref_var_dup into source_daily_rec;
  dbms_output.put_line(my_ref_var%rowcount);
  dbms_output.put_line(my_ref_var_dup%rowcount);
  close my_ref_var_dup;
  if my_ref_var%isopen then
    dbms_output.put_line('my_ref_var is still open');
  else
    dbms_output.put_line('my_ref_var was closed');
  end if;
End ;
/

Note: The assigned cursor variable acts as alias to the first variable.


ROWTYPE_MISMATCH Exception:

If the query and the INTO clause do not structurally match, then the PL/SQL runtime engine will raise the predefined ROWTYPE_MISMATCH exception.



SCOPE OF CURSOR OBJECT:

Once a OPEN FOR creates a cursor object, the cursor object remains accessible as long as atleast one
cursor variable refers to that cursor object.

This means that we can create a cursor object in one scope (PL/SQL block) and assign it to a cursor variable. Then, by assigning that cursor variable to another cursor variable with a different scope,
the cursor object remains accessible even if the original cursor variable has gone out of scope.

Example:

Create or Replace Function return_cursorobject (my_query varchar2)
 Return SYS_REFCURSOR
IS
  my_local_cur sys_refcursor;
Begin
  Open my_local_cur for my_query;
  return my_local_cur;
End;
/
-- MAIN BLOCK

Declare
  my_local_cursor sys_refcursor;
  my_fectch_rec target_monthly%rowtype;
Begin
  my_local_cursor := return_cursorobject ('select * from target_monthly');
  fetch my_local_cursor into my_fectch_rec;
  dbms_output.put_line(my_local_cursor%rowcount);
End;
/

EXAMPLE on LAG Function.

Example:
Generate Aggregated Rolling last  six quarter results per sector per quarter.   
Output :  Quarter ,  Current Quarter Result ,  Aggregated Last Six quarter Results.


-- Create a table for test example

create table sector_quarter_results
(quarter varchar2(20),
 sector  varchar2(20),
 results number);

-- Financial sector

insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',100000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',90000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',80000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',70000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',60000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',50000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',40000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',30000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',20000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',10000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',5000,'Financial');

-- Healthcare sector

insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',1000,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',900,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',800,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',700,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',600,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',500,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',400,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',300,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',200,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',100,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',50,'Healthcare');


-- Insurance Sector

insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',10000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',9000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',8000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',7000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',6000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',5000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',4000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',3000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',2000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',1000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',500,'Insurance');

commit;

Reporting Query:

select quarter,sector,current_results, 
                                          (nvl(current_results,0)+
                                           nvl(previous_quarter_1,0)+
                                           nvl(previous_quarter_2,0)+
                                           nvl(previous_quarter_3,0)+
                                           nvl(previous_quarter_4,0)+
                                           nvl(previous_quarter_5,0)) rolling_6_quarters
from
(
     select quarter,
            sector,
            results current_results,
            lag(results,1) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_1,
            lag(results,2) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_2,
            lag(results,3) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_3,
            lag(results,4) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_4,
            lag(results,5) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_5
     from sector_quarter_results
)
order by sector asc,(substr(quarter,4)||substr(quarter,2,1)) desc;

OUTPUT:

QUARTER         SECTOR     CURRENT_RESULTS  ROLLING_6_QUARTERS
---------------- ------------------ ---------------           ------------------
Q3 2010              Financial                     100000             450000
Q2 2010              Financial                      90000             390000
Q1 2010              Financial                      80000             330000
Q4 2009              Financial                      70000             270000
Q3 2009              Financial                      60000             210000
Q2 2009              Financial                      50000             155000
Q1 2009              Financial                      40000             105000
Q4 2008              Financial                      30000              65000
Q3 2008              Financial                      20000              35000
Q2 2008              Financial                      10000              15000
Q1 2008              Financial                       5000               5000

Q3 2010              Healthcare                      1000               4500
Q2 2010              Healthcare                       900               3900
Q1 2010              Healthcare                       800               3300
Q4 2009              Healthcare                       700               2700
Q3 2009              Healthcare                       600               2100
Q2 2009              Healthcare                       500               1550
Q1 2009              Healthcare                       400               1050
Q4 2008              Healthcare                       300                650
Q3 2008              Healthcare                       200                350
Q2 2008              Healthcare                       100                150
Q1 2008              Healthcare                        50                 50

Q3 2010              Insurance                      10000              45000
Q2 2010              Insurance                       9000              39000
Q1 2010              Insurance                       8000              33000
Q4 2009              Insurance                       7000              27000
Q3 2009              Insurance                       6000              21000
Q2 2009              Insurance                       5000              15500
Q1 2009              Insurance                       4000              10500
Q4 2008              Insurance                       3000               6500
Q3 2008              Insurance                       2000               3500
Q2 2008              Insurance                       1000               1500
Q1 2008              Insurance                        500                500

RATIO_TO_REPORT

RATIO_TO_REPORT

Generate the value of the denominator for performance calculations.

Example:
Divide each salesperson's total sales (SP_SALES) by the total region sales (REGION_SALES) to determine what ratio of the total region sales can be attributed to each salesperson.


SELECT region_id, salesperson_id,
  SUM(tot_sales) sp_sales,
  SUM(tot_sales) / SUM(SUM(tot_sales)) OVER (PARTITION BY region_id) percent_of_region
  FROM orders
  WHERE year = 2001
  GROUP BY region_id, salesperson_id;


This can be done by RATIO_TO_REPORT function.


SELECT region_id, salesperson_id,
  SUM(tot_sales) sp_sales,
  RATIO_TO_REPORT(SUM(tot_sales)) OVER (PARTITION BY region_id)  sp_ratio
FROM orders
WHERE year = 2001
GROUP BY region_id, salesperson_id;

LAG / LEAD

LAG / LEAD

All rows to be referenced by their position relative to the current row.

They are useful for comparing one row of a result set with another row of the same result set.

Example:
"Compute the total sales per month for the Mid-Atlantic region, including the percent change from the previous month"

Requires data from both the current and preceding rows in order to calculate the answer.



Example

SELECT month, SUM(tot_sales) monthly_sales,

  LAG(SUM(tot_sales), 1) OVER (ORDER BY month) prev_month_sales

FROM orders

WHERE year = 2001

  AND region_id = 6

GROUP BY month

ORDER BY month;



     MONTH MONTHLY_SALES PREV_MONTH_SALES

---------- ------------- ----------------

         1        610697

         2        428676           610697

         3        637031           428676

         4        541146           637031

         5        592935           541146

         6        501485           592935

         7        606914           501485

         8        460520           606914

         9        392898           460520

        10        510117           392898

        11        532889           510117

        12        492458           532889

As we might expect, the LAG value for month 1 is NULL, since there is no preceding month. This would also be the case for the LEAD value for month 12.
Take this into account when performing calculations that utilize the results of the LAG or LEAD functions.
The next query utilizes the output from the previous query to generate the percentage difference from month to month.
Note how the prev_month_sales column is wrapped in the NVL function so that month 1 won't generate a NULL value for the percentage change:

SELECT months.month month, months.monthly_sales monthly_sales,

  ROUND((months.monthly_sales -- NVL(months.prev_month_sales,

    months.monthly_sales)) /

    NVL(months.prev_month_sales, months.monthly_sales),

    3) * 100 percent_change

FROM

 (SELECT month, SUM(tot_sales) monthly_sales,

    LAG(SUM(tot_sales), 1) OVER (ORDER BY month) prev_month_sales

  FROM orders

  WHERE year = 2001

    AND region_id = 6

  GROUP BY month) months

ORDER BY month;



     MONTH MONTHLY_SALES PERCENT_CHANGE

---------- ------------- --------------

         1        610697              0

         2        428676          -29.8

         3        637031           48.6

         4        541146          -15.1

         5        592935            9.6

         6        501485          -15.4

         7        606914             21

         8        460520          -24.1

         9        392898          -14.7

        10        510117           29.8

        11        532889            4.5

        12        492458           -7.6

Windowing Functions

Windowing Functions:

In cases such as
1. From the current row to the end of the partition
2. For the 30 days preceeding the transaction date
3. All rows having a transaction amount within 5% of the current row's transaction amount.

-- COMPLETE PARTITION WINDOW

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROLLING CALCULATIONS
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- WINDOW OF (CURRENT ROW - X ROWS) , (CURRENT ROW) , (CURRENT ROW + Y ROWS)
ROWS BETWEEN X PRECEDING AND Y FOLLOWING



FIRST_VALUE  AND LAST_VALUE :

These can be used with the windowing functions to identify the values of the first and last values
in the window.

FIRST_VALUE (sum(total_sales)) over (order by month
                                     rows between 1 preceding and 1 following) first_value_in_window,
LAST_VALUE  (sum(total_sales)) over (order by month
                                     rows between 1 preceding and 1 following) last_value_in_window,
Avg(sum(total_sales)) over (order by month
                            rows between 1 preceding and 1 following) rolling_3_month_avg

Ranking functions

Ranking functions:

1.RANK
2.DENSE_RANK
3.ROW_NUMBER
4.NTILE
5.WIDTH_BUCKET
6.CUME_DIST
7.PERCENT_RANK
8.FIRST / LAST (KEEP)


1.RANK: Starts with 1, if duplicate values, same rank is assigned. Gaps appear in the ranks.

2.DENSE_RANK: Starts with 1, if duplicate values,same rank is assigned. No gap in ranks.

3.ROW_NUMBER: Starts with 1 & unique, if duplicate values,numbers are arbitrarily assigned.

The PARTITION BY clause used in ranking functions is used to divide a result set into pieces so that rankings can be applied within each subset.

Handling NULLS:

All ranking functions allow to specify where in the ranking order NULL values should appear.

Append NULLS FIRST / NULLS LAST after the ORDER BY clause of the function.

USAGE: Top/Bottom-N queries



FIRST / LAST: (KEEP)

"Find the regions with the best and worst total sales last year."

Example:

MIN(region_id)
    KEEP (DENSE_RANK FIRST ORDER BY SUM(tot_sales) DESC) best_region,
MIN(region_id)
    KEEP (DENSE_RANK LAST ORDER BY SUM(tot_sales) DESC) worst_region

NTILE:

Group the records into buckets ( equiheight buckets ).

Example:

 NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) will divide it into four buckets.


WIDTH_BUCKET:(equiwidth buckets)

WIDTH_BUCKET function groups rows of the result set into buckets.

WIDTH_BUCKET function attempts to create equiwidth buckets, meaning that the range of values is evenly distributed across the buckets.

WIDTH_BUCKET can operate on numeric or date types.

WIDTH_BUCKET uses the values of the second, third, and fourth parameters to generate N buckets.

If the expression yields values that fall outside the range specified by the second and third parameters, the WIDTH_BUCKET function will generate two additional buckets, numbered 0 and N+1, into which the outliers are placed.

Example:
 WIDTH_BUCKET(SUM(tot_sales), 1, 3000000, 3) sales_buckets

Based on these parameters, the WIDTH_BUCKET function generates three buckets;
the first bucket starts at 1, and the third bucket has an upper range of 3,000,000.
Since there are three buckets, the ranges for each bucket will be
1 to 1,000,000,
1,000,0001 to 2,000,000, and
2,000,0001 to 3,000,000.
Any values less than 1 or greater than 3,000,000 will be placed to 0th and 4th buckets.

NOTE:

Keep in mind that the WIDTH_BUCKET function does not remove rows from the result set that do not lie within the specified range; rather, they are placed into special buckets that your query can either utilize or ignore as needed.


CUME_DIST and PERCENT_RANK :

CUME_DIST( )  : (Cumulative Distribution)
   Ratio of the number of rows that have a lesser or equal ranking to the total number of rows in the partition.

PERCENT_RANK( ) :
   Ratio of a row's ranking to the number of rows in the partition.

   PERCENT_RANK=((Rank of the row -1) / (Total rows -1))

Example:

CUME_DIST(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_cume_dist
PERCENT_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_percent_rank

SELECT FOR UPDATE / WHERE CURRENT OF

SELECT FOR UPDATE
When a cursor is opened, the sql statement gets executed and builds the dataset.
Once the dataset(resultset) is built, the changes made to the data at the database level will not be reflected to the dataset built.

Example:

Session X  (session sleeps for 10 minutes)

Declare
  cursor emp_rec is select * from scott.emp;
  emp_record emp_rec%rowtype;
Begin
  open emp_rec;
-- sleep for 10 mins
  dbms_lock.sleep(600);
  loop
    fetch emp_rec into emp_record;
   dbms_output.put_line('emp no:' || emp_record.empno || ' salary:' || emp_record.sal);
   exit when emp_rec%notfound;
  end loop;
End;
/

Session Y (while the Session X is in sleep mode)

update emp set sal=sal*1.1;
commit;

The increased salaries will not be reflected to the cursor dataset.

If we want to lock the rows captured by the cursor, we use the FOR UPATE OF clause.

When we issue a SELECT...FOR UPDATE statement , Oracle automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement.

No one else will be able to change any of these records until we perform a ROLLBACK or a COMMIT
but other sessions can still read the data.

we can use the FOR UPDATE clause in a select with multiple tables.All the tables with the columns in the OF list will be locked.

Example:

Cursor emp_cur is
  select
    emp.empno,
    emp.sal,
    dept.dept_name
  from emp,dept
  where emp.deptno=dept.deptno
  FOR UPDATE OF emp.SAL;

In the above Cursor only emp table rows will be locked for update not the dept table.

Once COmmit or Rollback is encountered after the cursor is open,locks will be released & we can no longer fetch data from the cursor.

Note:
we cannot execute another FETCH against a FOR UPDATE cursor after we COMMIT or ROLLBACK.

WHERE CURRENT OF :

"Delete the row I just fetched."
"Update the columns in the row I just fetched."

PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor.

To update columns in the most recently fetched row, specify:
    UPDATE table_name
       SET set_clause
     WHERE CURRENT OF cursor_name;

To delete the row from the database for the most recently fetched record, specify:
    DELETE
      table_name
     WHERE CURRENT OF cursor_name;

EXAMPLE:

Declare
  cursor emp_rec is select * from scott.emp for update;
  emp_record emp_rec%rowtype;
  my_sal scott.emp.sal%type;
BEGIN
  open emp_rec;
  fetch emp_rec into emp_record;
  dbms_output.put_line(emp_record.sal);
  update scott.emp set sal=sal*2
    where current of emp_rec;
  select sal into my_sal from scott.emp where empno=emp_record.empno;
  dbms_output.put_line(my_sal);
  close emp_rec;
commit;
End;
/

BULK COLLECT / LIMIT CLAUSE

BULK COLLECT:

Bulk collect can retrieve multiple rows of data through either an implicit or an explicit query with a single roundtrip to and from the database.

... BULK COLLECT INTO collection_name[, collection_name] ...

We can use BULK COLLECT keywords in any of the following clauses:
1.SELECT INTO
2.FETCH INTO
3.RETURNING INTO

SELECT...BULK COLLECT will not raise NO_DATA_FOUND if no rows are found.
Instead, we must check the contents of the collection to see if there is any data inside it.

The BULK COLLECT operation empties the collection referenced in the INTO clause before executing the query. If the query returns no rows, this collection's COUNT method will return 0.

Examples:

Declare  Type my_test_t is table of source_daily%rowtype;
  my_test_collection my_test_t :=my_test_t();
Begin
  select * bulk collect into my_test_collection 
  from source_daily;
  dbms_output.put_line(my_test_collection.count);
End;/

Declare  Type my_test_t is table of source_daily%rowtype;
  my_test_collection my_test_t :=my_test_t();
  Cursor test_cur is
    select * 
    from source_daily;
Begin  Open test_cur;
  Fetch test_cur
              bulk collect into my_test_collection;
  Dbms_output.put_line(my_test_collection.count);
End;/


LIMIT:

LIMIT clause for BULK COLLECT allows us to limit the number of rows fetched from the database.

FETCH cursor_name BULK COLLECT INTO ... [LIMIT rows];

where rows can be any literal, variable, or expression that evaluates to an integer.

LIMIT is very useful with BULK COLLECT, because it helps us to manage how much memory our program will be used to process the data.

[rows] this many number of rows that will be fetched into collections per single trip.

For every iteration the collections will be refreshed with the new fetched rows.
Once all the rows are done, the count on the collection will be zero and we can exit the fetch loop.

Example:

create table limit_test (rno number);

-- Populate test data
Begin
  For i in 1..10004
   loop
     insert into limit_test values (i);
   end loop;
  commit;
End;
/

Declare
  Type limit_test_t is table of limit_test%rowtype;
  limit_collection limit_test_t;
  Cursor c1 is select * from limit_test;
Begin 
  Open c1;
-- Bulk collect without any limit
  Fetch c1 bulk collect into limit_collection;
  Close c1;
  dbms_output.put_line(limit_collection.count);

-- Reopen the cursor & use limit
  open c1;
  loop
    fetch c1 bulk collect into limit_collection limit 30;
    exit when limit_collection.count=0;
    dbms_output.put_line(limit_collection.count);
  end loop;
End;/

RETURNING Clause : (With Bulk collect)

We can use the RETURNING clause within the DML (UPDATE/DELETE) to grab the modified info
by the statement into collections.

1.Execute the DML Statement and Grab the modified values into Collections Via Bulk Collect on the fly.
2. Use the variables in the code.

Note:

If the Update/Delete affects only one row, we can use the RETURNING Clause and grab the modified data into the Scalar variables.

If the Update/Delete affects multiple rows, we need to use BULK COLLECT and COLLECTION to grab the modified data using the RETURNING Clause.

CURSORS : Explict Cursors

EXPLICIT CURSORS:

Syntax:
CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]
       [ RETURN return_specification ]
       IS SELECT_statement
           [FOR UPDATE [OF [column list]];

USAGE FLOW:
Declare the cursor in the declaration block.
Open the cursor in execution block.
fetch the data from the opened cursor.
Close the cursor.


1. OPEN :

OPEN cursor_name [ ( arguments,... ) ];

arguments are the values to be passed, if the cursor was declared with a parameter list.

When we open the cursor,PL/SQL executes the query for that cursor.
Regardless of the when we perform the fetch, it reflects the data as it existed when cursor was opened.
If Select statement in the cursor uses a FOR UPDATE,all rows will be locked untill cursor is closed.

We can't open an already opened cursor untill closed.

2. FETCH :

FETCH cursor_name INTO record_or_variable_list;

We should always fetch into a record that was defined with %ROWTYPE against the cursor.

Once a cursor is open, we can fetch from it untill no more rows left but we can continue to fetch past the last record.we don't get any exception & fetch doesn't do anything( it wont' even make the variables to null).

Example:

Declare
-- returns zero records
cursor my_test_cursor is select sysdate from dual where 1=2;
my_date date:=sysdate+10;
begin
open my_test_cursor;
fetch my_test_cursor into my_date;
-- The variable is not set to NULL and it has the default value.
dbms_output.put_line(my_date);
end;



Note: All the calculated or virtual columns need to be aliased in the Explicit cursors.

3. CLOSE :

CLOSE cursor_name;

If we leave too many cursors open, we may exceed the value set by the database initialization parameter, OPEN_CURSORS.
If this happens, we will encounter error message:
    ORA-01000: maximum open cursors exceeded
If we get this message, check the usage of package-based cursors to make sure they are closed
when no longer needed.
BEGIN
       OPEN my_package.my_cursor;
       ... Do stuff with the cursor
       CLOSE my_package.my_cursor;
    EXCEPTION
       WHEN OTHERS
       THEN
          CLOSE my_package.my_cursor;
    END;

Explicit Cursor Attributes :

1. cursor_NAME%FOUND    : TRUE if a record was fetched good.
2. cursor_NAME%NOTFOUND : FALSE if a record was fetched good.
3. cursor_NAME%ROWCOUNT : Number of records fetched at that point in time.
4. cursor_NAME%ISOPEN   : TRUE if cursor is open.

EXCEPTIONS:

INVALID_CURSOR
If we try to use %FOUND, %NOTFOUND, or %ROWCOUNT before the cursor is opened
or after it is closed, Oracle will raise an INVALID_CURSOR error.

Cursor Parameters:
We can parameterize the Cursors and make it generic.
we can specify as many cursor parameters as we need.
When we OPEN the cursor, we need to include an argument in the parameter list for each parameter, except for trailing parameters that have default values.


The scope of the cursor parameter is confined to that cursor.

Example:

CURSOR emp_cur (emp_id_in NUMBER := 0)
    IS
       SELECT employee_id, emp_name
         FROM employee
        WHERE employee_id = emp_id_in;

Cursors : Implicit Cursors

Cursors:


When we execute a SQL statement from PL/SQL, the Oracle assigns a private work area for that statement and also manages the data specified by the SQL statement in the SGA.
The private work area contains information about the SQL statement and the set of data returned or affected by that statement.


Implicit Cursors:

PL/SQL declares and manages an implicit cursor every time we execute an DML (SELECT INTO,INSERT,UPDATE,DELETE).

SYNTAX:

SELECT column_list
      [BULK COLLECT] INTO PL/SQL variable list
      ...SQL SELECT statement...


A common use of implicit cursors is to perform a lookup based on a primary key.(returns only one row)

If we need to fetch multiple rows

1. Go for Explicit cursors.
2. BULK COLLECT INTO 


Implicit cursors can't be controlled.


Common Exceptions:

1. NO_DATA_FOUND
2. TO_MANY_ROWS


When ever these exceptions occur,execution of the current block terminates and control is passed to the execution section.

Best practice:

Include the implicit cursor as a separate PL/SQL BLOCK.



DECLARE
-------
-------
Begin
-------
-------
     Begin
        Select xxxx INTO xxxx from table where <conditions>; --<Implicit Cursor>
     Exception
        When NO_DATA_FOUND then
         xxxxxx;
        When TO_MANY_ROWS then
        xxxxxxx;
     END;
--------
--------
Exception
---------
---------
END;
/



EXAMPLE:

Create new email account in email_account TABLE

LOGIC FLOW: check if the entered email exists,
                          if exists return 'NOT AVAILABLE'
                          else 'AVAILABLE'


CREATE Function Check_email_availability
(entered_email in varchar2)
return varchar2

begin

   select email_address into return_value from email_account
   where email=entered_email;

   Return return_value;

Exception
 When NO_DATA_FOUND 
 then
      Return NULL;
 When TO_MANY_ROWS
 then
      DBMS_OUTPUT.PUT_LINE('DATA INTEGRITY');
      RAISE;
END;

-- CALLING PL/SQL BLOCK --

Declare
-------
-------
-------
Begin

  IF (check_email_availability) IS NULL

      'Email Available'
  else
  
      'Email is not Available'
  END IF;

-------
-------
-------
EXCEPTION
-------
-------
END;


IF      select email_address into return_value from email_account
        where email=entered_email;
doesn't return any row means the entered email is not used by anyone and can be used, but the implicit cursor raises NO_DATA_FOUND exception and we handle it and return NULL.


IF       select email_address into return_value from email_account
         where email=entered_email;
returns more than one, it means something wrong with the table design.



CURSOR ATTRIBUTES:

1. SQL%FOUND    : Returns TRUE if one row was fetched.
2. SQL%NOTFOUND : Returns FALSE if one row was fetched.
3. SQL%ROWCOUNT : Returns the number of rows fetched from the cursor.(SELECT INTO : 1)
4. SQL%ISOPEN   : Alway FALSE

All the implicit cursor attributes return NULL if no implicit cursors have yet been executed in the session.
Otherwise, the values of the attributes always refer to the most recently executed SQL statement, regardless
of the block or program from which the SQL statement was executed.