By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,686 Members | 1,510 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Oracle REF CURSOR - 3

debasisdas
Expert 5K+
P: 8,127
RESTRICTIONS ON CURSOR VARIABLES
=================================
Currently, cursor variables are subject to the following restrictions:
Cannot declare cursor variables in a package spec.

Expand|Select|Wrap|Line Numbers
  1. CREATE PACKAGE emp_stuff AS
  2. TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  3. emp_cv EmpCurTyp; -- not allowed
  4. END emp_stuff;
  5.  
Cannot pass cursor variables to a procedure that is called through a database link.
If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.
Cannot use comparison operators to test cursor variables for equality,inequality, or nullity.
Cannot assign nulls to a cursor variable.
Database columns cannot store the values of cursor variables. There is no equivalent type to use in a CREATE TABLE statement.
Cannot store cursor variables in an associative array, nested table, or varray.
Cursors and cursor variables are not interoperable; that is,cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor FOR loop.


Sample Program---- Fetching from a Cursor Variable into Collections
==================================================
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE EmpCurTyp IS REF CURSOR;
  3. TYPE NameList IS TABLE OF emp.ename%TYPE;
  4. TYPE SalList IS TABLE OF emp.sal%TYPE;
  5. emp_cv EmpCurTyp;
  6. names NameList;
  7. sals SalList;
  8. BEGIN
  9. OPEN emp_cv FOR SELECT ename,sal FROM emp WHERE sal < 3000;
  10. FETCH emp_cv BULK COLLECT INTO names, sals;
  11. CLOSE emp_cv;
  12. FOR i IN names.FIRST .. names.LAST
  13. LOOP
  14. dbms_output.put_line('Name = ' || names(i) || ', salary = ' ||
  15. sals(i));
  16. END LOOP;
  17. END;
  18.  
Restrictions on Cursor Expressions
============================
1.Cannot use a cursor expression with an implicit cursor.
2.Cursor expressions can appear only:
In a SELECT statement that is not nested in any other query expression, except
a.When it is a subquery of the cursor expression itself.
b.As arguments to table functions, in the FROM clause of a SELECT statement.
3.Cursor expressions can appear only in the outermost SELECT list of the query specification.
4.Cursor expressions cannot appear in view declarations.
5.Cannot perform BIND and EXECUTE operations on cursor expressions.


Sample Example of Cursor Expressions
==============================
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE emp_cur_typ IS REF CURSOR;
  3. emp_cur emp_cur_typ;
  4. dept_name dept.dname%TYPE;
  5. emp_name emp.ename%TYPE;
  6. CURSOR c1 IS SELECT
  7. dname, CURSOR
  8. (
  9. SELECT e.ename FROM emp e
  10. WHERE e.deptno = d.deptno
  11. ) employees
  12. FROM dept d
  13. WHERE dname like '%A%';
  14. BEGIN
  15. OPEN c1;
  16. LOOP
  17. --fetch the cursor into target variables.
  18. FETCH c1 INTO dept_name, emp_cur;
  19. EXIT WHEN c1%NOTFOUND;
  20. dbms_output.put_line('Department: ' || dept_name);
  21. LOOP
  22. FETCH emp_cur INTO emp_name;
  23. --exit when there is no more data in the cursor.
  24. EXIT WHEN emp_cur%NOTFOUND;
  25. dbms_output.put_line(' Employee: ' || emp_name);
  26. END LOOP;
  27. END LOOP;
  28. CLOSE c1;
  29. END;
  30.  
May 29 '07 #1
Share this Article
Share on Google+