Sep 25, 2015

How to test a oracle stored procedure with ref cursor from SQL*PLUS or SQL Developer

Oracle stored procedure is one kind of PL/SQL program unit. Consider the following cases :
1. stored procedure with scalar type - NUMBER, VARCHAR
2. stored procedure with REF CURSOR or SYS_REFCURSOR. What is difference between REF CURSOR and SYS_REFCURSOR?

Execute/Test stored procedure with primitive type:-

Step 1:- Create a stored procedure with primitive type.Below is sample code for the same ignore this step if you already have some stored procedure created. Execute it, SQL*PLUS/SQL Develope worksheet.
create or replace procedure get_employee_total_saalry 
(
  base_salary IN NUMBER 
, bonus  IN NUMBER 
, total_sal OUT NUMBER 
) as 
begin
  total_sal :=base_salary + .12*base_salary + bonus; 
end get_employee_total_saalry;

Step 2:- Create anonymous block to execute the stored procedure.Execute following blocks and see the output as total salary.
--Stored procedure having primitive type as paraemter 
declare
type rc is ref cursor;
v_base_sal NUMBER(5) := 1000;
v_bonus NUMBER(5)  := 2300;
v_total NUMBER(10) ;
begin
--call stored procedure with two IN and one OUT type parameter
HR.get_employee_total_saalry(v_base_sal,v_bonus,v_total);
    dbms_output.put_line(' Total sal is ' ||v_total);
end;

Execute/Test stored procedure with REF_CURSOR/SYS_REFCURSOR type:-

Step 1:- Create a stored procedure  with REF CURSOR. Below is the sample code for the same, ignore this step if you already have procedure created.
create or replace procedure get_employees_name(
v_deptId_in NUMBER,
v_cur OUT SYS_REFCURSOR ) 
IS
begin
  open v_cur for select FIRST_NAME,LAST_NAME from
        employees where DEPARTMENT_ID = v_deptId_in;
end get_employees_name;

Step 2:- Now write execution block from where program unit with REF CURSOR is tested.
--using refcursor --
declare
-declare ref cursor type 
type rc is ref cursor;
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur rc; -declare ref cursor variable
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,v_cur);
dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
dbms_output.put_line('---------------------------------');
LOOP
    FETCH v_cur INTO v_fname, v_lname;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(v_fname || '     ' || v_lname);
  END LOOP;
  CLOSE v_cur;
end;

Using SYS_REFCURSOR also, we can write program blocks to test same stored procedure. (Remember, SYS_REFCURSOR is just a predefined weak REF CURSOR, so we do not have to declare a cursor type like "type rc is ref cursor". Following is testing program(differences from above program has been highlighted):
declare
--type rc is ref cursor; NOT required 
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur SYS_REFCURSOR; --rc replaced with predefined type SYS_REFCURSOR
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,v_cur);
dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
dbms_output.put_line('---------------------------------');
LOOP
    FETCH v_cur INTO v_fname, v_lname;
    EXIT WHEN v_cur%NOTFOUND;
    
    dbms_output.put_line(v_fname || '      ' || v_lname);
  END LOOP;
  CLOSE v_cur;
end;

Alternate way execute stored procedure with REF_CURSOR/SYS_REFCURSOR type:-

Declare the cursor variable of type refcursor and run stored procedure with exec. In SQL developer/SQL*PLUS it can be executed like this :-
--cursor variable declaration 
variable v_emp_cur refcursor;
--execute procedure 
execute get_employees_name(90,:v_emp_cur);
--display result referenced by ref cursor.
print v_emp_cur;

=====End of post=======

Read : Difference between SYS_REFCURSOR and REF CURSOR.
Location: Hyderabad, Telangana, India

5 comments:

  1. Great efforts put to publish these kinds of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create great relations. You’re doing an excellent job. Keep it up.

    Magento Development Training Course in Chennai Zuan Education

    Selenium Training Course in Chennai Zuan Education

    ReplyDelete
  2. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    Digital Marketing Course In Kolkata

    ReplyDelete
  3. Great efforts put to publish these kinds of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create great relations. You’re doing an excellent job. Keep it up.

    Magento Development Training Course in Chennai Zuan Education

    Selenium Training Course in Chennai Zuan Education

    ReplyDelete