How to execute a cursor from SQL prompt.

[NitinJain]

Hello,

I have defined the following function getParentNodes in the package t_Nodes. When this function is execute, it should return more than one row.

====================

CREATE OR REPLACE PACKAGE t_Nodes IS
TYPE parentNodeId IS REF CURSOR;
FUNCTION getParentNodes(nodeId NUMBER) RETURN parentNodeId;
END t_Nodes;


CREATE OR REPLACE PACKAGE BODY t_Nodes IS

FUNCTION getParentNodes(nodeId NUMBER) RETURN parentNodeId IS
cr_ParentNodes parentNodeId ;

BEGIN
OPEN cr_ParentNodes FOR SELECT parent_node_id
FROM t_arch
WHERE specified_in_dom = 1 AND
child_node_id = nodeId;

IF cr_ParentNodes%NOTFOUND THEN
raise_application_error (-20209, 'Node ' || nodeId || ' does not have parent node in the database');
END IF;

RETURN cr_ParentNodes;

END getParentNodes;
END t_Nodes;

=============

I was trying to execute it from the SQL prompt using

SET SERVEROUT ON

BEGIN

dbms_output.put_line( t_Nodes.getParentNodes(9));

END;

By doing so I was getting the following error:

ERROR at line 3:
ORA-06550: line 3, column 2:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 3, column 2:
PL/SQL: Statement ignored


Please can anyone suggest me something to solve this problem.

Warm Regards.

 

[sem]

You should declare host bind variable of REFCURSOR type and then PRINT it:

var rc REFCURSOR
EXEC :rc :=t_Nodes.getParentNodes(9)
print rc