result set or all rows fetched , sys_refcursor

SQL Optimizer SQL Optimizer for Oracle
1

Please could you help me?

I need to print the result set or all rows fetched about the query:

CREATE OR REPLACE
PROCEDURE get_emp_rs
(p_deptno IN hr.employees.department_id%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT first_name,
salary,
department_id
FROM hr.employees
WHERE department_id = p_deptno
ORDER BY first_name;
END get_emp_rs;
/

declare
v varchar2(4000);
z varchar2(4000);
begin
z:=get_emp_rs( 90, v);
dbms_output.put_line(z);
end;
/

ORA-06550: line 5, column 6:

PLS-00222: no function with name ‘GET_EMP_RS’ exists in this scope
ORA-06550: line 5, column 3:

I need the result set for example:

Steven 24000 90
Neena 17000 90
Lex 18000 90

Thanks a lot :slight_smile:

2

Try this. Hope it helps.

declare
c SYS_REFCURSOR;
l_first_name hr.employees.first_name%type;
l_salary hr.employees.salary%type;
l_department_id hr.employees.department_id%type
begin
get_emp_rs( 90, c);
loop
fetch c into l_first_name, l_salary, l_department_id;
exit when c%notfound;
dbms_output.put_line(l_first_name || ’ ’ || to_char(l_salary) || ’ ’ || l_department_id);
end loop;
close c;
end;
/

3

Thank you very much

You are very kind

Is it possible to close the cursor somewhere in the procedure?

close p_recordset;

CREATE OR REPLACE
PROCEDURE get_emp_rs
(p_deptno IN hr.employees.department_id%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT first_name,
salary,
department_id
FROM hr.employees
WHERE department_id = 90
ORDER BY first_name;
close p_recordset; – maybe I’m crazy
END get_emp_rs;

Finally script output

ORA-01001: invalid cursor

4

You won’t be able to fetch the data once the cursor is close. If you want the cursor to open and close in the same procedure, you can move the code to output data into the same procedure. Or, you will need some other types of storage (e.g. a table) to keep the data for later access.

Thanks,

Alex