High Water Mark Oracle

In simple term High water mark is a logical mark in the memory area to separate blocks having data and no data. As you know that the blocks represent the most granular form of data in memory.

When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward as data get saved into the database. After inserting some records High Water Mark will be moved to forward.

See below given logical and pictorial representation of high water mark when a table data insert/update/delete/truncate

High Water Mark Oracle
High Water Mark Oracle

By default High Water Mark starts at the first block for new tables. When table gets data via insert/update more blocks being used High Water Mark moves forward. When data get deleted, High Water Mark remain same even though blocks doesn’t have any data.

Delete has no impact on high water mark and Truncate reset the High Water Mark. This is the one difference between delete and truncate ( Read more difference between Delete and Truncate )

When full table scan takes place, Oracle scans all blocks below High Water Mark even though it has no data.

Advertisements

5 different ways to test Oracle Ref Cursor results

In the previous posts we have covered Ref Cursors and Ref Cursor examples.
Now we will examine how Ref Cursor examples are tested.

For all the examples I have used the package we have created in Ref Cursor Example post

create or replace package pkg_refcur 
is
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);
………………………………………….
………………………………………….
End pkg_refcur;
/

create or replace package body pkg_refcur
is
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select e.empno,e.empname,d.deptno,d.deptname from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

………………………………………….
………………………………………….
End pkg_refcur;

1. Using sqlplus 

— Declare a variable for refcursor
SQL> variable v_refcur refcursor;

— Execute the package
SQL> exec pkg_refcur.p_get_employees(2,:v_refcur);

PL/SQL procedure successfully completed.

— Print the refcursor variable
SQL> print v_refcur;

EMPNO EMPNAME                  SALARY     DEPTNO    MANAGER
—————————————————————–
16 Henry                      8500          2         11
17 Robert                     9500          2         16
18 Paul                       7700          2         17

Please note that v_refcur is a bind variable here.

2. Using %rowtype 

This will be useful only when the ref cursor is based on a single table

SQL> set serveroutput on;
SQL> declare
rec_emp  emp%rowtype;
v_refcur sys_refcursor;
Begin
pkg_refcur.p_get_employees(2,v_refcur);
loop
fetch v_refcur into rec_emp;
exit when v_refcur%notfound;
dbms_output.put_line(‘Empno   –>’ || rec_emp.empno);
dbms_output.put_line(‘Empname –>’ || rec_emp.empname);
dbms_output.put_line(‘Salary   –>’ || rec_emp.salary);
dbms_output.put_line(‘deptno –>’ || rec_emp.DEPTNO);
end loop;
End;
/
Empno   –>16
Empname –>Henry
Salary   –>8500
deptno –>2
Empno   –>17
Empname –>Robert
Salary   –>9500
deptno –>2
Empno   –>18
Empname –>Paul
Salary   –>7700
deptno –>2

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose

3. Using PL/SQL table of type and record 

Procedure p_get_dept_emp cannot be tested with using (2). So declare a record and type on it.

SQL> Declare
2  Type rec_emp_dept is record
3  (
4  empno    emp.empno%type,
5  empname  emp.empname%type,
6  deptno   dept.deptno%type,
7  deptname dept.deptname%type
8  );
9  rec      rec_emp_dept;
10  v_refcur sys_refcursor;
11  Begin
12  pkg_refcur.p_get_dept_emp(2,v_refcur);
13   loop
14       fetch v_refcur into rec;
15       exit when v_refcur%notfound;
16       dbms_output.put_line(‘Empno    –>’ || rec.empno);
17       dbms_output.put_line(‘Empname  –>’ || rec.empname);
18       dbms_output.put_line(‘Deptno   –>’ || rec.deptno);
19       dbms_output.put_line(‘Deptname –>’ || rec.deptname);
20   end loop;
21  End;
22  /
Empno    –>16
Empname  –>Henry
Deptno   –>2
Deptname –>RESEARCH
Empno    –>17
Empname  –>Robert
Deptno   –>2
Deptname –>RESEARCH
Empno    –>18
Empname  –>Paul
Deptno   –>2
Deptname –>RESEARCH

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose.
This is not ideal for a Ref Cursor returning many columns.

4. Using a wrapper function to call 

This is a pretty much easy method. But may not work if you have a procedure having DML and ref cursor return

