CURSORS

[NitinJain]

Hello,
I was asked to use REF CURSORS to solve the following problem, unfortunately I am still not able to figure it out.

The select statement in the function may return more than 1 value. I want all these values to be returned when this function is called from the java adaptors.

Will

CREATE OR REPLACE FUNCTION getParentNodes (nodeId NUMBER) RETURN NUMBER???****???? IS
=================
CURSOR cr_ParentNodes IS

SELECT parent_node_id
FROM t_arch
WHERE specified_in_dom = 1 AND
child_node_id = nodeId;

parentNodeId cr_ParentNodes%ROWTYPE;
BEGIN

OPEN cr_ParentNodes;

LOOP
FETCH cr_ParentNodes INTO parentNodeId;
EXIT WHEN cr_ParentNodes%NOTFOUND;
END LOOP;

CLOSE cr_ParentNodes;

RETURN parentNodeId.parent_node_id;


END getParentNodes;

be able to handle more than one value and return them to the java program.


Regards.

 

[jee]

There is a very good discussion, with examples, of how to use ref cursors in the Oracle PL/SQL guide in Chapter 5, "Interaction with Oracle". This discussion is in the subsection called "Using Cursor Variables".

The procedure is to declare a variable as a cursor and then pass this variable to a procedure which sets up the query used to define the dataset. The cursor variable must be an IN OUT parameter, so you have to use a stored procedure, not a function. Here is one of the examples from the manual:

CREATE PACKAGE emp_data AS
...
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;

CREATE PACKAGE BODY emp_data AS
...
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
END emp_data;

You can access all the Oracle 8i manuals online at the Oracle Technology Network site - you have to sign up, but it is free - URL for documentation is:

http://otn.oracle.com/docs/products/oracle8i/content.html