Home » SQL & PL/SQL » SQL & PL/SQL » Ref cursor
Ref cursor [message #251913] Tue, 17 July 2007 02:34 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
DECLARE
TYPE numType IS REF CURSOR;
numTypeCur numType;
var NUMBER DEFAULT 10;
rec samp_emp%ROWTYPE;
BEGIN
IF var=20 THEN
OPEN numTypeCur FOR SELECT * FROM samp_emp;
ELSE
OPEN numTypeCur FOR SELECT * FROM emp ;
END IF;
LOOP
FETCH numTypeCur INTO rec;
dbms_output.put_line(rec.emp_id);
EXIT WHEN numTypeCur%NOTFOUND;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;


suppose if i wants to use parameterised cursor in this ref cursor how to do?
Re: Ref cursor [message #254687 is a reply to message #251913] Fri, 27 July 2007 08:54 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry for the extreme late reply, I don't know whether you're still waiting for it. Something like this perhaps:
set serverout on

DECLARE
   TYPE numtype IS REF CURSOR;

   numtypecur   numtype;
   var          NUMBER             DEFAULT 10;
   rec          departments.department_id%type;
BEGIN
   OPEN numtypecur FOR 'SELECT department_id FROM '
                       || CASE var 
                          WHEN 20 THEN 
                          	'employees'
                          ELSE
                          	'departments'
                          END;
   LOOP
      FETCH numtypecur
      INTO  rec;

      DBMS_OUTPUT.put_line (rec);
      EXIT WHEN numtypecur%NOTFOUND;
   END LOOP;
   
   CLOSE numtypecur;
EXCEPTION
   WHEN OTHERS
   THEN
   	  IF numtypecur%ISOPEN THEN
   	  	CLOSE numtypecur;
   	  END IF;
   	  	
      DBMS_OUTPUT.put_line (SQLERRM);
      -- consider a RAISE.
END;
/


Make sure all open cursors are closed.

MHE
Previous Topic: trigger problem
Next Topic: is it possible to write "ORDER BY QUARTERLY"
Goto Forum:
  


Current Time: Fri Dec 09 08:00:13 CST 2016

Total time taken to generate the page: 0.20760 seconds