, , , , , , ,

Cursors In Oracle

CURSOR : A cursors is a pointer used to fetch rows from a result set 
Two types of classification s:


I.STATIC CURSOR S: 
Static : Normal cursor (implicit or explicit)

Cursor attributes  for implicit and explicit:

%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open

a. Implicit : 
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)

Example Implicit Cursor:

select * from emp

If SQL%FOUND then

v_count:= SQL%ROWCOUNT

end if;


b. Explicit : 2 network round trips. Store data first then retrieve data. 
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.

Syntax:
open c1; — cursor c1 is select <stmt>

fetch <>

exit when c1%NOTFOUND

Example Explicit cursor:

Without Using Loop s
Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

open cur1;

fetch cur1 into v_ename,v_empno,v_sal;

exit when cur1%notfound;

—<do processing>

close cur1;

end;

Using Loops:

Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

Using Loops with Cursor Parameters:

Declare

Cursor cur1( cp_deptNo Number) 
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo

l_deptNo Number :=50;
begin

For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

II. DYNAMIC CURSOR s : 

Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures. 

a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.

Example For the Ref Cursor :

–SPECK PACKAGE 
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
 AS
  TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
  TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

END REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:


–BODY PACKAGE 
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor: 
PROCEDURE 
WEAK_REF_CUR_PRC( p_deptno IN number,
                  p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
                  —- From 9i (p_cursor OUT SYS_REFCURSOR )—-
                 )
IS

BEGIN

  OPEN p_cursor FOR
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
end WEAK_REF_CUR_PRC;

— For Strong Ref Cursor: 
PROCEDURE 
STRONG_REF_CUR_PRC( p_deptno IN number,
                    p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
                  )
IS

BEGIN
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
  end STRONG_REF_CUR_PRC;
 END REFCURSOR_PKG;
/by
, , , ,

Bulk Collect in Oracle PL/SQL

We usually use cursor for loops to process data.(i.e declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch) statements in plsql programs causes a context switch between the plsql engine and the sql engine.Too many context switches may degrade performance dramatically.

In order to reduce the number of these context switches we can use bulk collecting feature
Bulk collecting lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk collecting is available for select, insert, delete and update statements.

Below are some examples:

create table BULK_COLLECT_TEST as select * from PER_ALL_PEOPLE_F;

Table created.

insert into BULK_COLLECT_TEST

select * from BULK_COLLECT_TEST;

20000 rows created.

–BLOCK1:Using Loops
declare
 cursor c1
 is select object_name from BULK_COLLECT_TEST;
 rec1 c1%rowtype;
 begin
      open c1;
       loop
       fetch c1 into rec1;
    exit when c1%notfound;
    null;
    end loop;
 end;

total Elapsed Time is : 45 Secs

–BLOCK2: Using Bulk Collecting
declare
  cursor c1 is select object_name from BULK_COLLECT_TEST;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
begin
open c1;
   fetch c1 bulk collect into rec1;
end;

total Elapsed Time is : 5 Sec

So bulk collecting the rows shows a huge performance improvement over fetching row by row.

Some cases there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again.
Otherwise process memory gets bigger and bigger as you fetch the rows.

–Bulk Collect Example using LIMIT :
declare
 cursor c1 is select object_name from BULK_COLLECT_TEST;
 type c1_type is  table of c1%rowtype;
 rec1 c1_type;
begin
    open c1;
    loop
    fetch c1 bulk collect into rec1 limit 200;
    for i in 1..rec1.count loop
    null;
    end loop;
    exit when c1%notfound;
    end loop;
end;

/by
, , , , , ,

Submitting Concurrent Program From Back end / PL SQL

Pre-requisites :
Step1: Data Definition and Template to be created
Step2: Concurrent program needs to be created

Steps To Create the PL/SQL package:

1. Initialize the  Session Specific variable using fnd_global.APPS_INITIALIZE
2. Set The BI publisher report layout Before submitting the concurrent program
3. Submit the Concurrent Program

Code: (Tested in R12.1.1 )

DECLARE
l_user_id              fnd_user.user_id%TYPE;
l_resp_id              fnd_responsibility.responsibility_id%TYPE;
l_resp_appl_id         fnd_application.application_id%TYPE;
l_set_layout           boolean;
l_request_id           NUMBER;
l_phase                VARCHAR2 (100);
l_status               VARCHAR2 (100);
l_dev_phase            VARCHAR2 (100);
l_dev_status           VARCHAR2 (100);
l_wait_for_request     boolean := FALSE;
l_get_request_status   boolean := FALSE;
Output_layout_failed EXCEPTION;
request_submission_failed EXCEPTION;
request_completion_abnormal EXCEPTION;
BEGIN
l_request_id := NULL;

—
— Get the Apps Intilization Variables
—
SELECT   fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO   l_user_id, l_resp_id, l_resp_appl_id
FROM   fnd_user fnd, fnd_responsibility_tl fresp
WHERE   fnd.user_name = ‘OEAG’
AND fresp.responsibility_name = ‘Custom XML Reports’;

—
–Initialize the Apps Variables
—
fnd_global.APPS_INITIALIZE (user_id        => l_user_id,
resp_id        => l_resp_id,
resp_appl_id   => l_resp_appl_id);

COMMIT;

—
— Set the Layout  for BI Publisher Report
—

l_set_layout :=
fnd_request.add_layout (template_appl_name   => ‘XXERP’,
template_code        => ‘XXORACLEERPAPPSGUIDE’,
–Data Template Code
template_language    => ‘en’,
template_territory   => ‘US’,
output_format        => ‘PDF’);

