Home » SQL & PL/SQL » SQL & PL/SQL » Pl/SQL CURSORS
Pl/SQL CURSORS [message #2199] Thu, 27 June 2002 04:04 Go to next message
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 Go to previous message
Mahesh Rajendran
Messages: 10690
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> 
Previous Topic: how to get year and month from date ?
Next Topic: Userenv getting in my way!
Goto Forum:
  


Current Time: Wed Oct 23 20:33:05 CDT 2019