Collections –VI
Bulk Binding·
Bulk Binds are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements sequentially, all of the operations are carried out at once, in bulk.
· Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.
· BULK_COLLECT and FORALL, together these two features are known as 'Bulk Binding'.
· To do bulk binds with INSERT, UPDATE, and DELETE statements, use FORALL statement.
Syntax:- FORALL index IN lower_bound..upper_bound
sql_statement;
FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine
· To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.
Syntax:-
…BULK COLLECT INTO collection_name[, collection_name] ...
BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine.
You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses

How to find Oracle connection string?

SELECT '&&_CONNECT_IDENTIFIER' FROM dual;
But it works from oracle 9i version onwards only.

in vs exists

In vs Exists which gives better performance depends upon the situation.

The query using in
select * from emp where deptno in (select deptno from dept)
is processed as
select * from emp x , (select distinct deptno from dept) y
where x.deptno = y.deptno

The inner query is first evaluated, distincted, indexed and then joined to the original table.
Table in the subqery is small and table in the main query is large then in usaully makes sense.

Where as in the case of exists
select * from emp a where exists ( select deptno from dept b where a.deptno = b.deptno)
is processed as

for deptno in ( select * from emp )
loop
if ( exists ( select deptno from dept where deptno = emp.deptno )
then
statement
end if
end loop

It always results in a full scan of emp.
If the main table size is small and subquery table size is large then exists usually makes sense.

If both the tables are large then which gives better performance depends upon indexes and other factors.

selecting 5 random records from a table
we can use dbms_random.random to generate random numeric values. It returns binary_integer; ex:- select empno
from
(select empno
from emp
order by dbms_random.random)
where rownum <= 5

Oracle To_date function is used to convert a string to date.

Never use to_date function with a date format.

For example
Select to_date(hiredate) from emp;

Here to_date first converts hiredate to character format and then once again convert it back to date format. Sometimes this may give unexpected results .

In PL/SQL , we can handle warning or error condition in exception handling part. In exception handling part we can handle runtime errors only. we can not catch compile time errors using exceptions.

Reference Cursors - I

Reference cursors are dynamic in nature. In static cursors once the cursor is defined the contents of the cursor’s are fixed. In case of dynamic cursors the contents of cursors can be dynamically changed depending on the requirement. Dynamic cursors can be defined using REF type.

A REF CURSOR is basically a data type. A variable created based on such data type is called cursor variable. A cursor variable can be associated with different queries at run time.

Reference Cursors - II
Example for associating cursor variable with different queries at run time.

Create or replace procedure test_ref as
-- declaring reference cursor
type my_refcursor is ref cursor;
-- declaring cursor variable
my_ref_var my_refcursor;
emp_rec emp%rowtype;
dept_rec dept%rowtype;
begin
--open cursor variable to select from emp table
open my_ref_var for select * from emp;
loop
fetch my_ref_var into emp_rec;
exit when my_ref_var%notfound;
dbms_output.put_line( 'employee name is '||emp_rec.ename);
dbms_output.put_line( 'employee job is '||emp_rec.job);
dbms_output.put_line( 'employee sal is '||emp_rec.sal);
end loop;
close my_ref_var;
--open cursor variable to select from dept table
open my_ref_var for select * from dept;
loop
fetch my_ref_var into dept_rec;
exit when my_ref_var%notfound;
dbms_output.put_line( 'department name is '||dept_rec.dname);
dbms_output.put_line( 'location is '||dept_rec.loc);
end loop;
close my_ref_var;
end;
/

Rreference Cursors – III
A ref cursor can be a strongly typed or weakly typed

If return clause is omitted then it is a weakly typed ref cursor.
Ex:- type my_refcursor is ref cusor.
- This gives greater flexibility
- Increases the likelihood of runtime errors because column mismatches are not picked up at compile time.

Return type is mentioned then it is called strongly typed ref cursor.
Ex:- type my_refcursor is ref cursor return emp%rowtype.
- This reduces the chances of runtime errors since column mismatches are detected at compile time.
- It limits the flexibility of type

The return type of strongly typed ref cursor can be
1.%Rowtype
2.%Type
3.Record structure

We can’t return a scalar type.

Reference Cursors – IV
Example for strongly typed ref cursor using %rowtype

Create or replace procedure test_ref as
type my_refcursor is ref cursor return emp%rowtype;
my_ref_var my_refcursor;
emp_rec emp%rowtype;
begin
open my_ref_var for select * from emp;
loop
fetch my_ref_var into emp_rec;
exit when my_ref_var%notfound;
dbms_output.put_line( 'employee ' ||emp_rec.ename || ' salary is '||emp_rec.sal);
end loop;
close my_ref_var;
end;
/

Reference cursors – V
Example for strongly typed ref cursor using %type

create or replace procedure test_ref as
emp_rec emp%rowtype;
type my_refcursor is ref cursor return emp_rec%type;
my_ref_var my_refcursor;

begin
open my_ref_var for select * from emp;
loop
fetch my_ref_var into emp_rec;
exit when my_ref_var%notfound;
dbms_output.put_line( 'employee ' ||emp_rec.ename || ' salary is '||emp_rec.sal);
end loop;
close my_ref_var;
end;
/

Reference Cursors – VI
Example for strongly typed ref cursor using record
create or replace procedure test_ref as
type emp_recordtype is record
(
ename varchar2(10),
sal number(7,2)
);

type my_refcursor is ref cursor return emp_recordtype; my_ref_var my_refcursor;
emp_recvar emp_recordtype;
begin
open my_ref_var for select ename,sal from emp;
loop
fetch my_ref_var into emp_recvar;
exit when my_ref_var%notfound;
dbms_output.put_line( 'employee ' ||emp_recvar.ename || ' salary is '||emp_recvar.sal);
end loop;
close my_ref_var;
end;
/

Reference Cursors – VII

Differences between a cursor and ref cursor.
1. A cursor is static in nature. Once the cursor is defined the contents of a cursor is fixed. Where as in ref cursors the contents of the cursors can be dynamically changed depending upon the requirement.
2. Ref cursor can be returned to a client where as a cursor cannot be returned to a client.
3. Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)
4. A ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.
5. Static sql (not using a ref cursor) is much more efficient than using ref cursors and that use of ref cursors should be limited to- returning result sets to clients- when there is NO other efficient/effective means of achieving the goal

