Oracle stored procedures that return ref cursor how to display?

CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
IO_CURSOR IN OUT T_CURSOR);
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR);
END CURSPKG;
/


CREATE OR REPLACE PACKAGE BODY CURSPKG AS
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
IO_CURSOR IN OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
IF N_EMPNO <> 0
THEN
OPEN V_CURSOR FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = N_EMPNO;

ELSE
OPEN V_CURSOR FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

END IF;
IO_CURSOR := V_CURSOR;
END OPEN_ONE_CURSOR;

PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR)
IS
V_CURSOR1 T_CURSOR;
V_CURSOR2 T_CURSOR;
BEGIN
OPEN V_CURSOR1 FOR SELECT * FROM EMP;
OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
EMPCURSOR := V_CURSOR1;
DEPTCURSOR := V_CURSOR2;
END OPEN_TWO_CURSORS;
END CURSPKG;
/
The above is built and stored procedure.

Below is the call - in many ways are not
declare
type emp_cursor is ref cursor;
type dept_cursor is ref cursor;
v_emp emp_cursor;
v_dept dept_cursor;
begin
scott.curspkg.OPEN_TWO_CURSORS(v_emp,v_dept);
for rec in v_emp loop
dbms_output.put_line(rec.empno);
end loop;
end;
Always prompt report early not to v_emp if the function might be able to solve. But this is a stored procedure, see the post as the stored procedure with countless silly B function
Really speechless, online for an expert to solve!

Started by Beata at February 03, 2016 - 8:34 AM

Online for solution! Really depressed

Posted by Beata at November 19, 2016 - 2:43 AM

How even a person all have no!

Posted by Beata at November 24, 2016 - 3:15 AM

Expert Hugh Sunday go

Posted by Matilda at November 30, 2016 - 3:37 AM

SQL code
create or replace package pa_query as
type p_cursor is ref cursor;
end pa_query;
/
create or replace procedure p_query(v_cursor out pa_query.p_cursor)
is
begin
open v_cursor for select * from emp;
end p_query;

Have a look this to you?




Posted by Matilda at December 07, 2016 - 4:26 AM

Thank you for your reply, this I know, but I mean if a stored procedure is not cursor to pass it out how to get
In fact, there are many ways, but it is not by ref cursor. What are not. But recently found a post don't know is it right? He said but seems reasonable!

Posted by Beata at December 11, 2016 - 5:09 AM

This is what he said:
4 (return data set)
2010-11-17 11:59

Question: how to make the process of return data set?

Answer:

There are two key parameters: one is the use of out type. Two is the use of special data types of parameters: system reference cursor type(sys_refCursor).

The process of return data set, can be received by another block or procedure in the PL/SQL program,

Question: cursor type and sys_refCursor type have what distinction?

Answer:

Cursor is the cursor type, representative is the data set itself, can only be turned off in the PL/SQL program; through the cursor FOR loop or the definition of cursor to complete the.

While sys_refCursor is representative of the vernier references, namely data set of reference address, only through the open for statement to complete the.

The biggest difference between the two is that the latter can be transferred to other programs that address.

Question: it is said that "ref cursor" can return data set, what is the relationship with sys_refCursor?

Answer: ref cursor is the use of Oracle in the old version of data types, this object is that must be defined in the package; and now use ys_refCursor type, can be used directly.

The synthetic example

Requirements: creating process (department number: add_sal), a wage increase 10% to the designated staff. Then return to this department the following staff information: empno,ename,sal.

The process of code

create or replace procedure add_sal(

p_dept dept.deptno%type,

p_data out sys_refcursor)

as

begin

update emp set sal=sal+sal*0.1 where deptno=p_deptno;

commit;

open p_data for

select empno,ename,sal from emp where deptno=p_deptno;

end;

Call a: call in PL/SQL program

declare

v_data sys_refcursor;

v_empno emp.empno%type;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

add_sal(10,v_data);

fetch v_data into v_empno,v_ename,v_sal;

while(v_data%found)

loop

dbms_output.put_line(v_empno||','||v_ename||','||v_sal);

fetch v_data into v_empno,v_ename,v_sal;

end loop;

close v_data;

end;

Call two: call in JDBC

CallableStatement cstmt = null;

String spName = "{call add_sal(?,?)}";

cstmt = conn.prepareCall(spName);

cstmt.setInt(1, "10");

-- must be registered Oracle CURSOR types

cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

-- will be cited into the result set cursor type

ResultSet rs = (ResultSet)cstmt.getObject(2);

The following slightly.

Summary:

1, What is sys_refcusor? Why use sys_refcusor type?

Answer: sys_refcusor is called the "system reference cursor", through this type can make the process transfer data set to the external procedure.
2, : What is its essential distinction with cursor?
Answer: cursor must be closed immediately in a PL/SQL block, sys_refcusor can transfer the data set address, which is equivalent to transfer the data set.

3, There are several ways to query multiple rows of data?

Answer: 3, a: the cursor object, the for statement, open for statement.

4, What is called the system uses a cursor variable?

Answer: the implementation of open x for query; query can be a static SQL, also can be a string, which X called "system uses a cursor variable", must be defined as sys_refCursor.

Posted by Beata at December 24, 2016 - 6:03 AM

I own to knot paste! Ha ha ha I still pretty deep column!

Posted by Beata at January 03, 2017 - 6:24 AM

I look up to here to solve the problem, the landlord cattle.

Posted by Gabriel at January 13, 2017 - 7:43 AM

-- calling a stored procedure
declare
TestCursor SYS_REFCURSOR;- definition system cursor
V_ listed the table name,%type.;
V_ listed 2 table name,%type.;
begin
test(TestCursor);-- the name of the stored procedure
loop
begin
Fetch TestCursor into v_ column, v_ column 2;
exit when TestCursor%notfound;
DBMS_OUTPUT.put_line(v_, Column names ||','||v_ listed 2);
end;
end loop;
end;

Posted by Kate at January 15, 2017 - 7:23 AM