PRAGMA RESTRICT_REFERENCES Oracle

PRAGMA RESTRICT_REFERENCES uses to control the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow some rules in terms of transaction control and security.     

What is PRAGMA

PRAGMA is an instruction or a hint or information to the compiler. Pragmas are processed at compile time, not at run time.

Syntax :-

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;    
PRAGMA restrict_references(get_emp_salary, RNDS, RNPS, WNDS, WNPS);
END pkg_salary;

RNDS – Read No Database State. Asserts that the function not to read or query tables
RNDS – Read No Package State. Asserts that the function not to read or reference package variables
WNDS – Write No Database State. Asserts that the function not modify database tables
WNPS – Write No Package State. Asserts that the function not modify package variables
TRUST – Asserts that the function can be trusted not to violate one or more rules. Used only when C or JAVA routines are called from PL/SQL.

Explanation :-

In the above example Function get_emp_salary is associated with PRAGMA restrict_references. Oracle conveying the compiler to follow four rules WNDS, WNPS, RNDS, RNPS. When the package body compiles and find any rules which violates any of the rules (RNDS, RNPS, WNDS, WNPS, TRUST) it will raise a compilation error. Note that if there is a PRAGMA derivative and violates any rules it will NOT raise any compiler error and it might raise run time error.

Consider the below examples

1) Function to raise employee salary 

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
END pkg_salary;
/

CREATE OR REPLACE PACKAGE body pkg_salary
IS

function get_emp_salary(p_empno integer) return number
is
n_sal number;
Begin
update emp set salary = salary + salary * 0.1 where empno = p_empno
returning salary into n_sal;
commit;
return n_sal;
End get_emp_salary;

END pkg_salary;
/

Compiling……..

SQL> ../pkg_salary.sql;

Package created.

Package body created.

Executing………

SQL> select pkg_salary.get_emp_salary(10) from dual;
select pkg_salary.get_emp_salary(10) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query

So package specification and body compiled properly but unable to use it as it raise error while executing.

2) Function to raise employee salary – With PRAGMA restrict_references

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
PRAGMA restrict_references(get_emp_salary, WNDS);
END pkg_salary;
/

CREATE OR REPLACE PACKAGE body pkg_salary
IS
function get_emp_salary(p_empno integer) return number
is
n_sal number;
Begin
update emp set salary = salary + salary * 0.1 where empno = p_empno
returning salary into n_sal;
commit;
return n_sal;
End get_emp_salary;

END pkg_salary;
/

Compiling……..

SQL> ../pkg_salary.sql;

Package created.

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY PKG_SALARY:

LINE/COL ERROR
——– —————————————————————–
4/1      PLS-00452: Subprogram ‘GET_EMP_SALARY’ violates its associated
pragma

When we use PRAGMA restrict_references it raises a compiler error which helps developer to re-write his code.
Generally PRAGMA restrict_references are used with functions.

Note:-

DEFAULT key word applies PRAGMA restrict_references to all of the sub programs in side the package
See below example

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
function get_emp_name(p_empno integer) return varchar2;
PRAGMA restrict_references(DEFAULT, WNDS);
END pkg_salary;
/

Package created.

Note:- Normally functions are not created for DML related processes and PRAGMA restrict_references are not necessary.

Advertisements

Oracle Cursor examples

We will explain different types of Cursors with simple examples. Previous post we have explained detailed about different types of cursors.

In case you want to read the previous post – Oracle Cursor – Basics

All examples are tested in Oracle 11g Release 2 version.

I used the classic DEPT and EMP tables to illustrate the examples

SQL> desc dept;
Name               Type
—————————————– ——– ————–
DEPTNO         NOT NULL NUMBER(38)
DEPTNAME  VARCHAR2(20)
LOCATION   VARCHAR2(30)

DEPTNO DEPTNAME Location
1 Sales Dubai
2 Marketing London
3 IT Paris
4 Logistics Tokyo
5 Finance Singapore

SQL> desc emp;
Name              Type
—————————————– ——– ————–
EMPNO           NOT NULL NUMBER(38)
EMPNAME     VARCHAR2(20)
SALARY          NUMBER
DEPTNO          NUMBER(38)

EMPNO EMPNAME SALARY DEPTNO
10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

1.  Single record and single column

declare
v_ename varchar2(50);
Cursor Cur_ename is select empname from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_ename;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
Close Cur_ename;
end;
/

Output would be

Employee Name –>Bill

In general case cursors are intended for multi-record queries. Just given above example for illustration purpose only.

2.  Multiple record and single column

declare
v_ename varchar2(20);
Cursor Cur_ename is select empname from emp;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_ename;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
end loop;
Close Cur_ename;
end;
/

Output would be

