| Pl/SQL CURSORS [message #2199] |
Thu, 27 June 2002 04:04  |
Nitin Jain
Messages: 7
Registered: October 2001
|
Junior Member |
|
|
Hello!
I want to create a function which might return more than one value using cursor. I have given the example below.
Regards.
=======================================================
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;
IF cr_ParentNodes%NOTFOUND THEN raise_application_error(-20206, 'Node ' || nodeId || ' doesn't have a parent node in the database');
END IF;
EXIT WHEN cr_ParentNodes%NOTFOUND;
END LOOP;
CLOSE cr_ParentNodes;
RETURN ??????????????/
END getParentNodes;
|
|
|
|
| Re: Pl/SQL CURSORS [message #2201 is a reply to message #2199] |
Thu, 27 June 2002 04:59  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
is this helping you?
SQL> get fu_resultset
1 Create or replace package Package_resultset is
2 Type empRC is ref cursor return emp%rowtype;
3 function Function_resultset return empRC;
4 End;
5 /
6 create or replace package body Package_resultset is
7 function Function_resultset return empRC is
8 local_cursor empRC;
9 BEGIN
10 open local_cursor for select * from emp where deptno=10;
11 return local_cursor;
12 END;
13 END;
14* /
15 .
SQL> @fu_resultset
Package created.
Package body created.
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
