473,327 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,327 developers and data experts.

Oracle REF CURSOR - 3

debasisdas
8,127 Expert 4TB
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
0 17989

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: duikboot | last post by:
Hi all, I'm trying to export a view tables from a Oracle database to a Mysql database. I create insert statements (they look alright), but it all goes wrong when I try to execute them in Mysql,...
3
by: IGotYourDotNet | last post by:
Can anyone point me to some example ASP.NET apps that use Oracle has a backend and C# as the language? I need to figure out how to populate a drop down, and then a datagrid depending on what the...
2
by: André Nobre | last post by:
I don't know if this is the right place to make this question, so, if isn't, let me know... I have an oracle package with some procedures, and i need to access one procedure using vb.net. The...
6
by: JV | last post by:
A ref cursor data type can obviously be returned as an output parameter of a stored procedure, but can an ASP.NET application call an oracle proc that uses a ref cursor as an input parameter? If...
1
by: Chad | last post by:
Hi, I am a SQL Server programmer using Oracle for the first time. In our .NET client apps which use a SQL Server back end, we would use Stored Procedure exclusively for all database access for...
14
by: peteh | last post by:
Hi All; We have many production jobs that "load from cursor" to a UDB/AIX 8.2 (with dpf) data warehouse from source tables residing Oracle 9i. Since Oracle dates are (roughly) equivalent to DB2...
2
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers:...
0
debasisdas
by: debasisdas | last post by:
SAMPLE CODE TO SHOW USE OF REFCURSOR ======================================= EXAMPLE #1 ---------------------- declare --declare the fer cursor. type my_ref_cur_typ is ref cursor; --declare...
0
debasisdas
by: debasisdas | last post by:
Cursor Variable Returning %ROWTYPE ----------------------------------------------------------- DECLARE TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; tmp_cv TmpCurTyp;TYPE EmpCurTyp IS REF...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.