Employee Name –>Bill
Employee Name –>Solomon
Employee Name –>Susan
Employee Name –>Wendy
Employee Name –>Benjamin
Employee Name –>Tom
Employee Name –>Henry
Employee Name –>Robert
Employee Name –>Paul

This is a classic example. Here query returns multiple records having one column. Variable v_ename used to store the returned column

3.  Multiple record and multiple column – Same table

declare
v_emprec emp%rowtype;
Cursor Cur_ename is select * from emp;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_emprec;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Name –>’|| v_emprec.empname || ‘———-Salary –>’ || v_emprec.salary );
end loop;
Close Cur_ename;
end;

Output would be

Name –>Bill———-Salary –>12000
Name –>Solomon———-Salary –>10000
Name –>Susan———-Salary –>10000
Name –>Wendy———-Salary –>9000
Name –>Benjamin———-Salary –>7500
Name –>Tom———-Salary –>7600
Name –>Henry———-Salary –>8500
Name –>Robert———-Salary –>9500
Name –>Paul———-Salary –>7700

Here we are fetching all columns from EMP table and storing in cursor variable v_emprec. It is a row-type variable which used to hold all column values from EMP table.

4.  Multiple record and multiple column from different tables

Below example we are trying to fetch department names and employee names.

declare
Cursor Cur_ename is select empname,deptname from emp e,dept d
where d.deptno = e.deptno;
v_rec Cur_ename%rowtype;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_rec;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Name –>’|| v_rec.empname || ‘———-deptname –>’ || v_rec.deptname );
end loop;
Close Cur_ename;
end;

Here we can use different style of variables

1)

v_empname emp.empname%type;
v_deptname dept.deptname%type;

2)

v_rec  Cur_ename%rowtype;

I used 2) because in future if I want to add one more column in SQL no need to add another variable since v_rec automatically adjusts the column fetches.

5.  Nested Cursor example

declare
v_deptno emp.deptno%type;
Cursor Cur_dept is select * from dept;
Cursor Cur_emp is select * from emp e where e.deptno = v_deptno;
v_deptrec dept%rowtype;
v_emprec emp%rowtype;
begin
Open Cur_dept;
loop
Fetch Cur_dept into v_deptrec;
exit when Cur_dept%notfound;
v_deptno := v_deptrec.deptno;
dbms_output.put_line(‘Outerloop’);
— Inner loop start here
Open Cur_emp;
loop
fetch Cur_emp into v_emprec;
exit when Cur_emp%notfound;
dbms_output.put_line(‘Deptname –> ‘|| v_deptrec.deptname || ‘——Empname —       > ‘|| v_emprec.empname);
end loop;
close Cur_emp;
— End of Inner Loop
end loop;
Close Cur_dept;
end;

Output would be

Outerloop
Deptname –> Sales——Empname –> Wendy
Deptname –> Sales——Empname –> Benjamin
Deptname –> Sales——Empname –> Tom
Outerloop
Deptname –> Marketing——Empname –> Henry
Deptname –> Marketing——Empname –> Robert
Deptname –> Marketing——Empname –> Paul
Outerloop
Outerloop
Outerloop
Deptname –> Finance——Empname –> Bill
Deptname –> Finance——Empname –> Solomon
Deptname –> Finance——Empname –> Susan

Here two different cursors, one for finding all departments and other for finding employees associated with it. Outerloop executed 5 times and inner loop executed 5 times. ( Note:- Only 3 departments has employees, so total 6 employees shown in the output )

When writing nested cursor make sure

1.  Properly format the code

2. Properly apply the comments in each section

3. Try to provide log statements

The above examples covered all basic cursors and still there are many complex cursor examples need to explain and will do in another post.

Did you find above post useful ? If yes please comment.

Oracle Cursor basics

Cursor

Cursor is one of the fundamental feature of Oracle. Oracle cursor is a memory handle to the SQL area. Cursors are associated with SQL and variables. Oracle holds all relevant information about SQL and variables in the context area.

Example : – Cursor Cur_Emp is Select * from EMP where deptno =10;

Oracle Cursor Classification
Oracle Cursor Classification

Mainly there are two type of cursors. Implicit Cursors and Explicit Cursors

Implicit Cursor

Implicit Cursor is defined and controlled by Oracle Internally. It is easy use.

Example :-

declare
v_empname varchar2(50);
begin
select empname into v_empname from emp where empno = 10;
end;

select query used in above PL/SQL block is an implicit cursor

Explicit Cursor

Explicit Cursors are defined and controlled programatically. There are two types of explicit cursors, Static Cursors and Ref Cursors.

In this post we will discuss more abour Static Cursors

Static Cursors :- Defining and associating before run time. SQL is constructed and associated prior to execution or run time.

Example :-

declare
v_empname varchar2(50);
Cursor Cur_ename is select empname from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_empname;
dbms_output.put_line(‘Employee Name –>’|| v_empname);
Close Cur_ename;
end;

