Oracle REF CURSORs
Submitted by admin on Wed, 2003-03-05 23:54
articles:
Below is an example of how to setup a REF CURSOR and how to call it from a PL/SQL block.
Why would you want to use REF CURSOR's? REF CURSORs can often provide greater performance when working with data. Much of these comes from the fact that packages are stored into the SHARED POOL of the Oracle database or otherwise known as PINNED to memory.
What I did find interesting was that the process that executes this procedure never needs to know what tables the view touches or even be able to see the view. The procedure simply returns the requested columns.
Also: It would be entirely feasible, using REF CURSOR design, to have queries stored in a LONG column in a table. Remembering that the length of the data cannot exceed 32K. These queries can then be retrieved at package execution time. This could often reduce the number of production moves regarding packages. You could further enhance it by versioning your queries, so that only the most recent would be retrieved.
Provided is an example of a FUNCTION and a PROCEDURE:
PL/SQL Block:
/* Formatted on 2003/01/27 13:22 (Formatter Plus v4.7.0) */
DECLARE
emp_num VARCHAR2 (2000) := '9769,28441';
emp_cur emp_refcur_pkg.c_cursor;
emp_rec hum_emp_career_hist_v%ROWTYPE;
l_counter PLS_INTEGER := 0;
l_start_time DATE;
l_end_time DATE;
l_elapsed_time NUMBER;
l_error NUMBER;
BEGIN
l_start_time := SYSDATE;
emp_refcur_pkg.employeecareer (emp_num, l_error, emp_cur);
IF l_error = 0
THEN
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
l_counter := l_counter + 1;
DBMS_OUTPUT.put_line ('==============');
DBMS_OUTPUT.put_line (l_counter);
DBMS_OUTPUT.put_line ('==============');
DBMS_OUTPUT.put_line ('Employee Number := '
|| emp_rec.employee_number
);
DBMS_OUTPUT.put_line ('Employee Name := ' || emp_rec.employee_name);
DBMS_OUTPUT.put_line ( 'Eff Start Date := '
|| emp_rec.effective_start_date
);
DBMS_OUTPUT.put_line ( 'Eff End Date := '
|| emp_rec.effective_end_date
);
DBMS_OUTPUT.put_line ('Job Title := ' || emp_rec.job_title);
DBMS_OUTPUT.put_line ('Job Code := ' || emp_rec.job_code);
DBMS_OUTPUT.put_line ('COID := ' || emp_rec.coid);
DBMS_OUTPUT.put_line ('UDN := ' || emp_rec.udn);
DBMS_OUTPUT.put_line ('Organization := ' || emp_rec.NAME);
END LOOP;
emp_refcur_pkg.employeecareer_close (emp_cur);
ELSE
DBMS_OUTPUT.put_line ('Error Encountered.');
END IF;
IF l_counter = 0
THEN
DBMS_OUTPUT.put_line ('No Records to Process');
ELSE
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line (l_counter || ' record(s) found.');
END IF;
l_end_time := SYSDATE;
l_elapsed_time := ROUND ((l_end_time - l_start_time) * 24 * 60 * 60, 2);
DBMS_OUTPUT.put_line (l_elapsed_time || ' second(s) execution time.');
EXCEPTION
WHEN OTHERS
THEN
hum_do.default_exception ( 'ERROR :: '
|| 'Employee Number := '
|| emp_rec.employee_number
|| 'Employee Name := '
|| emp_rec.employee_name
|| 'Eff Start Date := '
|| emp_rec.effective_start_date
|| 'Eff End Date := '
|| emp_rec.effective_end_date
);
emp_refcur_pkg.employeecareer_close (emp_cur);
END;
RESULT
============== 1 ============== Employee Number := 9769 Employee Name := Chase, Mr. Barry S Eff Start Date := 16-OCT-2000 Eff End Date := 30-MAY-2002 Job Title := Consulting Systems Engineer Job Code := JE9 COID := 11701 UDN := 920 Organization := 11701/920 Applications Development 7 ============== 2 ============== Employee Number := 28441 Employee Name := Johnson, Ms. Faye M Eff Start Date := 30-DEC-2001 Eff End Date := 31-DEC-4712 Job Title := Systems Engineer Ii Job Code := PF9 COID := 11701 UDN := 920 Organization := 11701/920 Applications Development 7 ============== 3 ============== Employee Number := 28441 Employee Name := Johnson, Ms. Faye M Eff Start Date := 31-DEC-2000 Eff End Date := 31-DEC-2000 Job Title := Systems Engineer I Job Code := PF7 COID := 11701 UDN := 908 Organization := 11701/908 IT Education ============== 4 ============== Employee Number := 28441 Employee Name := Johnson, Ms. Faye M Eff Start Date := 26-JUN-2000 Eff End Date := 30-DEC-2000 Job Title := Associate Systems Engineer Job Code := RW6 COID := 11701 UDN := 908 Organization := 11701/908 IT Education 4 record(s) found.
VIEW
-- Displays Career History in Desc order where most current job history is first rec returned
-- Ordered by FULL_NAME, EMPLOYEE_NUMBER, EFFECTIVE_START_DATE DESC
CREATE OR REPLACE VIEW APPS.HUM_EMP_CAREER_HIST_V
AS /* Formatted on 2003/01/22 12:08 (Formatter Plus v4.7.0) */
SELECT ppf.employee_number, ppf.full_name employee_name
, paf.effective_start_date, paf.effective_end_date
, pjd.segment1 job_title, pjd.segment2 job_code, pca.segment1 coid
, pca.segment2 udn, hou.NAME
FROM apps.per_all_people_f ppf
, hr.per_person_type_usages_f ptu
, hr.per_person_types ppt
, hr.per_jobs pj
, hr.per_job_definitions pjd
, hr.per_periods_of_service pps
, hr.hr_all_organization_units hou
, hr.pay_cost_allocation_keyflex pca
, hr.per_jobs pjp
, hr.per_job_definitions pjdp
, apps.hr_organization_units houp
, hr.pay_cost_allocation_keyflex pcap
, apps.per_all_assignments_f pafp
, apps.per_all_assignments_f paf
WHERE pcap.cost_allocation_keyflex_id(+) = houp.cost_allocation_keyflex_id
AND (pjd.segment1 pjdp.segment1 OR pjdp.segment1 IS NULL)
AND pjdp.job_definition_id(+) = pjp.job_definition_id
AND houp.organization_id(+) = pafp.organization_id
AND pjp.job_id(+) = pafp.job_id
AND pafp.assignment_type(+) = 'E'
AND pafp.primary_flag(+) = 'Y'
AND pafp.effective_end_date(+) < TO_DATE ('31-DEC-4712', 'DD-MON-YYYY')
AND pafp.effective_end_date(+) + 1 = paf.effective_start_date
AND pafp.assignment_id(+) = paf.assignment_id
AND pca.cost_allocation_keyflex_id = hou.cost_allocation_keyflex_id
AND pjd.job_definition_id = pj.job_definition_id
AND ppt.system_person_type IN ('EMP', 'EX_EMP')
AND ppt.person_type_id = ptu.person_type_id
AND paf.effective_start_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = paf.person_id
AND hou.organization_id = paf.organization_id
AND pj.job_id = paf.job_id
AND pps.period_of_service_id = paf.period_of_service_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.person_id = paf.person_id
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y'
ORDER BY ppf.full_name, ppf.employee_number, paf.effective_start_date desc
PACKAGE
/* Formatted on 2003/01/27 13:12 (Formatter Plus v4.7.0) */
CREATE OR REPLACE PACKAGE emp_refcur_pkg
AS
TYPE c_cursor IS REF CURSOR;
/* return Career History for given employee */
PROCEDURE employeecareer (
p_employee_number IN VARCHAR2
, p_error OUT NUMBER
, emp_cur OUT c_cursor
);
/* return org tree with level variable */
PROCEDURE employeeorg_vchild (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_level IN NUMBER
, p_error OUT NUMBER
, emp_cur OUT c_cursor
);
/* return children of submitted employee - only immediate level down*/
PROCEDURE employeeorg_child (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_error OUT NUMBER
, emp_cur OUT c_cursor
);
/* return supervisor of submitted employee */
PROCEDURE employeeorg_supervisor (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_error OUT NUMBER
, emp_cur OUT c_cursor
);
/* return siblings of submitted employee */
PROCEDURE employeeorg_siblings (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_error OUT NUMBER
, emp_cur OUT c_cursor
);
/* use any select statement and return cursor */
PROCEDURE dynsql (
p_sql IN VARCHAR2
, p_error OUT NUMBER
, emp_cur OUT c_cursor
);
/* get person id for provided employee number and eff_date */
FUNCTION get_person_id (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN NUMBER;
/* get primary assignment id for provided employee number and eff_date */
FUNCTION get_primary_asg_id_e (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN NUMBER;
/* get primary assignment id for provided person_id and eff_date */
FUNCTION get_primary_asg_id_p (
p_person_id IN NUMBER
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN NUMBER;
/* get employee number for provided person_id and eff_date */
FUNCTION get_employee_number_p (
p_person_id IN NUMBER
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN VARCHAR2;
/* get employee number for provided assignment_id and eff_date */
FUNCTION get_employee_number_a (
p_assignment_id IN NUMBER
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN VARCHAR2;
/* close cursor */
PROCEDURE employeecareer_close (emp_cur IN OUT c_cursor);
/* close cursor */
PROCEDURE dynsql_close (emp_cur IN OUT c_cursor);
END emp_refcur_pkg;
PACKAGE BODY
/* Formatted on 2003/01/27 13:13 (Formatter Plus v4.7.0) */
CREATE OR REPLACE PACKAGE BODY emp_refcur_pkg
AS
PROCEDURE employeecareer (
p_employee_number IN VARCHAR2
, p_error OUT NUMBER
, emp_cur OUT c_cursor
)
IS
-- EMPLOYEE NUMBER
-- EMPLOYEE NAME
-- EFFECTIVE START DATE
-- EFFECTIVE END DATE
-- JOB TITLE
-- JOB CODE
-- COID
-- UDN
-- ORGANIZATION NAME
stmt VARCHAR2 (2000);
BEGIN
stmt :=
'SELECT * FROM apps.hum_emp_career_hist_v WHERE employee_number IN ('
|| p_employee_number
|| ')';
OPEN emp_cur FOR stmt;
p_error := 0;
EXCEPTION
WHEN OTHERS
THEN
hum_do.default_exception ( CHR (10)
|| 'ERROR IN APPS.EMP_REFCUR_PKG.EMPLOYEE_CAREER'
|| CHR (10)
|| 'Submitted SQL := '
|| stmt
);
p_error := 2;
END employeecareer;
PROCEDURE employeeorg_vchild (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_level IN NUMBER
, p_error OUT NUMBER
, emp_cur OUT c_cursor
)
IS
-- LEVEL
-- EMPLOYEE_NAME
-- EMPLOYEE_NUMBER
-- SUPERVISOR FULL_NAME
-- SUPERVISOR EMPLOYEE NUMBER
BEGIN
OPEN emp_cur FOR
SELECT LEVEL
, LPAD (' ', 2 * LEVEL - 2)
|| emp.employee_name employee_name
, emp.employee_number, emp.supervisor_full_name
, emp.supervisor_number
FROM (SELECT ppf.full_name employee_name, ppf.employee_number
, peosp.full_name supervisor_full_name
, peosp.employee_number supervisor_number
FROM apps.per_assignments_f paf
, apps.per_people_f ppf
, apps.per_people_f peosp
WHERE peosp.person_id(+) = paf.supervisor_id
AND p_eff_date BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+)
AND p_eff_date BETWEEN paf.effective_start_date(+) AND paf.effective_end_date(+)
AND p_eff_date BETWEEN peosp.effective_start_date(+) AND peosp.effective_end_date(+)
AND paf.person_id(+) = ppf.person_id
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y') emp
WHERE LEVEL <= p_level
CONNECT BY PRIOR emp.employee_number = emp.supervisor_number
START WITH emp.employee_number = p_employee_number;
p_error := 0;
EXCEPTION
WHEN OTHERS
THEN
hum_do.default_exception ( CHR (10)
|| 'ERROR IN APPS.EMP_REFCUR_PKG.EMPLOYEEORG_VCHILD'
);
p_error := 2;
END employeeorg_vchild;
PROCEDURE employeeorg_child (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_error OUT NUMBER
, emp_cur OUT c_cursor
)
IS
-- EMPLOYEE_NAME
-- EMPLOYEE_NUMBER
BEGIN
OPEN emp_cur FOR
SELECT ppf.full_name full_name, ppf.employee_number
FROM per_all_assignments_f paf
, per_all_people_f ppf
, (SELECT person_id
FROM per_all_assignments_f
WHERE person_id =
emp_refcur_pkg.get_person_id (p_employee_number
, p_eff_date
)
AND p_eff_date BETWEEN effective_start_date
AND effective_end_date) p_child
WHERE p_eff_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_eff_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.person_id = ppf.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND paf.supervisor_id = p_child.person_id;
p_error := 0;
EXCEPTION
WHEN OTHERS
THEN
hum_do.default_exception ( CHR (10)
|| 'ERROR IN APPS.EMP_REFCUR_PKG.EMPLOYEEORG_CHILD'
);
p_error := 2;
END employeeorg_child;
PROCEDURE employeeorg_supervisor (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_error OUT NUMBER
, emp_cur OUT c_cursor
)
IS
-- EMPLOYEE_NAME
-- EMPLOYEE_NUMBER
BEGIN
OPEN emp_cur FOR
SELECT ppf.full_name full_name, ppf.employee_number
FROM per_all_assignments_f paf
, per_all_people_f ppf
, (SELECT supervisor_id
FROM per_all_assignments_f
WHERE person_id =
emp_refcur_pkg.get_person_id (p_employee_number
, p_eff_date
)
AND p_eff_date BETWEEN effective_start_date
AND effective_end_date) p_sup
WHERE ppf.person_id = paf.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND p_eff_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_eff_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.person_id = p_sup.supervisor_id;
p_error := 0;
EXCEPTION
WHEN OTHERS
THEN
hum_do.default_exception ( CHR (10)
|| 'ERROR IN APPS.EMP_REFCUR_PKG.EMPLOYEEORG_SUPERVISOR'
);
p_error := 2;
END employeeorg_supervisor;
PROCEDURE employeeorg_siblings (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
, p_error OUT NUMBER
, emp_cur OUT c_cursor
)
IS
-- EMPLOYEE_NAME
-- EMPLOYEE_NUMBER
BEGIN
OPEN emp_cur FOR
SELECT ppf.full_name full_name, ppf.employee_number
FROM per_all_assignments_f paf
, per_all_people_f ppf
, (SELECT supervisor_id
FROM per_all_assignments_f
WHERE person_id =
emp_refcur_pkg.get_person_id (p_employee_number
, p_eff_date
)
AND p_eff_date BETWEEN effective_start_date
AND effective_end_date) p_sup
WHERE p_eff_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_eff_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.person_id = ppf.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND paf.supervisor_id = p_sup.supervisor_id;
p_error := 0;
EXCEPTION
WHEN OTHERS
THEN
hum_do.default_exception ( CHR (10)
|| 'ERROR IN APPS.EMP_REFCUR_PKG.EMPLOYEEORG_SIBLINGS'
);
p_error := 2;
END employeeorg_siblings;
PROCEDURE dynsql (
p_sql IN VARCHAR2
, p_error OUT NUMBER
, emp_cur OUT c_cursor
)
IS
stmt VARCHAR2 (32000);
BEGIN
stmt := p_sql;
OPEN emp_cur FOR stmt;
p_error := 0;
EXCEPTION
WHEN OTHERS
THEN
hum_do.default_exception ( CHR (10)
|| 'ERROR IN APPS.EMP_REFCUR_PKG.DYNSQL'
|| CHR (10)
|| 'Submitted SQL := '
|| stmt
);
p_error := 2;
END dynsql;
FUNCTION get_person_id (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN NUMBER
IS
t_value per_all_people_f.person_id%TYPE := 0; -- IF t_value has no value then 0 is returned to signify no data found or error --
BEGIN
SELECT person_id
INTO t_value
FROM per_all_people_f
WHERE p_eff_date BETWEEN effective_start_date AND effective_end_date
AND employee_number = p_employee_number;
RETURN t_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END get_person_id;
FUNCTION get_primary_asg_id_e (
p_employee_number IN VARCHAR2
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN NUMBER
IS
t_value per_all_assignments_f.assignment_id%TYPE := 0; -- IF t_value has no value then 0 is returned to signify no data found or error --
p_person_id PLS_INTEGER;
BEGIN
p_person_id := get_person_id (p_employee_number, p_eff_date);
IF p_person_id = 0
THEN
RETURN 0;
ELSE
SELECT assignment_id
INTO t_value
FROM per_all_assignments_f
WHERE p_eff_date BETWEEN effective_start_date AND effective_end_date
AND primary_flag = 'Y'
AND assignment_type = 'E'
AND person_id = p_person_id;
RETURN t_value;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END get_primary_asg_id_e;
FUNCTION get_primary_asg_id_p (
p_person_id IN NUMBER
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN NUMBER
IS
t_value per_all_assignments_f.assignment_id%TYPE := 0; -- IF t_value has no value then 0 is returned to signify no data found or error --
BEGIN
SELECT assignment_id
INTO t_value
FROM per_all_assignments_f
WHERE p_eff_date BETWEEN effective_start_date AND effective_end_date
AND primary_flag = 'Y'
AND assignment_type = 'E'
AND person_id = p_person_id;
RETURN t_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END get_primary_asg_id_p;
/* get employee number for provided person_id and eff_date */
FUNCTION get_employee_number_p (
p_person_id IN NUMBER
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN VARCHAR2
IS
t_value per_all_people_f.employee_number%TYPE := '0'; -- IF t_value has no value then 0 is returned to signify no data found or error --
BEGIN
SELECT employee_number
INTO t_value
FROM per_all_people_f
WHERE p_eff_date BETWEEN effective_start_date AND effective_end_date
AND person_id = p_person_id;
RETURN t_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN '0';
END get_employee_number_p;
/* get employee number for provided assignment_id and eff_date */
FUNCTION get_employee_number_a (
p_assignment_id IN NUMBER
, p_eff_date IN DATE DEFAULT TRUNC (SYSDATE)
)
RETURN VARCHAR2
IS
t_value per_all_people_f.employee_number%TYPE := '0'; -- IF t_value has no value then 0 is returned to signify no data found or error --
BEGIN
SELECT emp_refcur_pkg.get_employee_number_p (person_id, p_eff_date)
INTO t_value
FROM per_all_assignments_f
WHERE p_eff_date BETWEEN effective_start_date AND effective_end_date
AND assignment_id = p_assignment_id;
RETURN t_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN '0';
END get_employee_number_a;
PROCEDURE employeecareer_close (emp_cur IN OUT c_cursor)
IS
BEGIN
CLOSE emp_cur;
END employeecareer_close;
PROCEDURE dynsql_close (emp_cur IN OUT c_cursor)
IS
BEGIN
CLOSE emp_cur;
END dynsql_close;
END emp_refcur_pkg;»
- admin's blog
- Log in to post comments
Comments
When using Java and Stored
When using Java and Stored Procedures/Functions remember to close everything, that means Connection, ResultSet, and CallableStatement. Cursors don't close themselves implicitly, unless the INIT.ora variable CLOSE_CACHED_OPEN_CURSORS is set to TRUE.
Thanks for the example.
hum_do package
Love this article. Thanks.
By the way where can we find the package hum_do source codes?
If you don't mind, can you please post it out?
Thanks a lot.