SQL> create or replace function f_refcur return sys_refcursor
2  is
3  v_refcur sys_refcursor;
4  Begin
5    pkg_refcur.p_get_dept_emp(2,v_refcur);
6    return v_refcur;
7  End;
8  /

Function created.

SQL> select f_refcur from dual;

F_REFCUR
——————–
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

EMPNO EMPNAME                  DEPTNO DEPTNAME
———- ——————– ———- ——————–
16 Henry                         2 RESEARCH
17 Robert                        2 RESEARCH
18 Paul                          2 RESEARCH

5. Using Developer tools like TOAD, Sql Developer etc

Normally professional developers use any of the development tools like TOAD or SQL Developer etc.

a) Using TOAD

Step 1

Connect to TOAD

Open Schema Browser

Click “Package” tab

Locate the package and choose “Execute Procedure” from right click menu

Test Ref Cursor TOAD Step 1
Test Ref Cursor TOAD Step 1

Step 2

Click right most icon of the opened window as shown below

Test ref cursor from TOAD step 2
Test ref cursor from TOAD step 2

Step 3

Choose the last radio button “Load into grid from memory (strong and weak)”

Test ref cursor from TOAD step 3
Test ref cursor from TOAD step 3

Click OK

Click OK

Now you can see the ref cursor results in another window as shown below

Test ref cursor from TOAD Step 4
Test ref cursor from TOAD Step 4

In similar manner you can test any ref cursor within package/procedure/function

b) SQL Developer

SQL Developer is a Oracle Corporation product.

Step 1

Connect to SQL Developer ( I Have used version 3.0.4)

Click “Package” node and locate the package

Test red cursor from SQL Developer Step 1
Test red cursor from SQL Developer Step 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 2

Click Run button or Ctrl-F10

Test red cursor from SQL Developer Step 2
Test red cursor from SQL Developer Step 2

Click OK

Step 3

Test red cursor from SQL Developer Step 3
Test red cursor from SQL Developer Step 3

 

 

 

 

 

 

 

 

 

 

(In the message window you have to choose “Output variables”)

Conclusion :- Most of the ref cursors can be tested with either of the method. Also you can use the base query directly in SQL window or SQL plus to test the ref cursors.

Ref Cursors Examples

REF CURSOR is a data type in the Oracle. To read more about REF CURSOR

I have created a package to explain few different ref cursor examples.
It covers most of the typical and simple examples.

create or replace package pkg_refcur 
is
— Declaring a Strong Ref Cursor Type
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

— Declaring a Weak Ref Cursor Type
TYPE ref_weak_typ IS REF CURSOR;

–Package Signatures
procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);

procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor);

procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor);

procedure p_dynamic(pi_tablename  in   integer,
po_results    out  sys_refcursor);

End pkg_refcur;
/

create or replace package body pkg_refcur
is
— 1)
— Using Strong ref cursor to return employees in a department
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

— 2)
— Using weak ref cursor to return employees and department details
procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

— 3)
— Using system ref cursor to return employees and department details
procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_deptemp;

— 4)
— Associating multiple query
procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor)
is
Begin
if upper(pi_type) = ‘DEPT’ then
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘EMP’ then
Open po_results for
select * from dept e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘BOTH’ then
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
end if;
Exception
when others then
raise;
End p_getdeptemp;

— 5)
— Associating multiple query with dynamic query
procedure p_dynamic(pi_tablename  in  integer,
po_results    out sys_refcursor)
is
Begin
if upper(pi_tablename) in (‘EMP’,’DEPT’) then — for security
Open po_results for
‘select * from ‘ || pi_tablename ;
end if;
Exception
when others then
raise;
End p_dynamic;

End pkg_refcur;
/

Conclusion :- Ref Cursors are very ideal for interchanging data between two different application involving Oracle as a database, like Java and Oracle, Dot net and oracle, Oracle forms and Oracle etc.

Ref Cursors Oracle

The REF CURSOR is a data type in the Oracle. REF CURSOR also referred as Cursor Variables.Cursor variables are like pointers to result sets. Cursor can be attached to only one query while REF CURSOR can be used to associate multiple queries at run time. 

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
refcur1  empcurtyp;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;

REF CURSOR can be categorized into three

1. Strong Ref Cursor
Ref Cursors which has a return type is classified as Strong Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;

Here empcurtyp is a Strong Ref Cursor

2. Weak Ref Cursor
Ref Cursors which has no return type is classified as Weak Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR;
…..
End;