A cursor has mainly 4 stages. Declaration, Opening, Fetching and Closing.

Declaring Cursor

Generally Cursors all declared at declare section of the PL/SQL block or sub program and it must be associated with an SQL.

declare
Cursor Cur_ename is select empname  from emp where empno = 10;
begin

………….

Here Cur_ename is the name of the cursor and “select ename into v_ename from emp where empno = 10″is the SQL associated with it.

Opening Cursor

After declaration cursor need to open. OPEN keyword use to open a cursor

OPEN Cur_ename;

Fetching Cursor

Fetching cursor into variables. Note that number of columns in the SQL and number of variables must match and also variables must be compatible.

Fetch Cur_ename into v_ename;

In above case SQL has only one column which will return a varchar2. So we have declared a variable v_ename as varchar2.

Closing Cursor

CLOSE command will close the opened cursor.

Close Cur_ename;

Explicit Cursor Attributes

Explicit cursor has 4 attributes. %FOUND, %NOTFOUND, %ISOPEN and %ROWCOUNT.

%FOUND – Using to check the cursor is success or not. Returns true or false. If cursor returns any row then %FOUND will be true. Only used after opening the cursor.

%NOTFOUND – Using to check the cursor is not a success or not. Returns true or false. If cursor returns no rows then %NOTFOUND will be true. Only used after opening the cursor.

%ISOPEN – This is self-explanatory. Used to check whether cursor is open or not. Returns true or false.

%ROWCOUNT – To find the number of rows the cursor returned. Return as an integer.

( Note that SQL cursor has one more attribute %BULK_ROWCOUNT which using the bulk collect functionality. We will discuss this in another section)

Examples:-

1.  Cursor example with single record

declare
v_ename varchar2(50);
Cursor Cur_ename is select empname from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_ename;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
Close Cur_ename;
end;
/

Output would be

Employee Name –>Bill

In the above example Cur_ename is associated with SQL which will return only one record. So only one fetch is required here.

2.  Cursor example with multiple record

declare
v_ename varchar2(20);
Cursor Cur_ename is select empname from emp;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_ename;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
end loop;
Close Cur_ename;
end;
/

Output would be

Employee Name –>Bill
Employee Name –>Solomon
Employee Name –>Susan
Employee Name –>Wendy
Employee Name –>Benjamin
Employee Name –>Tom
Employee Name –>Henry
Employee Name –>Robert
Employee Name –>Paul

In general

OPEN cursor
Fetch first record and print the variables
Fetch second record and print the variable
Fetch third record and print the variable
….
Fetch last record and print the variable
CLOSE cursor

Also read –

Cursor Examples
Ref Cursors 
Triggers
Bind Variables

TO_CHAR function Oracle

TO_CHAR is a conversion function in Oracle to convert

1. number to character

2. date to character

Syntax:-

TO_CHAR(value,[format],[nls_language])

Where both format and nls_language are optional. Oracle distinguishes each function by its arguments.

1. TO_CHAR number

TO_CHAR number function is handy for formatting and constructing fixed length outputs.

Examples:-

SQL> select to_char(‘012’) as str from dual;

STR

012

Query Result
to_char(‘012’) 012
to_char(‘012′,’9999’) 12
to_char(‘012′,’$9999.99’) $12.00
to_char(‘012′,’$0009999.99’) $0000012.00
to_char(‘012.48′,’$9999.999’) $12.480

TO_CHAR helps to avoid implicit conversion in some scenarios.

See below examples

SQL> Desc t

Name       Type
—-          ———–
NO           NUMBER(38)
CODE      VARCHAR2(2)

SQL> select * from t;

NO            CODE
—————–
1                  11
2                  22
3                  33

SQL> select * from t where code = 11;

NO            CODE
—————–
1                 11

SQL> select * from t where code = to_char(11);

NO           CODE
—————–
1                 11

First query forces oracle to do implicit conversion ( Number 11 will be converted to ’11’) while second query runs without any implicit conversion. Make both side same data type.

Consider TO CHAR example in PL/SQL

SQL> Create or replace procedure proc(p_code number)
2 is
3 v_no number;
4 begin
5 select no into v_no from t where code = to_char(p_code);
6 dbms_output.put_line(‘No is —>’ || v_no);
7 exception
8 when others then
9 dbms_output.put_line(sqlerrm);
10 end;
11 /

Procedure created.

SQL> exec proc(11);
No is —>1

PL/SQL procedure successfully completed.

For large queries implicit conversion affect the performance. So it is better always keep in mind to avoid implicit conversion as much as possible.

2. TO_CHAR date

TO_CHAR date function is one of the most used functions for date conversions and formatting.

Examples:-

SQL> select sysdate,to_char(sysdate,’mm/dd/yyyy’) as fmt_date from dual;