Reference Cursors – VIII
example for ref cursor passed as a parameter to a procedure
declare type my_refcursor is ref cursor return emp%rowtype; my_refcur my_refcursor;
procedure test_ref (emp_cur in my_refcursor) is
emp_rec emp%rowtype;
begin
loop
fetch emp_cur into emp_rec;
exit when emp_cur%notfound;
dbms_output.put_line(emp_rec.ename ||' is a ' || emp_rec.job);
end loop;
end;
begin
open my_refcur for select * from emp;
test_ref(my_refcur);
close my_refcur;
end;

Reference Cursors – IX

SYS_REFCURSOR

SYS_REFCURSOR is a built-in REF CURSOR type that allows any result set to be associated with it.
SYS_REFCURSOR can be used to:
Delcare a cursor variable in an Oracle stored precedure/function;
Pass cursors from and to an Oracle stored precedure/function

Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different. The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types.

Reference Cursors – X
How to get a refcursor cursor variable out from a function?
--first compile the function.
Create or replace function get_emp_by_dept (v_deptno emp.deptno%type)
return sys_refcursor is
emp_refcur sys_refcursor;
begin
open emp_refcur for select empno, ename from emp where deptno = v_deptno;
return emp_refcur;
end;

--then execute the following block
declare
deptno emp.deptno%type;
empno emp.empno%type;
ename emp.ename%type;
emp_refcur sys_refcursor;
begin
emp_refcur := get_emp_by_dept(10);
dbms_output.put_line('empno ename');
dbms_output.put_line('----- -------');
loop
fetch emp_refcur into empno, ename;
exit when emp_refcur%notfound;
dbms_output.put_line(empno || ' ' || ename);
end loop;
close emp_refcur;
end;

How to view list of privileges?
select * from system_privilege_map;

Difference between IW and WW?
Both IW and WW retruns week of the year.
IW element of the date format gives the ISO week number.

The following are the differences.
- ISO weeks are always seven days long
- The ISO week 1 may start in the end of the previous year
- There may or may not be a week 53
- The last ISO week of the year may contain days from the next year.
whereas
- WW always has week 53
- WW week 53 is 1 or 2 days long
- WW week 1 always starts on 01-JAN
- WW weeks only ever contain days from the year in question.

Observe the difference

select to_char(to_date('31/12/2008','DD/MM/YYYY'),'IW') from dual

select to_char(to_date('31/12/2008','DD/MM/YYYY'),'WW') from dual

You must include an actual parameter for any IN OUT parameters, even if they have default values.

Inside a package you can't reference host variables.