Here empcurtyp is a Weak Ref Cursor

3. System Ref Cursor
This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.

Declare
empcurtyp SYS_REFCURSOR;
…..
End;

Advantages 
1. Ref Cursor it self is a data type and easy to declare
2. More flexible because it is not tied to a specific query
3. Easily pass as arguments from subroutine to subroutine.
4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
5. Cursor variables are bind variables ( Read more about BIND VARIABLES )

Dis-advantages
1. Ref Cursors are not efficient as StatiC Cursor
2. Need additional code to print Ref Cursor values

In general Ref Cursors are only be used when static cursor cannot do the work. 

Bind variables Oracle

In general bind variables are Oracle variables which holds data and resides in separate memory area for performance benefits and faster access.

Example 1

SQL> variable i number;
SQL> exec :i := 1;

PL/SQL procedure successfully completed.

SQL> print :i

I
———-
1

Here :i act as a bind variable

Example 2

declare
v_dname varchar2(30);
begin
execute immediate ‘select deptname from dept d where d.deptno = :x’
into v_dname
using 1;
dbms_output.put_line(v_dname);
end;

Here 😡 is a bind variable. 😡 got assigned value 1 at run-time.

Example 3

All cursor variables are bind variables

declare
cursor c1(cv_dno integer)
is
select count(*) from emp e where e.deptno = cv_dno;
n_cnt integer;
begin
open c1(1);
fetch c1 into n_cnt;
close c1;
dbms_output.put_line(‘n_cnt–>’|| n_cnt);
end;

Here cv_dno is a bind variable.

Example 4

All PL/SQL variables are bind variables.

CREATE OR REPLACE PROCEDURE
P_INSERT_DEPT(p_deptno integer,p_deptname varchar2,p_location varchar2)
AS
BEGIN
insert into dept values(p_deptno,p_deptname,p_location);
commit;
END P_INSERT_DEPT;

Here p_deptno,p_deptname and p_location are bind variables. So it is a nice idea to wrap every thing using PL/SQL variables.

Misconception about Bind Variables

1. Any variable used with : (colon) is a bind variable – not true always

2. Bind variables only referenced from SQL Plus – not true

3. Static SQL in PL/SQL block must be re-written with EXECUTE IMMEDIATE to utilize bind variables ( same as example 2) – Not at all true

Conclusion :- It is true that bind variables improves the performance, doesn’t mean that you always need to use it. But keep it in mind that for large queries bind variables will save a lot of time.

Triggers in Oracle

Oracle Trigger executes based on an event, say after a table update or before a login etc.
Oracle Database automatically executes a trigger when certain conditions occur.

Oracle triggers classified into

1. DML Triggers
2. DDL Triggers
3. Instead of Trigger

In this post we will examine DML triggers

DML Triggers

DML triggers are which associated with DML operations, say insert into a table, update a view, delete from a table etc
Mainly DML triggers are 2 types, row level and statement level.
Row level triggers are fired whenever a row changes and statement level fires whenever any statement ( for example a batch update ) executes. Both row level and statement level can be defined with 3 different DMLs, insert,delete and update

In all-together

DML Triggers

  • Row Level
  1. Before Insert  and After Insert 
  2. Before Update and After Update
  3. Before Delete and After Delete
  • Statement Level
  1. Before Insert  and After Insert
  2. Before Update and After Update
  3. Before Delete and After Delete

So you can create total 2*3*2 = 12 types of DML triggers in Oracle

Statement level DML trigger will fire once per batch, Say I have Statement-lelvel before update trigger and I am updating the whole table using just one update command, the trigger will fire only once.

Row-level trigger will always fire for each row.

“:new” and “:old”
“:new” and “:old” keywords can be used in row-level triggers only. “:old” is used to reference old column value and :new is used to reference new column. Only in Update triggers you can use “:new” and “:old” in same context. See below table for more information.

old and new reference
old and new reference

CREATE TRIGGER

CREATE TRIGGER [SCHEMA].TRIGGER_NAME
BEFORE INSERT
ON [SCHEMA].TABLE_NAME FOR EACH ROW
DECLARE
BEGIN
“:new”.colum_name1  := ‘some_value’;
“:new”.column_name2 := ‘some_value’;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_NAME;

Here [SCHEMA] is optional. In above a row-level trigger after insert on a TABLE_NAME will be created.
Below another example which combines two different type of trigger together.