IF l_set_layout
THEN
— Submit the Request

l_request_id :=
fnd_request.submit_request (application   => ‘XXERP’,
program       => ‘XXOEAG_PG’,
description   => ”,
start_time    => SYSDATE,
sub_request   => FALSE,
argument1     => l_person_id);

COMMIT;

IF l_request_id > 0
THEN
—
–waits for the request completion
—

l_wait_for_request :=
fnd_concurrent.wait_for_request (request_id   => l_request_id,
interval     => 60,
max_wait     => 0,
phase        => l_phase,
status       => l_status,
dev_phase    => l_dev_phase,
dev_status   => l_dev_status,
MESSAGE      => l_messase);

COMMIT;

—
— Get the Request Completion Status.
—
l_get_request_status :=
fnd_concurrent.get_request_status (
request_id       => l_request_id,
appl_shortname   => NULL,
program          => NULL,
phase            => l_phase,
status           => l_status,
dev_phase        => l_dev_phase,
dev_status       => l_dev_status,
MESSAGE          => l_messase
);

—
–Check the status if It IS completed Normal Or Not
—
IF UPPER (l_dev_phase) != ‘COMPLETED’
AND UPPER (l_dev_status) != ‘NORMAL’
THEN
RAISE request_completion_abnormal;
END IF;
ELSE
RAISE request_submission_failed;
END IF;
ELSE
RAISE Output_layout_failed;
END IF;

p_request_id := l_request_id;
EXCEPTION
WHEN Output_layout_failed
THEN
DBMS_OUTPUT.put_line (‘Out put Layout failed’);
WHEN request_submission_failed
THEN
DBMS_OUTPUT.put_line (‘Concurrent request submission failed’);
WHEN request_completion_abnormal
THEN
DBMS_OUTPUT.put_line (
‘Submitted request completed with error’ || l_request_id
);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘ERROR:’ || SUBSTR (SQLERRM, 0, 240));
END;

/

You can Create this as PL/SQL Procedure and register into Concurrent Program also.

/by
, , , ,

ERRBUF and RETCODE in Concurrent Program

For each Concurrent program we must use mandatory parameters : ERRBUF and RETCODE.

ERRBUF: It return the error message. 
For you program if you get any error in exception block you can assign the error message to this parameter. 
This error message you can see after concurrent program run go to details button it will open details in that Completion Text filed will show your errbuf.

RETCODE: This parameter returns the status of the concurrent program.
0- Success –Completed
1- Warning — Yellow color
2- Error — Red

These parameters we call as a first parameters for the program.

Ex:
Create procedure  CProgram(ERRBUF out varchar2, RETCODE  out varchar2, person_id in NUmber)
as
begin
<Declaration Goes  Here>
begin
<Code Block1 Here>

exception
when no_data_found then
retcode := 1;
errbuf:= ‘RetCode : Warning’;
end;
…
<Code Block2 Here>
…
retcode:= 0;
commit;
exception
when others then

retcode := 2;

errbuf:= ‘errbuf:= ‘RetCode : Error’;

end;
/by
, , , ,

What are the different types of Oracle Exceptions?

The main classification is Oracle Defined and User defined Exceptions

User Defined:

User defined exceptions are defined in many ways.

a.New user defined Exceptions can be created using existing Oracle defined Exceptions

b.Entirely new Exceptions can be according to a users need. 
Eg: Raise an exception if employee salry should not be negative value.

There are mainly 3 ways to use User Defined Exceptions

A. RAISE EXCEPTION

declare
exc_user Exception; –declare exception
begin
–code–
exception when others then
raise exc_user;
–exception raised
exception
when exc_user then
–handler for exc_user. exception handled
when others then
–handler for others
end;

B. RAISE_APPLICATION_ERROR

declare
exc_user Exception; –declare exception
begin
if (<logic>) then
RAISE_APPLICATION_ERROR(-20001, exc_user);
–code greater than -20000
exception
when exc_user then
 –handled when error occurs with the specified Oracle Code
when others then
–handler for others

C. PRAGMA EXCEPTION_INIT

declare
exc_user Exception; –declare exception
PRAGMA EXCEPTION_INIT(exc_user, -<oracle_error_code>); 
— Oracle code with ‘-‘sign
begin
–code–
exception
when exc_user then
–handler for exc_user –handled when error occurs with the specified Oracle Code
when others then
–handler for others

Oracle defined:

There are many built in exceptions which we can use. Most commonly used ones are:

a.NO_DATA_FOUND
b.TOO_MANY_ROWS_FOUND
c.ZERO_DIVIDE
d.CURSOR_ALREADY_OPEN
e.INVALID_CURSOR
f.DUP_VALUE_ON_INDEX
g.VALUE_ERROR
h.INVALID_NUMBER

Difference between Value Error and Invalid Number
See below are the examples:

Eg1:
SQL> select to_number (‘a’)
  from dual

Error :ORA-01722: invalid number

Eg2:

SQL>   declare
       n number;
   begin
      n := ‘a’;
    exception
     when value_error
    then
     dbms_output.put_line (‘Value Error’);
   end;

 
Error: Value Error

Eg3:

SQL> declare
     n number;
    begin
         select ‘a’ into n
         from dual ;
     exception
     when value_error then
     dbms_output.put_line (‘Value Error’);
    end;


Error: Value Error

Eg4:
SQL> declare
     n number;
    begin
        select to_number(‘a’) into n
        from dual;
    exception
    when value_error  then
    dbms_output.put_line (‘Value Error’);
    when invalid_number then
    dbms_output.put_line (‘Invalid Number’);
 end;

Error:Invalid Number
/by