SYSDATE    FMT_DATE
———      ———-
23-FEB-13  02/23/2013

SQL> select sysdate,to_char(sysdate,’YYTH MONTH YEAR’) as fmt_date from dual;

SYSDATE FMT_DATE
——— ———————————————————
23-FEB-13 13TH FEBRUARY TWENTY THIRTEEN

Below listed most used date formats used with TO_CHAR date function. ( I have used sysdate for example and the day I tested the examples is February 23, 2013 at 4:00 pm PST )

Format Example Result Explanation
YYYY to_char(sysdate,’YYYY’) 2013 Year
YYY to_char(sysdate,’YYY’) 13 Last three digit of the year
YY to_char(sysdate,’YY’) 13 Last two digit of the year
YEAR to_char(sysdate,’YEAR’) TWENTY THIRTEEN Year in words
RR to_char(sysdate,’RR’) 13 Last two digit of the year
RRRR to_char(sysdate,’RRRR’) 2013 Year
SYYYY to_char(sysdate,’SYYYY’) 2013 S prefixed (–) sign for BC
Y,YYY to_char(sysdate,’Y,YYY’) 2013 Year with comma
MONTH to_char(sysdate,’MONTH’) FEBRUARY Complete month
MON to_char(sysdate,’MON’) FEB 3 letter month format
MM to_char(sysdate,’MM’) 02 Month of the year
W to_char(sysdate,’W’) 4 Week of the current month
WW to_char(sysdate,’WW’) 8 Week of the year (1 – 53)
DAY to_char(sysdate,’DAY’) SATURDAY Name of the day
DD to_char(sysdate,’DD’) 23 Day in number format
D to_char(sysdate,’D’) 7 Day of the week (1 – 7)
DDD to_char(sysdate,’DDD’) 54 Day of the year (1 – 366)
DY to_char(sysdate,’DY’) SAT Short form of Day
HH to_char(sysdate,’HH’) 04 Hour (1 – 12)
HH12 to_char(sysdate,’HH12′) 04 Hour in 12 hour format
HH24 to_char(sysdate,’HH24′) 16 Hour in 24 hour format
MI to_char(sysdate,’MI’) 02 Minute (0 – 59)
SS to_char(sysdate,’SS’) 23 Second (0 – 59)
SSSSS to_char(sysdate,’SSSSS’) 57683 Seconds after midnight (0 – 86399)
PM to_char(sysdate,’PM’) PM AM or PM
AM to_char(sysdate,’AM’) PM AM or PM
BC to_char(sysdate,’BC’) AD AD or BC
CC to_char(sysdate,’CC’) 21 Century
DL to_char(sysdate,’DL’) Saturday, February 23, 2013 Date in Long Format
J to_char(sysdate,’J’) 2456347 Julian date format
Q to_char(sysdate,’Q’) 1 Quarter of the Year (1 – 4)

Some more examples

1. select to_char(sysdate,’mm/dd/yyyy’) as today from dual;

TODAY
———-
02/23/2013

2. select to_char(sysdate,’mm/dd/yyyy hh:mi:ss AM’) as today from dual;

TODAY
———————-
02/23/2013 04:12:58 PM

3. select to_char(sysdate,’YEAR’) as today from dual;

TODAY
——————————————
TWENTY THIRTEEN

4. select to_char(sysdate,’mon ddth yyyy’) as today from dual;

TODAY
————-
feb 23rd 2013

 

Recommended read:

Julian Date

 

Oracle IN clause

IN operator uses to specify one or more values in where clause.

Syntax:
select columns from table_name where column_name IN ( values )
Examples:- 

Consider below EMP table structure

Empno Empname Salary Deptno
10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

1. Find all details of Bill, Solomon and Wendy employee.
Select * from EMP where empname INBill’ , ‘Solomon’ ,‘Wendy’);

2. Find details of employee earning highest salary.
Select * from EMP where salary IN (select max(salary) from EMP) ;
3. Find details of employees earning less than highest salary.
Select * from EMP where salary NOT IN (select max(salary) from EMP) ;
Not that NOT IN operator is a negation of IN operator.

DUAL table in Oracle

DUALtable is a special single row table present by default in Oracle Databases.Structure of DUAL table
DUAL table has only one column called DUMMY as Varchar2(1) data type and value as X.

Examples
1) select 1 from DUAL;
returns 1

2) select 1+2 from DUAL;
returns 3

3) select sysdate from DUAL;
returns system date of oracle database.

4) select `sql` from DUAL;
returns`sql`

5) select user from DUAL;
returns oracle user logged in.

6) select 
   (select empno from emp where empno = 10)
   from DUAL;
returns 10

DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)

7) select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.

8) select LOWER(`ORAclE`) from DUAL;
returns`oracle` where LOWER is a in-built SQL function.

DUAL table can be used in SQL and PL/SQL.