CREATE or REPLACE TRIGGER [SCHEMA].TRIGGER_NAME
BEFORE INSERT
ON [SCHEMA].TABLE_NAME FOR EACH ROW
DECLARE
BEGIN
if inserting then
if “:new”.recid is null then
— do something —
end if;
elsif updating then
— do something —
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_NAME;
/

CREATE or REPLACE command creates the trigger if it doesn’t exists and replaces if it exists. Note that it replaces the trigger for the base table only. You cannot replace trigger name which is associated with another table.

DROP TRIGGER

DROP TRIGGER TRIGGER_NAME;

The above command will drop the trigger permanently.

ALTER TRIGGER

ALTER TRIGGER command can be used to rename, compile, disable and enable the trigger;

ALTER TRIGGER trg_dept compile; — recompiles the trigger

ALTER TRIGGER trg_dept disable; — disables the trigger

ALTER TRIGGER trg_dept enable; — disables the trigger

ALTER TRIGGER trg_dept rename to trg_emp; — rename the trigger

Next :- DDL Triggers

Oracle Constraints

Constraint is a rule imposed on tables to restrict the values stored in. Oracle has five different type of constraints. We will examine each one with suitable examples.

1. NOT Null Constraint

NOT NULL constraint checks the columns to be populated with non-null values.

Example :-

Create table person
(
pno   integer,
pname varchar2(20) not null
);

pname column has a not null constraint.

SQL> desc person;
Name                                      Null?    Type
—————————————– ——– ————
PNO                                                NUMBER(38)
PNAME                    NOT NULL VARCHAR2(20)

Below insert statement will validate the above not null constraints

SQL> insert into person values(1,null);
insert into person values(1,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“PERSON”.”PNAME”)

Where to Use not null constraint

Situations when column needs to populated always can use NOT NULL constraint.
For example for EMP table, empname column must be not null, but for salary it may not be.

2. UNIQUE Constraint

UNIQUE Constraint imposes each column will have distinct value within the table. Not that UNIQUE Constraint column can have null values. Null values are not considered for uniqueness. UNIQUE Constraint can be applied to composite(Two or more columns) keys also.

Example :-

Create table person
(
pno   integer,
pname varchar2(20) constraint uq_pname unique
);

SQL> insert into person values(1,’Bill’);

1 row created.

SQL> insert into person values(2,’Bill’);
insert into person values(2,’Bill’)
*
ERROR at line 1:
ORA-00001: unique constraint (UQ_PNAME) violated

When we are trying to insert PERSON table with duplicate pname it will raise an error. UNIQUE constraint column can accept null values. Not that Oracle creates a unique index automatically when UNIQUE constraint assigned to a column.

Where to use unique constraint

Unique constraints can be assigned to columns those have unique values or no values.

3. Primary Key Constraint

A primary key constraint combines a NOT NULL constraint and a UNIQUE constraint. A table can have only one PRIMARY KEY and it can be created for composite(Two or more columns) keys also.

Example :-

Create table person
(
pno   integer constraint pk_pno PRIMARY KEY,
pname varchar2(20)
);

Where to Use PRIMARY KEY constraint

Generally every table should have a PRIMARY KEY (If you are normalizing the design).

4. Foreign Key Constraint

Foreign Key Constraint used to relate two or more table and values in one table to match values in another table.

CREATE TABLE DEPT
(
DEPTNO   INTEGER PRIMARY KEY,
DEPTNAME VARCHAR2(20) NOT NULL,
LOCATION VARCHAR2(20)
);

CREATE TABLE EMP
(
EMPNO    INTEGER PRIMARY KEY,
EMPNAME  VARCHAR2(20) NOT NULL,
SALARY   NUMBER,
DEPTNO   INTEGER constraint fk_deptno references DEPT(deptno)
);

Where to Use FOREIGN KEY constraint

If you want to maintain a PARENT-CHILD relationship FOREIGN KEY constraints are ideal.

5. CHECK Constraint

CHECK Constraints are enforcing certain types of values in a column.

Create table person
(
pno    integer,
pname  varchar2(20),
status varchar2(20) constraint ch_status check( status in (‘ACTIVE’,’INACTIVE’))
);

Here status column have only two value, ‘ACTIVE’ or ‘INACTIVE’

Conclusion : – Oracle constraints are integral part of table. Constraints help us to maintain the integrity of the database. Also helps to reduce the redundant data.

Next :- Oracle Constraint Examples