Skip to Main Content
  • Questions
  • PL/SQL Procedure returning a Ref-Cursor with "embedded Cursors"

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Klaus .

Asked: March 12, 2002 - 7:30 am UTC

Answered by: Tom Kyte - Last updated: May 23, 2011 - 10:37 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Luckily I'm in the position to ask a question right now ...
I've been searching the archives, but didn't find a solution ...

My problem is the following:

I've a query like ...

SELECT U.UserId, U.LoginName, U.PassWord, U.ForeName,
U.SureName, U.Gender, U.Description, U.ModDate,
U.ModUser, DSCore.getState(U.State) AS State,
U.DefaultLanguageId,
CURSOR(SELECT C.UserContactId,
DSCore.getContactType(C.ContactTypeId) AS ContactType,
C.Value, C.Description,
DSCore.getState(C.State) AS State, C.ModUser
FROM UserContacts C
WHERE C.UserId = U.UserId) AS Contacts,
CURSOR(SELECT A.UserAddressId,
DSCore.getAddressType(A.AddressTypeId) AS AddressType,
A.StreetNo, A.ZipCode, A.City, A.Country,
DSCore.getState(A.State) AS State, A.ModUser
FROM UserAddresses A
WHERE A.UserId = U.UserId) AS Addresses
FROM Users U
WHERE U.UserId = pUserId;

Which executes very well from JDBC, ... even if run by a PHP-Script on a Web-Server.

Now I'd like to implement a PL/SQL Package Procedure, which returns a ref-cursor to the resultset of the above query, but the PL/SQL Parser encountered problems with the CURSOR-Function ...

The DSCore.Package Functions are all implemented an run smoothly. The whole package is pinned into the memory for performance reasons.

I've no clue, how to get around this problem - any hints are welcome!


and we said...

In 8i and before, PLSQL used a different SQL parser then the "database" did. For this reason, there are some features in SQL that PLSQL does not understand including (but not limited to)

o order by in a subquery
o select ( select .... ) from table (selecting a select)
o analytic functions
o group by extensions (group by rollup, group by cube)
o CURSOR in a select (obviously) ;)

So, how to solve that? Well, if you look at the following example, we'll see some SQL that should work:


scott@ORA817DEV.US.ORACLE.COM> select deptno, dname, cursor( select ename from emp where emp.deptno = dept.deptno )
2 from dept
3 where deptno = 10
4 /

DEPTNO DNAME CURSOR(SELECTENAMEFR
---------- -------------- --------------------
10 ACCOUNTING CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

ENAME
----------
CLARK
KING
MILLER


see that it apparently does not work (in 9i, this block does in fact execute cleanly

scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> variable x refcursor
scott@ORA817DEV.US.ORACLE.COM> set autoprint on
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> declare
2 p_deptno number default 10;
3 begin
4 open :x for
5 select deptno, dname, cursor( select ename from emp where emp.deptno = dept.deptno )
6 from dept
7 where deptno = p_deptno;
8 end;
9 /
select deptno, dname, cursor( select ename from emp where emp.deptno = dept.deptno )
*
ERROR at line 5:
ORA-06550: line 5, column 31:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( ) - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current exists max min prior sql stddev sum
variance execute multiset the both leading trailing forall
year month DAY_ HOUR_ MINUTE_ second TIMEZONE_HOUR_
TIMEZONE_MINUTE_ time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL stri
ORA-06550: line 6, column 3:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
; return returning and or

and here is how to solve this issue. We'll bypass the PLSQL SQL parser and by using dynamic SQL achieve the opening of the refcursor with the embedded CURSOR successfully:

scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> declare
2 p_deptno number default 10;
3 begin
4 open :x for
5 'select deptno, dname, cursor( select ename from emp where emp.deptno = dept.deptno )
6 from dept
7 where deptno = :p_deptno' USING p_deptno;
8 end;
9 /

PL/SQL procedure successfully completed.


DEPTNO DNAME CURSOR(SELECTENAMEFR
---------- -------------- --------------------
10 ACCOUNTING CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

ENAME
----------
CLARK
KING
MILLER


scott@ORA817DEV.US.ORACLE.COM>

and you rated our response

  (82 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

... as ever ... :-))

March 12, 2002 - 7:53 am UTC

Reviewer: Klaus from sunny Vienna/Austria (... no cangaroos ... ;-) ...)

again hi Tom ...

you made my day and saved me lot's of time/troubles ...

next time in USA, I owe you a pint and a session pool ...

Thanks a lot!


What is the purpose of CURSOR?

March 12, 2002 - 8:14 am UTC

Reviewer: AB from UK

Tom

I'm interested to know what is the purpose and intended use of the CURSOR expression and what types of application use is?

Thanks

Tom Kyte

Followup  

March 12, 2002 - 4:43 pm UTC

It can be used to get "non-square" results from the database.

Say you wanted to run a single query to retrieve:

o a list of tables you owno all of their columns and datatypes
o the constraints on each table
o the list of users that have SELECT on it
o the list of roles that have SELECT on it

Well, you would have to run at least 3 and probably 4 queries to answer that normally.

with CURSOR -- you can run a single query that returns all of that in one query. You'll get a result set that contains 4 result sets for each row.

March 12, 2002 - 12:47 pm UTC

Reviewer: A reader

Tom,

Can you explain CURSOR in sql statement with REAL life
example ?. How we implement this functionality and in which cases we use this.


select deptno, dname, cursor( select ename from emp where emp.deptno = dept.deptno ) from dept
where deptno = p_deptno;





Tom Kyte

Followup  

March 12, 2002 - 4:56 pm UTC

the original questioner had a REAL WORLD example ...

The retrieved a user, all of their contacts and all of their addresses.

Normally, you would run a query like this:


for x in ( select * from users )
loop
for y in ( select * from contacts where userid=x.userid )
loop
...
end loop;
for z in ( select * from addresses where userid=x.userid )
loop
...
end loop;
end loop;


Their query does all of that in a single query -- instead of incurring the overhead of running two queries / row, you just get a row with two result sets that you can fetch from.

the next logical question is...

March 12, 2002 - 9:04 pm UTC

Reviewer: Scott Mattes from VA, USA

Tom,
How does one code PL/SQL to fetch from this cursor?

Tom Kyte

Followup  

March 13, 2002 - 7:06 am UTC

it is not useful in PLSQL as yet. It is useful for returning results back to a client like Java or C.

Sorry to labour this one....

March 13, 2002 - 4:06 am UTC

Reviewer: Adrian Billington from UK

Tom

Like Scott, I'd also be interested to know if we could cycle through this statement in PL/SQL. Furthermore, what can you actually do with the resultset once you have it - it seems to me as though you can read it but not do much else with it unless there are some tricks to referencing the various cursor resultsets...?

Thanks

Adrian

Tom Kyte

Followup  

March 13, 2002 - 7:18 am UTC

In java or C, you'll get cursors (result sets) that you can read (thats what we do with resultsets?). You can iterate over them and do whatever you please.

PLSQL, unaware of this construct, cannot use them (yet)

More please

March 13, 2002 - 9:51 am UTC

Reviewer: A reader

Hi,

As Scott and Adrian addressed, how we reference the portion of "nested cursor" when we pass the REF cursor result set back to JAVA or C program?

Please give little example?

Thanks


Tom Kyte

Followup  

March 13, 2002 - 11:22 am UTC

$ java RefCursor
ACCOUNTING
CLARK
KING
MILLER
RESEARCH
SMITH
JONES
SCOTT
ADAMS
FORD
SALES
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
OPERATIONS

$ cat RefCursor.java

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class RefCursor
{

public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query = "begin "+
"open ? for "+
"'select dname, CURSOR(select ename "+
"from emp "+
"where emp.deptno=dept.deptno) "+
"from dept'; "+
"end;";

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn=
DriverManager.getConnection
("jdbc:oracle:thin:@aria-dev:1521:ora817dev",
"scott", "tiger");

Statement trace = conn.createStatement();

CallableStatement cstmt = conn.prepareCall(query);

cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();

ResultSet rset= (ResultSet)cstmt.getObject(1);

for(int i = 0; rset.next(); i++ )
{
System.out.println( rset.getString(1) );
ResultSet rset2 = (ResultSet)rset.getObject(2);
for( int j = 0; rset2.next(); j++ )
System.out.println( " " + rset2.getString(1) );
rset2.close();

}

rset.close();
cstmt.close();
}
}



OK, how about an example in some other language?

March 13, 2002 - 10:39 am UTC

Reviewer: Scott Mattes from VA, USA

First, are there plans for PL/SQL to be able to use this?

Second, would you give an example in Delphi, JAVA or C for using this, I am way curious.

Tom Kyte

Followup  

March 13, 2002 - 11:42 am UTC

yes, there are.

See java above. No idea about "delphi", you'd have to ask them.

March 13, 2002 - 4:36 pm UTC

Reviewer: A reader

Tom,

I can get same results by joing tables. What's new in
CURSOR subqueries?

Tom Kyte

Followup  

March 13, 2002 - 6:44 pm UTC

Look at the second comment on this page "What is the purpose of CURSOR? " and my response to it. Now, write me a single query using only joins that gets me that data without using CURSOR (hint -- there is in fact a way to do it HOWEVER it in itself involves using Object types!)



calling the given PL/SQL Function from java/PHP -> ORA 6503

March 14, 2002 - 7:54 am UTC

Reviewer: Klaus from Vienna, Austria

Hi Tom,

Based on your response I adapted my PL/SQL Function and it could be compiled successfully!

Unfortunately I'll get ORA 6503, when I try to execute the function, for e.g. with the following Java Code ...

String query = "begin ? := DSS.getUser(1); end;";

DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:oci8:@tstDB", "username", "password");

CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();

ResultSet rset= (ResultSet)cstmt.getObject(1);

for (int i = 0; rset.next(); i++) {
System.out.println("UId ="+rset.getString(1));
System.out.println("Login ="+rset.getString(2));
System.out.println("Passwd="+rset.getString(3));
System.out.println("FName ="+rset.getString(4));
System.out.println("SName ="+rset.getString(5));

ResultSet rset2 = (ResultSet) rset.getObject(12);

for (int j = 0; rset2.next(); j++) {
System.out.println(" ContactId="+rset2.getString(1));
}

rset2.close();

ResultSet rset3 = (ResultSet) rset.getObject(13);

for (int j = 0; rset3.next(); j++) {
System.out.println(" AddressId="+rset3.getString(1));
}

rset3.close();
}

rset.close();
cstmt.close();
conn.close();

The query in the PL/SQL Function works properly and delivers a record, if executed in SQL-Plus or other tool!

FollowUp-Question from me is, how to retrieve the refCursor
from the function?

Thanks again for your help!

Tom Kyte

Followup  

March 14, 2002 - 8:29 am UTC

well, one would need to see the PLSQL code as the error is coming from there!

Based on the error:

$ oerr ora 6503
06503, 00000, "PL/SQL: Function returned without value"
// *Cause: A call to PL/SQL function completed, but no RETURN statement was
// executed.
// *Action: Rewrite PL/SQL function, making sure that it always returns
// a value of a proper type.

I would GUESS that you are forgetting to return the ref cursor in the function!

You retrieve the ref cursor from the function in exactly the way I demonstrated above with the anonymous block -- it is not any different whatsoever. You would change:

String query = "begin "+
"open ? for "+
"'select dname, CURSOR(select ename "+
"from emp "+
"where emp.deptno=dept.deptno) "+
"from dept'; "+
"end;";


to

String query = "begin ? := f(); end;";

and thats it.

forgotten PL/SQL Function Code

March 14, 2002 - 9:14 am UTC

Reviewer: Klaus UNGER from Vienna/Austria

Hi Tom,

Sorry about forgetting the function source ...

refCursor ist defined as follows in the package:

TYPE refCursor IS REF CURSOR;

here is the source - as I'm returning the cursor, I got worried about the ref Cursor declaration ...

FUNCTION getUser(pUserId Users.UserId%TYPE)
RETURN refCursor
IS
c refCursor;
BEGIN
OPEN c FOR
'SELECT U.UserId, U.LoginName, U.PassWord, U.ForeName, U.SureName, U.Gender, U.Description, ' ||
' U.ModDate, U.ModUser, DSCore.getState(U.State) AS State, U.DefaultLanguageId, ' ||
' CURSOR(SELECT C.UserContactId, DSS.getContactType(C.ContactTypeId) AS ContactType, ' ||
' C.Value, C.Description, DSCore.getState(C.State) AS State, C.ModUser ' ||
' FROM UserContacts C ' ||
' WHERE C.UserId = U.UserId) AS Contacts, ' ||
' CURSOR(SELECT A.UserAddressId, DSS.getAddressType(A.AddressTypeId) AS AddressType, ' ||
' A.StreetNo, A.ZipCode, A.City, A.Country, DSCore.getState(A.State) AS State, ' ||
' A.ModUser ' ||
' FROM UserAddresses A ' ||
' WHERE A.UserId = U.UserId) AS Addresses ' ||
'FROM Users U ' ||
'WHERE U.UserId = :UID' USING pUserId;

RETURN c;

EXCEPTION
WHEN OTHERS THEN
IF (c%isOpen) THEN
CLOSE c;
END IF;
END;

As I understand, I don't have to declare a Type, that specifies the structure of the result-set. If I have to,
I wouldn't know, how to declare a cursor in a record-type ...

So again ... please enlight me with your comments!

Many thanks in advance

Klaus

PS: pint-counter++; ... ;)

Tom Kyte

Followup  

March 14, 2002 - 10:43 am UTC

You have the major #1 bug i the world.

"when others" that is not followed immediately by RAISE.

change the code to:

WHEN OTHERS THEN
IF (c%isOpen) THEN
CLOSE c;
END IF;
RAISE;


and then let us know what happens.



... done ...

March 14, 2002 - 10:56 am UTC

Reviewer: Klaus UNGER from Vienna / Austria

Hi Tom,

before I go to self-flagellation for the stupid raise thing ... must be a kind of code-blindness ...

Thanks ... the raise brought to light, that dynamic SQL cannot handle bind-varibales like :UID. When I use :1 the procedure works properly ...

Thanks and sorry for any inconvenience

Klaus

Tom Kyte

Followup  

March 14, 2002 - 11:09 am UTC

UID being one of those "magic words" ;)

ops$tkyte@ORA817DEV.US.ORACLE.COM> variable x refcursor
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2          open :x for 'select :uid from dual' using 1;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-01745: invalid host/bind variable name
ORA-06512: at line 2


ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2          open :x for 'select :user from dual' using 1;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-01745: invalid host/bind variable name
ORA-06512: at line 2


ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2          open :x for 'select :bv_uid from dual' using 1;
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> print x

   :BV_UID
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM>  

being a little bit embarrassed ...

March 15, 2002 - 4:45 am UTC

Reviewer: Klaus from Vienna/Austria

Hi Tom ...

Thanks again,
Seems to be, that I was not programming PL/SQL for a bit too long ... forget about all "minor details", which of course have huge impacts ...

- raise
- UID

back in college I'll give myself an failed ... ;)

Thanks & kind regards
Klaus

Very Informative......

April 09, 2002 - 8:59 am UTC

Reviewer: Neeti from TX

I am not sure how many goddies are still hidden in your
treasure. That was cool. Can you point to the documentation
where I can find more information about this construct.


Thanks,
Neeti

Tom Kyte

Followup  

April 09, 2002 - 9:15 am UTC

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1002754 <code>

....

How about in 9i?

April 09, 2002 - 2:29 pm UTC

Reviewer: A reader

You mentioned 'it is not useful in PLSQL as yet. It is useful for returning
results back to a
client like Java or C.'
but things like the following code give may be perhaps usefull ?:

set serveroutput on

declare
type rctyp is ref cursor;

myrc1 rctyp;
myrc2 rctyp;
empno number;
deptno number;
ename varchar2(10);

begin
open myrc1 for select deptno,
cursor(select e.empno, e.ename
from emp e
where e.deptno = d.deptno)
from dept d;

fetch myrc1 into deptno, myrc2;
while myrc1%FOUND loop
dbms_output.put_line('deptno = ' || deptno);
fetch myrc2 into empno, ename;
while myrc2%FOUND loop
dbms_output.put_line('empno = ' || empno || ' ename = ' || ename);
fetch myrc2 into empno, ename;
end loop;
fetch myrc1 into deptno, myrc2;
end loop;
close myrc1;
end;
/
deptno = 10
empno = 7782 ename = CLARK
empno = 7839 ename = KING
empno = 7934 ename = MILLER
deptno = 20
empno = 7369 ename = SMITH
empno = 7566 ename = JONES
empno = 7788 ename = SCOTT
empno = 7876 ename = ADAMS
empno = 7902 ename = FORD
deptno = 30
empno = 7499 ename = ALLEN
empno = 7521 ename = WARD
empno = 7654 ename = MARTIN
empno = 7698 ename = BLAKE
empno = 7844 ename = TURNER
empno = 7900 ename = JAMES
deptno = 40

Cheers
Charly



Tom Kyte

Followup  

April 09, 2002 - 2:40 pm UTC

Very nice, i tried binding to a dynamic ref cursor in 8i -- that didn't work. didn't think to try in 9i! I knew it was coming (thats why i said "yet" over and over).

Thanks!

How to do the Reverse

April 23, 2002 - 9:59 am UTC

Reviewer: Siva from NJ, USA

It is useful for sending Ref Cursor from Sql to Java.
The same does not work in reverse.

I am parsing an XML in Java and converting into a sql statement. I would like to pass the REF cursor of this SQL to PL/SQL. How do I do This.

I tried something like this in Oracle 8.1.6 . Gives some Internal Error

-- The following would be in a Java Stored procedure

public static oracle.sql.REF getOracleResultset(String serviceResponse)
throws java.sql.SQLException {

-- This would be SQL I would construct from the xml
String sqlstmt = new String("select * From emp;");
System.out.println("Recieved serviceResponse");

Connection conn = new OracleDriver().defaultConnection();

CallableStatement cstmt = conn.prepareCall("begin open ? for "+sqlstmt+" end;");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();

REF refc = ((OracleCallableStatement)cstmt).getREF(1);

return refc;
}

Tom Kyte

Followup  

April 23, 2002 - 10:57 am UTC

You cannot return a result set from a java stored procedure to plsql.

You can return the STRING that is the QUERY and let the SQL layer open a ref cursor.

Instead of:

return refc;

you will return sqlstmt;

Then the SQL layer (which you must go through regardless -- it is a layer you will be passing through anyway) can easily open that in a ref cursor.

Maximum open cursors exceeded when used with ORDER BY

June 24, 2002 - 9:53 am UTC

Reviewer: Roger Andersson from Sweden

I use this technique for administration of users and their roles in one application. I return all users and all their roles in one select and it works fine, until I add an ORDER BY clause.

Then I get ORA-01000, maximum open cursors exceeded. I have open_cursors set to 100, which I know is too low, and my query returns, and will probably never return more than, approx 150 records.

I can probably increase open_cursors and use the select order by statement I need for my feature.

I want to know if there is a way to use these "embedded" cursor together with order by and resultsets with a couple of hundred up to 1000 records, without having open_cursors set very high? If so, how should the select statement look like? If not, do you have a suggestion for another technique that can be used to solve this in one select statement?

One simple testcase is to start with the Java code provided above, add a couple of hundred records to the dept table and add an order by dname to the select statement.

Tom Kyte

Followup  

June 24, 2002 - 9:57 am UTC

The order by is adding some recursive sql (to allocate temp space or do something). It is not a function of the number of records (you would get it sorting 1 record or 1,000,000 records).

100 is way too low. I typically run with 1,000 to 1,500. We dynamically allocate the array 64 at a time so setting high does not have a high cost associated with it.

ORA-01008: not all variables bound

August 30, 2002 - 8:10 am UTC

Reviewer: A reader

Hi Tom

I followed your example and did this PL/SQL block but whenever I execute it I get ORA-01008, what´s wrong with my code?


DECLARE
type ref_cursor is ref cursor;
c1 ref_cursor;
cursor ch_cursor is select co_id, ch_seqno, ch_status, userlastmod, entdate
from contract_history ch1
where rownum = 1;
r_contract_histroy ch_cursor%rowtype;
fr_entdate CONSTANT DATE := trunc(sysdate - 7);
to_entdate CONSTANT DATE := trunc(sysdate - 1);
BEGIN
OPEN c1 for 'select co_id, ch_seqno, ch_status, userlastmod, entdate
from (select co_id, ch_seqno, ch_status, userlastmod, entdate,
dense_rank() over (partition by co_id order by ch_seqno desc) rango
from contract_history
where co_id < 11
and trunc(entdate) <= :to_entdate) iv_ch
where rango = 1
or (ch_seqno = 1 and trunc(entdate) <= :to_entdate)
order by co_id, ch_seqno' USING to_entdate;
LOOP
FETCH c1 into r_contract_histroy;
EXIT when c1%notfound;
dbms_output.put_line(r_contract_histroy.co_id);
END LOOP;
CLOSE c1;
END;
/

ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 11

Tom Kyte

Followup  

August 30, 2002 - 10:23 am UTC

using to_entdate, to_entdate;


You have two bind variables, only one of which was bound.


ops$tkyte@ORA920.US.ORACLE.COM> variable x refcursor
ops$tkyte@ORA920.US.ORACLE.COM> set autoprint on
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> begin
  2          open :x for 'select :y, :y from dual' using 'Hello';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 2


ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "x"
Elapsed: 00:00:00.00
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> begin
  2          open :x for 'select :y, :y from dual' using 'Hello', 'World';
  3  end;
  4  /

PL/SQL procedure successfully completed.


:Y                               :Y
-------------------------------- --------------------------------
Hello                            World

Elapsed: 00:00:00.00
ops$tkyte@ORA920.US.ORACLE.COM> 

ORA-01008 I found error

August 30, 2002 - 8:18 am UTC

Reviewer: A reader

Hi

I noticved I have to put two variables

using to_Entdate, to_entdate

I thought if the variable names are equal I only have to put once...

cheers

Usage in 8.05

September 03, 2002 - 9:14 am UTC

Reviewer: B.Harris from UK

Tom,

I'm unsure of how to implement in 8.0.5
ie. i want to return a ref cursor with embedded select using sql object type solution (due to ref cursor's not supporting dynamic sql)

i would like to
- create dynamic sql using embedded select (using dbms_sql)
then insert into collection type (sql object type)
- return as ref cursor

How would I implement define_column and column_value to insert into my object type?

Any workarounds/examples would be helpful?


Tom Kyte

Followup  

September 03, 2002 - 9:30 am UTC

Not making sense to me. Not clear what you want -- but I would suggest saving it up for a QUESTION, not a review.

CURSOR

December 24, 2002 - 10:32 am UTC

Reviewer: mo

Tom:

You say using CURSOR makes things easier. However I still do not see it by running the example below. Any advantages of using this in pl/sql.

SQL> select deptno,dname,cursor(select ename from emp where emp.deptno = dept.deptno)
  2  from dept where deptno = 10;

    DEPTNO DNAME          CURSOR(SELECTENAMEFR
---------- -------------- --------------------
        10 ACCOUNTING     CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

ENAME
----------
CLARK
KING
MILLER

3 rows selected.


1 row selected.

  1  select a.deptno,a.dname,b.ename from dept a, emp b where
  2* a.deptno = b.deptno and b.deptno = 10
SQL> /

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     KING
        10 ACCOUNTING     MILLER

3 rows selected.
 

Tom Kyte

Followup  

December 24, 2002 - 12:21 pm UTC

where did I say it was easier?

It can be quite useful in many cases. Write me a query against

ALL_USERS, ALL_OBJECTS, ALL_TAB_PRIVS that returns:

a) a username
b) object names and their types owned by that user
c) all grants granted to that user



cursor

December 24, 2002 - 2:29 pm UTC

Reviewer: mo

Tom:
 Do you mean this query: 

select a.user_id,substr(a.username,1,10) username,
 substr(b.owner,1,10) owner,substr(b.object_name,1,20) object_name,
substr(c.grantor,1,10) grantor,
substr(c.table_name,1,10) table_name,
substr(c.privilege,1,10) privilege
from all_users a,all_objects b, all_tab_privs c
where a.username=b.owner and b.owner=c.grantor
SQL> /

   USER_ID USERNAME   OWNER      OBJECT_NAME          GRANTOR    TABLE_NAME PRIVILEGE
---------- ---------- ---------- -------------------- ---------- ---------- ----------
         0 SYS        SYS        /1005bd30_LnkdConsta SYS        DUAL       SELECT
         0 SYS        SYS        /10076b23_OraCustomD SYS        DUAL       SELECT
         0 SYS        SYS        /1033c8a_SqlTypeWith SYS        DUAL       SELECT
         0 SYS        SYS        /103a2e73_DefaultEdi SYS        DUAL       SELECT
         0 SYS        SYS        /10501902_BasicFileC SYS        DUAL       SELECT
         0 SYS        SYS        /106faabc_BasicTreeU SYS        DUAL       SELECT
         0 SYS        SYS        /1079c94d_NumberCons SYS        DUAL       SELECT
         0 SYS        SYS        /10804ae7_Constants  SYS        DUAL       SELECT
         0 SYS        SYS        /108343f6_MultiColor SYS        DUAL       SELECT
         0 SYS        SYS        /10845320_TypeMapImp SYS        DUAL       SELECT
         0 SYS        SYS        /10948dc3_Permission SYS        DUAL       SELECT
         0 SYS        SYS        /1095ce9b_MultiCombo SYS        DUAL       SELECT
         0 SYS        SYS        /109cbb8e_SpanShapeR SYS        DUAL       SELECT
         0 SYS        SYS        /10a793fd_LocaleElem SYS        DUAL       SELECT
         0 SYS        SYS        /10b74838_SecurityMa SYS        DUAL       SELECT
         0 SYS        SYS        /10d0c623_drawBytesI SYS        DUAL       SELECT
         0 SYS        SYS        /10dcd7b1_ProducerCo SYS        DUAL       SELECT
         0 SYS        SYS        /10e48aa3_StringExpr SYS        DUAL       SELECT 

Tom Kyte

Followup  

December 24, 2002 - 2:46 pm UTC

yes and if you don't know what is wrong with that answer you gave, you need to pick up a book on SQL!!!

tell me, why is DUAL in there over and over and over and over -- why will /1005bd30_LnkdConsta appear in there over and over and over.

You have two 1:m relationships. A square result set isn't going to be very good for that.

Suppose you have a user SYS with 500 objects in all_objects and 50 grants in all_tab_privs. Do you really want 25000 records back for that user???


CURSOR

December 24, 2002 - 3:14 pm UTC

Reviewer: mo

well i mean if we have 1:m between user and objects and 1:m between user and grants then this should be the query. But it seems to be the same result. Are you saying you can not get the result you want with a join statement, and I have to use CURSOR.

  1  select a.user_id,substr(a.username,1,10) username,
  2   substr(b.owner,1,10) owner,substr(b.object_name,1,20) object_name,
  3  substr(c.grantor,1,10) grantor,
  4  substr(c.table_name,1,10) table_name,
  5  substr(c.privilege,1,10) privilege
  6  from all_users a,all_objects b, all_tab_privs c
  7* where a.username=b.owner and a.username=c.grantor
SQL> /

   USER_ID USERNAME   OWNER      OBJECT_NAME          GRANTOR    TABLE_NAME PRIVILEGE
---------- ---------- ---------- -------------------- ---------- ---------- ----------
         0 SYS        SYS        /1005bd30_LnkdConsta SYS        DUAL       SELECT
         0 SYS        SYS        /10076b23_OraCustomD SYS        DUAL       SELECT
         0 SYS        SYS        /1033c8a_SqlTypeWith SYS        DUAL       SELECT
         0 SYS        SYS        /103a2e73_DefaultEdi SYS        DUAL       SELECT
         0 SYS        SYS        /10501902_BasicFileC SYS        DUAL       SELECT


Is this how you do it:

select username,cursor (select object_name,object_type from all_objects where object_type='TABL
cursor (select table_name,privilege from all_tab_privs) from all_users
where username='SYS'
SQL> /

USERNAME                       CURSOR(SELECTOBJECT_ CURSOR(SELECTTABLE_N
------------------------------ -------------------- --------------------
SYS                            CURSOR STATEMENT : 2 CURSOR STATEMENT : 3

CURSOR STATEMENT : 2

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
ACCESS$                        TABLE
AQ$_MESSAGE_TYPES              TABLE
AQ$_PENDING_MESSAGES           TABLE
AQ$_PROPAGATION_STATUS         TABLE
AQ$_QUEUE_STATISTICS           TABLE
AQ$_QUEUE_TABLE_AFFINITIES     TABLE
AQ$_SCHEDULES                  TABLE
ARGUMENT$                      TABLE
ASSOCIATION$                   TABLE
ATEMPTAB$                      TABLE

CURSOR STATEMENT : 3

TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
DUAL                           SELECT
SYSTEM_PRIVILEGE_MAP           SELECT
TABLE_PRIVILEGE_MAP            SELECT
STMT_AUDIT_OPTION_MAP          SELECT
STANDARD                       EXECUTE
V_$BH                          SELECT
V_$SESSION_LONGOPS             SELECT
V_$NLS_PARAMETERS              SELECT
V_$NLS_VALID_VALUES            SELECT
V_$MLS_PARAMETERS              SELECT
V_$OPTION                      SELECT
V_$VERSION                     SELECT
V_$PQ_SESSTAT                  SELECT
V_$SESSION_CONNECT_INFO        SELECT
V_$PQ_TQSTAT                   SELECT
V_$ACTIVE_INSTANCES            SELECT 

Tom Kyte

Followup  

December 24, 2002 - 4:25 pm UTC

I am saying you cannot get a meaningful, useful result with joins due to the two independent 1:m relationships

You would use a cursor() function in order to get something you can actually use.

CURSOR function

December 26, 2002 - 10:17 am UTC

Reviewer: mo

Tom:

1. I full understand now how CURSOR function can eliminate square results when you jon more than two tables. IS this SQL or oracle SQL*PLUS function only.

2. I noticed the the CURSOR function above, it is always doing an outer join even without using (+) in the join. Can you use CURSOR with inner join so that you can take out the wher ecaluse and print out only dept numbers that have employees in them.

3. does this have any use in using static sql in 8i pl/sql or only in 9i R2.

THanks,

Tom Kyte

Followup  

December 26, 2002 - 11:54 am UTC

1) there are examples using plsql and java right above!

2) where exists... where in ... will do that.

3) using static sql in plsql *won't work* with this bit of functionality in 8i. In 9i and up -- go for it. In 8i, dynamic sql, views.


open q for query using a, b, c

February 07, 2003 - 9:02 am UTC

Reviewer: Edgar Chupit from Latvia

Hello Tom,

ops$tkyte@ORA920.US.ORACLE.COM> begin
2 open :x for 'select :y, :y from dual' using 'Hello', 'World';
3 end;
4 /

I have a question about this, is it possible when I have query in varchar to find out how many and what are the names of bind variables?
Suppose I have a stored function that constructs sql query and returns ref cursor. My function have 3 params if each of params is not null than I add where condition. Is there a possibility to write code like that one without having to
write all possible combinations of params? It's just an example but I hope you'll get the point.

function test1( tbl varchar, log varchar, par varchar ) return refCur is
q varchar(512);
result refCur;
begin
q := 'SELECT TABLE_NAME, TABLESPACE_NAME '||chr(10)||
'FROM USER_TABLES '||chr(10)||
'WHERE MONITORING = ''NO'' '||chr(10);

if( tbl is not null ) then
q := q || 'AND TABLE_NAME LIKE :TBL '||chr(10);
end if;
if( log is not null ) then
q := q || 'AND LOGGING = :LOG '||chr(10);
end if;
if( par is not null ) then
q := q || 'AND PARTITIONED = :PAR'||chr(10);
end if;

if( tbl is null and log is null and par is null ) then
open result for q;
else if ( tbl is null and log is null and par is not null ) then
open result for q using par;
else if( tbl is null and log is not null and par is null ) then
open result for q using log;
else if( tbl is null and log is not null and par is not null ) then
open result for q using log, par;
else if( tbl is not null and log is null and par is null ) then
open result for q using tbl;
else if( tbl is not null and log is null and par is not null ) then
open result for q using tbl, par;
else if( tbl is not null and log is not null and par is null ) then
open result for q using tbl, log;
else if( tbl is not null and log is not null and par is not null ) then
open result for q using tbl, log, par;
end if; end if; end if; end if; end if; end if; end if; end if;

return result;
end;


Tom Kyte

Followup  

February 07, 2003 - 12:22 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

show exactly how to do that

awesome

February 07, 2003 - 1:11 pm UTC

Reviewer: Edgar Chupit from Latvia

Hello Tom,

Thanks, really beautifull solution to use sys_context for this. Thanks!


problem with analytic function along with pl/sql table

February 25, 2003 - 10:59 am UTC

Reviewer: MRL from mumbai,India

I have this scenario:
I need to join a pl/sql table inside a procedure with a database table and also use rank() function ...all these in the same query whose results are returned through a REF cursor.
Now analytic function HAS to be used in a dynamic query in pl/sql. But when i join the pl/sql table with the database table(in that dynamic query) i get an error as 'invalid column name'. The part of the query (with out the rank() )function works well as a normal (non-dynamic) query.
Here it is:

create or replace type obj_1 as object
(col1 varchar2(10),col2 varchar2(10);
/

create or replace type table_1 as table of obj_1;
/
/* not giving package specification here*/

create or replace package body pkg_p
as
create or replace package body prc_p
(comma_sep_string_1 in varchar2,
comma_sep_string_2 in varchar2,
ref_cur_var out ref_cur)
as
array_1 dbms_utility.uncl_array;
array_2 dbms_utility.uncl_array;

count_1 number;
count_2 number;

table_2 table_1:=table_1(obj_1('',''));
dbms_utility.comma_to_table(comma_sep_string_1,count_1 ,array_1);

dbms_utility.comma_to_table(comma_sep_string_2,count_2 ,array_2);

table_2 .extend(count_1-1,1);

for i in 1..count_1.count loop
table_2(i).col1:=array_1(i);
table_2(i).col2:=array_2(i);
end loop;

open ref_cur_var for
'select a1,b1,c1,rank() over (partition by a1 order by b1 desc) as myrank
from database_table,(select x.col1,x.col2 from TABLE(CAST(table_2 as table_1) as x ) pl_sql_table
where database_table.c1=pl_sql_table.col1';
end prc_p;
end pkg_p;

forgive me for syntax errors.

I get the error as 'invalid column name';
If i just return
select a1,b1,c1 from database_table,(select x.col1,x.col2 from TABLE(CAST(table_2 as table_1) as x ) pl_sql_table
where database_table.c1=pl_sql_table.c1
through the ref cursor (non dynamic query without rank()) it works fine.

But i need to include rank() and the join with pl/sql table...how do i do it?

Have i done a very silly mistake in the code????

I also need to do a myrank < 21 in the where clause .
how do i do it?

(i have read in this site before that using comma_to_table for such a cause is not correct)

Tom Kyte

Followup  

February 25, 2003 - 7:51 pm UTC

you have to bind inputs -- this is the same as trying to bind a "date" into a query -- no different:

open ref_cur_var for
'select a1,b1,c1,rank() over (partition by a1 order by b1 desc) as myrank
from database_table,(select x.col1,x.col2 from TABLE(CAST( :x as table_1)
as x ) pl_sql_table
where database_table.c1=pl_sql_table.col1'
using table_2;


to get "myrank <21", you use an inline view


open ref_cur_var for
'select * from (
select a1,b1,c1,rank() over (partition by a1 order by b1 desc) as myrank
from database_table,(select x.col1,x.col2 from TABLE(CAST( :x as table_1)
as x ) pl_sql_table
where database_table.c1=pl_sql_table.col1 ) where myrank < 21'
using table_2;



cursor() in views

May 29, 2003 - 10:46 pm UTC

Reviewer: A reader

The OP's example (get user, all his contacts and all their addresses) is a great real-world example of CURSOR.

But when I try to create a view using a SELECT with a cursor() expression, I get ORA-22902 (cursor not allowed).

I really dont want to create 3 views (user, user_contacts, contact_address) for this! A user view with 2 CURSOR() columns would be perfect.

How can I work around this? Thanks

Tom Kyte

Followup  

May 30, 2003 - 7:38 am UTC

i guess you won't be using a view -- perhaps a stored procedure that returns a ref cursor.

Recursive describe on the result set

July 02, 2003 - 11:07 am UTC

Reviewer: Piotr Jarmuz from Braunschweig, Germany

Hello Tom,

Is there a way to do a recursive describe on the result set with the embedded cursors? I mean can I code:

public Node describe(ResultSetMetaData rs) throws Exception {
ResultSetMetaData md=rs.getMetaData();
for (int i=0; i<md.getColumnCount(); i++) {
if (md.getColumnType(i+1)==OracleTypes.CURSOR) {
// somehow get MetaData for this CURSOR
}
handle each other type
}

BEFORE I fetch actually the Cursor for the nested query?
I guess I can't but I'd like your confirmation.

My goal is to write a generic Java tree builder based on any SQL query also with embedded cursors.

I always can do that on the fly.
I though about it as an optimization to my builder.


Another associated problem:
Can I return an array of refcursors from a PL/SQL to the client? I tried the following in 9iR2 but it says:

declare
type cursor_array_t is table of sys_refcursor;
begin
...
end;

ORA-06550: line 2, column 24:
PLS-00990: Index Tables of Cursor Variables are disallowed
ORA-06550: line 2, column 1:
PL/SQL: Item ignored

It doesn't have to be an array. But I need a way to return an unknown number of cursors. I thought about embedded cursors built with execute immediate but maybe there is a simpler way?

Thanx a lot in advance!


Tom Kyte

Followup  

July 02, 2003 - 11:13 am UTC

the "describe" in java is limited to describing result sets - hence you must retrieve the result set. so, you have to fetch a row in order to describe the result set contained in that row.

You cannot return an unknown number of ref cursors.


Why should we use RAISE in OTHERS exception?

April 16, 2004 - 2:31 am UTC

Reviewer: Tony from India, Chennai

Tom,

Why should we use RAISE in OTHERS exception only?
Can you point to your answer for similar questions,if any, or some documents?

Tom Kyte

Followup  

April 16, 2004 - 7:31 am UTC



what else are you going to do?

I know of one case where I use a when others without a RAISE

create function is_number( p_string in varchar2 ) return boolean
as
l_n number;
begin
l_n := p_string;
return true;
exceptions
when others then return false;
end;


but show me another legitimate case where "all errors should forthrightly be ignored"



WHEN OTHERS

April 16, 2004 - 8:43 am UTC

Reviewer: Tony Andrews from London, UK

It fascinates me how often people code exception handlers like this one:

exception
when others then dbms_output.put_line (substr('Error: '||sqlerrm, 1, 249));
end;

(That is a real example posted as part of an answer in a forum yesterday.)

Clearly the writer intended that, should an error occur, it should be brought to the user's attention. What did he think would happen if he "forgot" to write that exception handler?!

Tom Kyte

Followup  

April 16, 2004 - 9:10 am UTC

that's what I call ignoring the error and why I say "have a when others that is not followed by a RAISE is almost CERTAINLY a bug in your code"


imagine that exception block makes it into production.

that error will NEVER be seen from again, it'll will silently fail while appearing to succeed.

Ref Cursors in a function

May 08, 2004 - 6:08 pm UTC

Reviewer: Mohini from australia

DBMS_OUTPUT.PUT_LINE (C_REC.EMPNO);
*
ERROR at line 4:
ORA-06550: line 4, column 29:
PLS-00302: component 'EMPNO' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

Tom Kyte

Followup  

May 10, 2004 - 7:21 am UTC

huh?

you might consider posting enough of an example for someone to comment on.

sorry for the bad cut-paste job

May 10, 2004 - 10:59 am UTC

Reviewer: mohini from australia

CREATE PACKAGE MY_TYPES_PKG
AS
TYPE refCursor IS REF CURSOR;

END MY_TYPES_PKG;

/


CREATE FUNCTION GETEMPNAME
RETURN MY_TYPES_PKG.refCursor
IS
C MY_TYPES_PKG.refCursor;
BEGIN
OPEN C FOR
SELECT * FROM EMP;

RETURN C;


END;

/


SELECT GETEMPNAME FROM DUAL;

gives all the records


Now if I want to process the recordset as follows..I get an error:

set serveroutput on;

BEGIN

FOR C_REC IN (SELECT GETEMPNAME FROM DUAL) LOOP
DBMS_OUTPUT.PUT_LINE (C_REC.EMPNO);
END LOOP;

END;

/

DBMS_OUTPUT.PUT_LINE (C_REC.EMPNO);
*
ERROR at line 4:
ORA-06550: line 4, column 29:
PLS-00302: component 'EMPNO' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


Tom Kyte

Followup  

May 10, 2004 - 11:40 am UTC

did you ever think about what is happening here?

you have a query that returns a refcursor.  

you do not have a query that returns a column name empno anywhere.  You have an UNNAMED column that is a refcursor -- a result set.  that result set might have EMPNO as a column, but c_rec sure doesn't.


In plsql, the way to do this is:

ops$tkyte@ORA9IR2> declare
  2          l_cursor sys_refcursor;
  3      l_rec    emp%rowtype;
  4  begin
  5          l_cursor := getEmpName;
  6          loop
  7                  fetch l_cursor into l_rec;
  8                  exit when l_cursor%notfound;
  9                  dbms_output.put_line( l_rec.empno );
 10          end loop;
 11          close l_cursor;
 12  end;
 13  /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
 
PL/SQL procedure successfully completed.
 

bad cut-paste...

May 10, 2004 - 11:00 am UTC

Reviewer: mohini from australia

Working in version 9.2.0.5 of oracle

Implicit vs Explicit

May 10, 2004 - 2:07 pm UTC

Reviewer: Mohini from Australia

9.2.0.5

Thanks Tom,
I was trying to process the cursor in one shot for loop..
instead of opening, closing it...
Thanks for the explanation.

questions:

1. FUNCTION GETEMPNAME can also be a procedure with an output variable.
Is there any advantage/disadvantage of doing it in a procedure rather than a function.

2. Can these weak refcursors be passed as a parameter into a procedure. Are there any caveats..

--Thanks

Tom Kyte

Followup  

May 10, 2004 - 2:12 pm UTC

1) no

2) yes, but you sort of need to have known at compile time what their select lists where -- in order to have the necessary variables setup to fetch into.

PL/SQL Procedure returning a Ref-Cursor with "embedded Cursors

June 28, 2004 - 7:00 am UTC

Reviewer: Sam Brown from London,England

Found The arcticle Very useful. It has very good clear examples on how to create cursors and why you would use Cursors

Why this Error -- it is internal error -- but I am not sure if it is known bug.

October 14, 2004 - 7:03 am UTC

Reviewer: Ajeet

Hi Tom:

I have 2 oracle instances -- evnrdwq2 and evnctpq1 -

both of them are 9.2.0.5.

There is a table which is at evnrdwq2 --name is rdm_f_parameter. and then I have a public synonym of this table at evnctpq1 with the name rdo_parameter_value .

When I run this query at evnctpq1 -- I get the error as mentioned below :

select
cursor(select a.parameter_value
from rdo_parameter_value a
where a.param_seq_id = b.parameter_seq_id and a.dim_seq_id = b.dim_seq_id
and a.fleet = b.fleet and a.flight_datetime > sysdate-120 )
from
rdo.rdo_sea_alert_v b
/

Then I get the error :

ERROR at line 5:
ORA-00600: internal error code, arguments: [kokbnp2], [942], [], [], [], [],
[], []

When I run the query at evnrdwq2 -- using the rdm_f_parameter as it is at evnrdwq2 -then I do'nt get this error and it works fine.

select
cursor(select a.parameter_value
from rdm_f_parameter a
where a.param_seq_id = b.parameter_seq_id and a.dim_seq_id = b.dim_seq_id
and a.fleet = b.fleet and a.flight_datetime > sysdate-120 )
from
rdo.rdo_sea_alert_v@evnctpq1 b
/
--

MY question is -- is there an issue in running cursor(Select from remote_table ....)

as even at evnctpq1 -- when I use a local table this query works perfectly..problem comes only when I use a table which is at the remote instance.

I have searched metalinlk but did not get a clear description ...of this.I tried google there someone has asked this question but there is no answer.

any help will be great.

Thanks
Ajeet

Tom Kyte

Followup  

October 14, 2004 - 10:24 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:25272733466344 <code>



Thanks -- The links were very helpful.

October 16, 2004 - 5:43 am UTC

Reviewer: Ajeet

Tom,
Thanks for the Links -- Solution of the problem was

instead of using a remote table -- use a view for that table. don't know what is the logic.But using the view worked and solve the probelm.

Thanks
Ajeet

8i Documentation

October 25, 2004 - 5:50 pm UTC

Reviewer: Robert from Memphis, USA

Tom,

Version 8.1.7.4.

Referring to the original question...can you please tell me where in the 8i documentation it says...
1) The 8i pl/sql and sql parsers are different.
2) Native dynamic sql uses the sql parser (vs. the pl/sql parser).

(I have been looking through the Concepts guide, Oracle8i Application Developer’s Guide - Fundamentals, and PL/SQL User’s Guide and Reference)

Thanks,

Robert.

Tom Kyte

Followup  

October 25, 2004 - 7:45 pm UTC

it doesn't, most implementation details are not "documented".

however, it is trivial to show empirically that they are -- you have sql that runs OK in sqlplus -- it fails to compile in plsql -- it succeeds if executed via NDS.

there are billions of implementation details that are not documented.

Pretty good inductive reasoning

October 26, 2004 - 10:32 am UTC

Reviewer: Robert from Memphis

Tom,

Not 100% airtight according to formal deductive logic... but close enough! :)

Thanks for your faithful and valuable service to the Oracle community!

Robert.

Refcursor Example in your Book : Page 33

November 03, 2004 - 11:48 am UTC

Reviewer: abhay

Tom,
I copied the REFCURSOR example in your book (Expert O on O ) on Page 33.

Why do I get error when I try to print second time ?

============

Create table x4
2 as
3* select table_name from user_tables
gname> /

Table created.

gname> variable x refcursor;
gname> begin
2 open :x for select * from x4;
3 end;
4 /

PL/SQL procedure successfully completed.

gname> delete from x4;

17 rows deleted.

gname> commit;

Commit complete.

gname> print :x;

TABLE_NAME
------------------------------
A3
BLK
BLK2
BLK3
EXCL_USER_SAVE
MYTAB
PART_TAB
PLAN_TABLE
SQLTABLE
SQLTABLE2
SQLTABLE3
T
T1
T2
T3
T4
T5

17 rows selected.

gname> PRINT :X;
SP2-0625: Error printing variable "x"

Tom Kyte

Followup  

November 04, 2004 - 1:37 am UTC

you already printed X, just as you cannot get blood from a stone, you cannot get more rows from an empty - closed cursor.

Embedded Cursor/ Ref Cursor unable to be cached

December 20, 2004 - 6:03 pm UTC

Reviewer: Chris Ellis from UK

Tom,
Am I right in thinking that the "embedded cursors"/Ref cursors are not going to be cached and hence soft-parsed for each "outer" row. [Based on reading Effective Oracle p297] The tkprof report I have, taken from charly's example above seems to indicate this :-

SELECT "A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME"
FROM
"EMP" "A2" WHERE "A2"."DEPTNO"=:CV1$


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.03 0.04 0 0 0 0
Fetch 18 0.00 0.00 0 25 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26 0.03 0.04 0 25 0 14

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS FULL EMP (cr=5 r=0 w=0 time=129 us)

I was wondering if this situation is unavoidable in trying to keep it in one sql statement with embedded cursors?

Great site and great books - Can't wait for the new improved one-on-one.

Tom Kyte

Followup  

December 20, 2004 - 6:46 pm UTC



Yes, the downside to ref cursors is they are parsed -- we need N cursor handles simultaneously in general (client could decide to process 100 rows at a time, or 1 or 1000)

the ref cursor needs it's own "cursor" basically -- to get a cursor, we have to parse.

dynamic SQL in PRO*C

January 22, 2005 - 1:00 pm UTC

Reviewer: A reader

Hi

I need to use cursor() function and analytic functions in PRO*C, 8.1.7.4. I am not able because I think these types of SQL need to be run as dynamic SQL.

There are four methods to run dynamic SQL in PRO*C, I think I need method 3 or 4 but I am not sure, do you know which method can be used for my purposes?



Tom Kyte

Followup  

January 22, 2005 - 4:22 pm UTC

well, there is ansi dynamic sql too -- "method 5" if you will.

you would be looking at "method 3". It is similar to native dynamic sql in plsql, it'll "hide" the query from the pro*c pre-processor.

Cursors in select?

February 16, 2005 - 1:00 pm UTC

Reviewer: Sree from Louisville,Ky,USA

Tom,
From your answers in this thread(at the begining), you said, this is not implemented in
pl/sql yet and they have plans to do so. Can this be done in pl/sql now (atleast in 10G?)
Here is the query..

select deptno, dname, cursor( select ename from emp where emp.deptno = dept.deptno )
from dept
where deptno = 10

Thanks,



Tom Kyte

Followup  

February 16, 2005 - 2:40 pm UTC

the original answer says and demonstrated it working in 9i....

Cursor in Select

February 17, 2005 - 10:15 am UTC

Reviewer: Sree from Louisville,KY,USA

Tom,
It is working as you mentioned before using this small block:

variable x refcursor
set autoprint on
declare
p_deptno number default 10;
begin
open :x for
select deptno,dname,cursor(select empno,ename,sal from emp_sri e where e.deptno=d.deptno)
from dept d
where deptno=p_deptno;
end;
/

PL/SQL procedure successfully completed.


DEPTNO DNAME CURSOR(SELECTEMPNO,E
---------- -------------------- --------------------
10 Accounting CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

EMPNO ENAME SAL
---------- -------------------- ----------
1234 Matt 2500
2345 John 4567

** I do not want to show CUSOR STATEMENT 3

But how do we implement it in a stored procedure and get the ref cursor and
output all those columns? can you show me an example.

Thanks for all your help.

Tom Kyte

Followup  

February 17, 2005 - 1:24 pm UTC

the main use of this feature is to return to a client a result set with optional pointers to other result sets that they probably won't ever use

instead of sending back dept JOINed to emp -- to an application that would probably never show the EMP data -- you send back a cursor, a pointer -- in the event they decide to show it.

In plsql to dump the entire result set -- just join. If you truly think for whatever reason you need a cursor in a cursor, plsql makes this happen most naturally like this;


for x in ( .... )
loop
do whatever
for y in ( select .... where ... = x.column )
loop
do whatever



unexpected ora-01008 (8.1.7.4)

June 09, 2005 - 1:09 pm UTC

Reviewer: Alexey from Russia

Hello, Tom.

I have one bind variable in a dynamic SQL expression.
When I open cursor for this sql, it gets me to ora-01008.
Please consider:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production

SQL> declare
  2    type cur is ref cursor;
  3    res cur;
  4  begin
  5    open res for
  6    'select * from (select * from dual where :p = 1) connect by 1 = 1'
  7    using 1;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 5

SQL> declare
  2    type cur is ref cursor;
  3    res cur;
  4  begin
  5    open res for
  6    'select * from (select * from dual where :p = 1) connect by 1 = 1'
  7    using 1, 2;
  8  end;
  9  /

PL/SQL procedure successfully completed.

And if I run the same thing on 10g -- all goes conversely. The first part runs ok, and the second part reports "ORA-01006: bind variable does not exist" (as it should be, I think). Remember, there is ONE bind variable in sql, not two. Is it a bug in 8i?

What should we do to avoid this error running the same plsql program code on different Oracle versions?

P.S. Thank you for your invaluable work on this site. 

Tom Kyte

Followup  

June 09, 2005 - 6:31 pm UTC

what is the purpose of this query really?

but it would appear to be a bug in 8i (since it should need but one). You will have to work that via support.

Re: unexpected ora-01008 (8.1.7.4)

June 10, 2005 - 10:45 am UTC

Reviewer: Alexey from Russia

> what is the purpose of this query really?

There was a complicated query in our application with the "connect_by" subquery. I just cut it down to demonstrate the problem.

BTW, the query just has been rewritten today cause our customers will not work with support and definitely will not apply any patches on their production 8i systems.

We developers are always pay for customers' oddities and oracle's mistakes. :)

But anyway -- thank you for your reply.

PL/SQL refcursor and maximum open cursors

August 02, 2005 - 6:48 am UTC

Reviewer: William from The Netherlands

Hi Tom,

I ran into a 'ORA-01000: maximum open cursors exceeded' error while processeing a query that contains several cursors.

What I do is open the main cursor and fetch it into several sys_refcursors (r_%) and process the sys_refcursors :

open c;
loop
begin
fetch c into l_barcode
, r_author
, r_corp_author
, l_illustration
;
-- Fetching all author attributes
loop
fetch r_author into l_cap
, l_addpre
, l_fname
, l_prefix
;
exit when r_author%notfound;
--
end loop;
..
..
end;
end loop;
close c;


As I said, I ran into ORA-01000, because every sys_refcursor is implicitly opened, but not closed for each record in cursor c. That means, not that I am aware of.

Is there a way to manage this process (besides altering the open_cursors parameter to a value of 1000000)

Kind regards,

William

Tom Kyte

Followup  

August 02, 2005 - 8:06 am UTC

no, not "every", just the ones you've fetched.


are you closing them?  I've 48,106 rows in my all_objects, insufficient open cursors to have them ALL open at the same time, but yet:

ops$tkyte@ORA10G> show parameter open_cursors
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2          cursor c is select owner, cursor(select * from dual) c1, cursor(select user_id from all_users where all_users.username =
  3          all_objects.owner) c2 from all_objects;
  4
  5          l_owner all_objects.owner%type;
  6          l_c1 sys_refcursor;
  7          l_c2 sys_refcursor;
  8
  9          l_dummy dual.dummy%type;
 10          l_user_id all_users.user_id%type;
 11
 12          l_cnt number := 0;
 13  begin
 14          open c;
 15          loop
 16                  fetch c into l_owner, l_c1, l_c2;
 17                  l_cnt := l_cnt+1;
 18                  dbms_application_info.set_client_info(l_cnt);
 19                  exit when c%notfound;
 20                  loop
 21                          fetch l_c2 into l_user_id;
 22                          exit when l_c2%notfound;
 23                  end loop;
 24                  close l_c2;
 25                  loop
 26                          fetch l_c1 into l_dummy;
 27                          exit when l_c1%notfound;
 28                  end loop;
 29                  close l_c1;
 30          end loop;
 31          close c;
 32  end;
 33  /
 
PL/SQL procedure successfully completed.
 

Same in 9i (with 100 open cursors)...
 

Result sets in Pro*C?

August 02, 2005 - 11:18 am UTC

Reviewer: David Weigel from Villanova, PA USA

Thanks for the Java example, but may I have a brief example of handling a result set from a PL/SQL procedure from C?

Tom Kyte

Followup  

August 02, 2005 - 1:55 pm UTC

</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

Re: PL/SQL refcursor and maximum open cursors

August 09, 2005 - 9:32 am UTC

Reviewer: William from The Netherlands

I tried implementing the close statement for the cursor, but ended up in an error ORA-01001: invalid cursor

I found out that the only way to close the cursors is to loop through the cursors, with the last opened cursor at the first entry, so f.e. your code is like this:

fetching c into l_c1, l_c2
looping through l_c2, close l_c2
looping through l_c1, close l_c1

If you process like the following, the error is returned:
fetching c into l_c1, l_c2
looping through l_c1, close l_c1
looping through l_c2, close l_c2

Come to think to it, it seems reasonable, or not?



Tom Kyte

Followup  

August 09, 2005 - 10:41 am UTC

I'm not sure it is reasonable. I'll file a bug and see what they say. If you take my code and process the columns backwards from the way I did, I get it too (the ora-1001), it should not matter in my mind.

maximum open cursors execeeded

September 01, 2005 - 10:12 am UTC

Reviewer: mani from India

Hi Tom

The below query fails with maximum open cursors exceeded. How to tune the query to overcome the cursor exceeded

GO_BLOCK('CB_ERROR_DETAILS');
FIRST_RECORD;
FOR I IN (SELECT ORL.FILE_TRANSFER_ID FILE_TRANSFER_ID,
OML.BRANCH_CODE BRANCH_CODE,
OML.DATE_TIME_SENT DATE_TIME_SENT,
ORL.ERROR_TYPE ERROR_TYPE,
OML.FILE_NAME FILE_NAME,
TRUNC(OML.LOG_DATE) LOG_DATE,
ORL.LINE_NUMBER LINE_NUMBER,
ORL.ERROR_MESG ERROR_MESG,
ORL.ERROR_CODE ERROR_CODE,-- ** Added By Mithna **
ORL.CLEARED_IND,
ORL.ROWID IDROW
FROM OD_REJECTION_LOG ORL,
OD_MAIL_LOG OML
WHERE OML.FILE_TRANSFER_ID = ORL.FILE_TRANSFER_ID
AND OML.BRANCH_CODE = NVL(:CB_FILTER.DI_V_BRANCH_CODE,OML.BRANCH_CODE)
AND TRUNC(OML.LOG_DATE) BETWEEN TRUNC(NVL(:CB_FILTER.TI_D_DATE_FROM,log_date))
AND TRUNC(NVL(:CB_FILTER.TI_D_DATE_TO,log_date))
AND SUBSTR(OML.FILE_NAME,1,1) = DECODE(:CB_FILTER.TI_V_FILE_TYPE,'F', SUBSTR(OML.FILE_NAME,1,1),:CB_FILTER.TI_V_FILE_TYPE)
AND OML.FILE_NAME = NVL(:CB_FILTER.TI_V_FILE_NAME,OML.FILE_NAME)
AND ((:CB_FILTER.RG_ERROR_STATUS != 'A' AND NVL(CLEARED_IND,'N') = :CB_FILTER.RG_ERROR_STATUS
)
OR
(:CB_FILTER.RG_ERROR_STATUS = 'A' AND NVL(CLEARED_IND ,'~') = NVL(CLEARED_IND ,'~')
)
)
--ORDER BY 4)
ORDER BY 6 DESC,5 ASC)



Tom Kyte

Followup  

September 01, 2005 - 3:45 pm UTC

sorry, if that query is doing it , it simply means you have open cursors set too low -- i see only one cursor there.

maximum open cursors execeeded

September 02, 2005 - 1:08 am UTC

Reviewer: mani from India

I had set open_cursors = 1000



Tom Kyte

Followup  

September 02, 2005 - 1:29 am UTC

so? (just means maybe you exceeded that and nothing is wrong on Oracle's part, but rather you have lots of cursors open)

tell you what, run it with sql_trace and see what is happening, you'll see your cursors, our cursors, all cursors in there.

Cursor expression in 9i

September 21, 2005 - 4:37 pm UTC

Reviewer: Dipti from NJ,USA

in the first question you said
ORA-00600: internal error code, arguments: [kokbnp2], [942], [], [], [], [],
[], []
occurrs in the oracle 8i and prior version.

I am using oracle 9i and I am still getting same error?

Please help?

Tom Kyte

Followup  

September 21, 2005 - 8:10 pm UTC

support, ora-600 = contact support.

Question in Ref Cursor in Oracle

September 22, 2005 - 4:17 am UTC

Reviewer: Oracle Knowledge Seeker from Mars

Can I get all the results that I have got in a ref Cursor into a variable instead of iterating through the cursor

For ex :

I declare a cursor as

TYPE dep_curtype IS REF CURSOR;
dep_id_cur_var dep_curtype ;

OPEN dep_id_cur_var FOR SELECT dep_id FROM JOB_DEP;

Now I want to get all the values that is fetched in dep_id_cur_var into a variable

For ex : If the values fetched from the Query SELECT dep_id FROM JOB_DEP; is 1,2,3,4,5







Tom Kyte

Followup  

September 22, 2005 - 1:52 pm UTC

no, you would have to write a query that returned a single row with the values concatenated in order to do that - search this site for stragg for example.

but you can select all of the values into an ARRAY


fetch cursor_variable BULK COLLECT into l_data;



Ref Cursor

September 22, 2005 - 4:29 am UTC

Reviewer: A reader

Can I get all the results that I have got in a ref Cursor into a variable instead of iterating through the cursor

For ex :

I declare a cursor as

TYPE dep_curtype IS REF CURSOR;
dep_id_cur_var dep_curtype ;

OPEN dep_id_cur_var FOR SELECT dep_id FROM JOB_DEP;

Now I want to get all the values that is fetched in dep_id_cur_var into a variable at once without iterating through the cursor

For ex : If the values fetched from the Query SELECT dep_id FROM JOB_DEP; is 1,2,3,4,5







some more help.

November 25, 2005 - 5:54 am UTC

Reviewer: Vijay from India

Hi Tom,
good day to you, with respect to cursor expression can this be used to form a query which returns me all the tables in a schema with a particualr column name and the table having a givn value in that column, say col1 in tables A1, A2, A3 having value 'test', so only these three tables are selected by the query.

As always thanks a lot for all your help.

Kind Regards,
Vijay.

Tom Kyte

Followup  

November 25, 2005 - 10:50 am UTC

you have to build a query that would explicitly reference the tables you want to reference (no)

some more light on this please

November 28, 2005 - 5:58 am UTC

Reviewer: Vijay from India

Hi Tom,
good day to you, with reference to above post do you mean it's not possible by a query and will require dynamic query processing please if you can shed some more light on this.

Kind Regards,
Vijay

Tom Kyte

Followup  

November 28, 2005 - 7:43 am UTC

you will need to have a query that explicitly references the tables in question. If you cannot do that with a static query - because the list of tables is not known at compile time - they dynamic sql will be your only choice.

ORA-00600: internal error code, arguments: [kokbcrwo1], [1], [2], [], [], [],

December 05, 2005 - 10:36 am UTC

Reviewer: Sameer Babu from Bangalore, India

I am getting the following error when I use the DISTINCT clause in the query which contains CURSOR expression:

ERROR at line 1:
ORA-00600: internal error code, arguments: [kokbcrwo1], [1], [2], [], [], [],
[], []

Query was:

SELECT DISTINCT a.commandTableName, CURSOR(SELECT d.tableColumnName, d.defaultValue FROM CommandColumns d WHERE d.commandId = a.commandId) FROM CommandConfig a, tCommands b, tConfig c WHERE a.commandId = b.commandId AND b.tId = c.tId AND c.eVersion = 13 AND c.tVersion = 13 AND a.isIdName IS NULL AND UPPER(c.internalName) IN (UPPER('test'))

Tom Kyte

Followup  

December 06, 2005 - 5:05 am UTC

and ora-600 means.....


[tkyte@localhost ~]$ oerr ora 600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number
[tkyte@localhost ~]$


contact support.


but they will say "thanks for pointing out that we are not raising the right error message - distinct on a cursor doesn't make sense"

(meaning, the bug is the wrong error is being reported, not that distinct is broken in this case)

In the meantime

December 06, 2005 - 9:14 am UTC

Reviewer: Bob B from Mechanicsburg, PA

In the meantime, if you weren't looking for distinct cursors (whatever that would mean), just use group by.

Tom Kyte

Followup  

December 06, 2005 - 9:48 am UTC

how do you group by a cursor?

REF Cursor

January 10, 2006 - 10:21 am UTC

Reviewer: Marc from germany

is it possible that the refcursor uses an defined sql_type or record as an output parameter.

I need a special name for the variables in the output from the select query that uses the refcursor?

I tried with the following piece of code:

I created a package were the type is defined

create or replace package types as

TYPE my_order_rec is record
( p_order_id_pk number,
p_order_id_fk number,
p_bill_id_pk number,
p_bill_no varchar2(10));

TYPE order_cur IS REF CURSOR RETURN my_order_rec;

END Types;


so I have problems with the procedure

create or replace procedure current_customers
(
p_order_id_pk in orders.order_id_pk%TYPE,
order_cur out types.my_order_rec
)

OPEN order_cur FOR select o.order_id_pk, c.bill_id_fk,
c.bill_id_pk, c.bill_no
from orders o,
bill c
where o.order_id_pk = c.bill_id_pk
and o.order_id_pk = p_order_id_pk;

END;
/

it does not work as it should,i hope you can give me a tipp

thanks

Tom Kyte

Followup  

January 10, 2006 - 11:00 am UTC

you used the wrong type for order_cur, you were missing "as" and "begin"

ops$tkyte@ORA9IR2> create or replace procedure current_customers
  2  (
  3    p_order_id_pk in  orders.order_id_pk%TYPE,
  4    order_cur     out types.order_cur
  5  )
  6  as
  7  begin
  8
  9  OPEN order_cur FOR select o.order_id_pk, c.bill_id_fk,
 10                            c.bill_id_pk, c.bill_no
 11                       from orders o,
 12                            bill c
 13                      where o.order_id_pk = c.bill_id_pk
 14                        and o.order_id_pk = p_order_id_pk;
 15
 16  END;
 17  /
 
Procedure created.
 

Thanks Tom

January 19, 2006 - 11:42 am UTC

Reviewer: Jose from Spain


Incorrect result from ref cursor

March 14, 2006 - 2:08 pm UTC

Reviewer: Branka from VA, USA

Tom,
I can not find where am I making mistake. Can you please help me.
I created sample code:
CREATE TABLE B_TEST (ID NUMBER, TEKST VARCHAR2(100 byte))
CREATE TABLE bB_TEST (ID NUMBER, TEKST VARCHAR2(100 byte))

insert into b_test VALUES(1,'A1');
insert into b_test VALUES(1,'A1');
insert into b_test VALUES(1,'A3');
insert into b_test VALUES(2,'B1');
insert into b_test VALUES(2,'B3');
insert into b_test VALUES(3,'C1');
insert into b_test VALUES(3,'C2');
insert into b_test VALUES(3,'C3');
insert into bB_test VALUES (1,'A1');
insert into bB_test VALUES(1,'A1');
insert into bB_test VALUES(1,'A2');
insert into bB_test VALUES(2,'B1');
insert into bB_test VALUES(2,'B2');
insert into bB_test VALUES(3,'C2');
insert into bB_test VALUES(3,'C3');


select * from b_test
minus
select * from bb_test

ID TEKST
--------- ------------------
1 A3
2 B3
3 C1

CREATE OR REPLACE PACKAGE COMPARE_CDS_AND_CDSP
as
TYPE refcursor_report IS REF CURSOR;
procedure compare_mpc_ipc(refcur_mpcipc OUT refcursor_report);
end;


CREATE OR REPLACE PACKAGE BODY COMPARE_CDS_AND_CDSP AS
PROCEDURE compare_mpc_ipc (refcur_mpcipc OUT refcursor_report)
AS
TYPE mpcipc_rec_type IS RECORD (
id number,
tekst VARCHAR2(100) ,
description varchar2(200));
mpcipc_report mpcipc_rec_type;
BEGIN
OPEN refcur_mpcipc FOR
SELECT id,tekst,'test'
FROM b_test
minus
SELECT id,tekst,'test'
FROM bb_test;
FETCH refcur_mpcipc INTO mpcipc_report;
-- WHILE refcur_mpcipc%FOUND LOOP
-- DBMS_OUTPUT.PUT_LINE(mpcipc_report.id||' '||mpcipc_report.tekst||' '||mpcipc_report.DESCRIPTION);
-- FETCH refcur_mpcipc INTO mpcipc_report;
END LOOP;
END compare_mpc_ipc;
END;

If I run exec COMPARE_CDS_AND_CDSP.compare_mpc_ipc(:B)
branka_dba@DIESEL1_CDS>print b

ID TEKST 'TES
---------- ---------- ----
2 B3 test
3 C1 test

If I remove coments from the SP, and print records, I will get
branka_dba@DIESEL1_CDS>exec COMPARE_CDS_AND_CDSP.compare_mpc_ipc(:B)
1 A3 test
2 B3 test
3 C1 test

I would like to use ref cursor as out variable, but with correct result.

Thanks

Branka



Tom Kyte

Followup  

March 15, 2006 - 9:24 am UTC

why are you fetching from it? once you fetch a row from that ref cursor - no one else will!

just open it, don't fetch it.

Find problem

March 14, 2006 - 2:14 pm UTC

Reviewer: Branka from VA USA

I didn't comment
FETCH refcur_mpcipc INTO mpcipc_report
Thanks

ref cursor

March 14, 2006 - 2:57 pm UTC

Reviewer: Branka from VA USA

I have one mor question, with almost same example.
Package body is a litle bit different.
I would like autput for all records that I find, but with this SP I will get only one (last one)
I know that I can create temporary table, but I would like to know if it is some other solution.
Thanks a lot.
Branka

CREATE OR REPLACE PACKAGE BODY COMPARE_CDS_AND_CDSP AS
PROCEDURE compare_mpc_ipc (refcur_mpcipc OUT refcursor_report)
AS
BEGIN
FOR x IN ( SELECT id, count(*) ptnt_count
FROM b_test
GROUP BY id
MINUS
SELECT id, count(*) ptnt_count
FROM bb_test
GROUP BY id
)
LOOP
BEGIN

OPEN refcur_mpcipc FOR
SELECT id,tekst,'test'
FROM b_test
where id=x.id
minus
SELECT id,tekst,'test'
FROM bb_test
where id=x.id;
END;
END LOOP;
END compare_mpc_ipc;
END;

Tom Kyte

Followup  

March 15, 2006 - 9:46 am UTC

explain in english what your "goal" is - not the code, phrase in text your question, from that we'll derive a query.


seems like:

with data as
( SELECT id, count(*) ptnt_count
FROM b_test
GROUP BY id
MINUS
SELECT id, count(*) ptnt_count
FROM bb_test
GROUP BY id)
select id, tekst, 'test'
from b_test where id in (select id from data)
MINUS
select id, tekst, 'test'
from bb_test
where id in (select id from data);



ref cursor

March 15, 2006 - 10:31 am UTC

Reviewer: Branka from VA, USA

I would like to compare id number of records for each id is same in 2 tables. If it is not same, than I would like more information about that record where number is different.
Thanks
Branka

Tom Kyte

Followup  

March 15, 2006 - 5:21 pm UTC

er?

Invalid cursor error calling SP via dblink

April 04, 2006 - 9:01 am UTC

Reviewer: Chris from Louisville, KY USA

Tom, there were some references above to invalid cursor's but I don't think it is the same scenario I have. I'm running two databases, both 9.2.0.7. I'm trying to call a packaged procedure in one database from the other that has a ref cursor as a parameter. Calling code looks like the following:

declare
c gui.t_cursor@rem_db;
v_column varchar2(100);
begin
gui.getcolumn@rem_db(c);
loop
fetch c into v_column;
exit when c%NOTFOUND;
dbms_output.put_line(v_column);
end loop;
end;
/

When I run this code I get ORA-01007: variable not in select list. Repeated executions then yield ORA-01001: invalid cursor (maybe because the cursor is open?).

However, if i remove the database links from the code and run it on the remote database as a local PL/SQL block, all works just fine.

Any suggestions?

Tom Kyte

Followup  

April 04, 2006 - 10:00 am UTC

ref cursors are not supported over dblinks at all.

PL/SQL Procedure returning a Ref-Cursor with "embedded Cursors

May 18, 2006 - 3:58 am UTC

Reviewer: Venkat from Bangalore,India

Hi Tom,
I ran your example , when I ran as listed below it ran fine .

SQL> declare
  2            cursor c is select owner, cursor(select * from dual) c1, 
  3  cursor(select user_id from all_users where all_users.username =
  4             all_objects.owner) c2 from all_objects;
  5    
  6              l_owner all_objects.owner%type;
  7              l_c1 sys_refcursor;
  8              l_c2 sys_refcursor;
  9    
 10              l_dummy dual.dummy%type;
 11             l_user_id all_users.user_id%type;
 12   
 13             l_cnt number := 0;
 14     begin
 15             open c;
 16             loop
 17                     fetch c into l_owner,  l_c1,l_c2 ;
 18                     l_cnt := l_cnt+1;
 19                     dbms_application_info.set_client_info(l_cnt);
 20                     exit when c%notfound;
 21                     loop
 22                             fetch l_c2 into l_user_id;
 23                             exit when l_c2%notfound;
 24                     end loop;
 25                     close l_c2;   
 26                     
 27                     loop
 28                             fetch l_c1 into l_dummy;
 29                             exit when l_c1%notfound;
 30                     end loop;
 31                     close l_c1;
 32                     
 33                      
 34             end loop;
 35             close c;
 36     end;
 37  /

PL/SQL procedure successfully completed.

But if I swap the variables in the fetch , it gives me the error as shown below 

SQL> declare
  2            cursor c is select owner, cursor(select * from dual) c1, 
  3  cursor(select user_id from all_users where all_users.username =
  4             all_objects.owner) c2 from all_objects;
  5    
  6              l_owner all_objects.owner%type;
  7              l_c1 sys_refcursor;
  8              l_c2 sys_refcursor;
  9    
 10              l_dummy dual.dummy%type;
 11             l_user_id all_users.user_id%type;
 12   
 13             l_cnt number := 0;
 14     begin
 15             open c;
 16             loop
 17                     fetch c into l_owner,  l_c2,l_c1 ;
 18                     l_cnt := l_cnt+1;
 19                     dbms_application_info.set_client_info(l_cnt);
 20                     exit when c%notfound;
 21                     loop
 22                             fetch l_c2 into l_user_id;
 23                             exit when l_c2%notfound;
 24                     end loop;
 25                     close l_c2;   
 26                     
 27                     loop
 28                             fetch l_c1 into l_dummy;
 29                             exit when l_c1%notfound;
 30                     end loop;
 31                     close l_c1;
 32                     
 33                      
 34             end loop;
 35             close c;
 36     end;
 37  /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 22

Is this a bug or am I doing something wrong here. I'm using Oracle 9i r2 on Windows  

Tom Kyte

Followup  

May 19, 2006 - 9:07 am UTC

l_cs points to (select * from dual)

select * from dual returns "not a number"

I don't know why you think the select * from dual would be appropriate to fetch into a number type?


PL/SQL Procedure returning a Ref-Cursor with "embedded Cursors

May 22, 2006 - 4:16 am UTC

Reviewer: Venkat from BANGALORE,INDIA

Thanks Tom, I understand that select * from dual does not return a number , but inside the loop when your a fetching values from cursor c

fetch c into l_c1,l_c2 ,(AS BOTH ARE REF_CURSORS) how does it matter if I fetch l_c1 or l_c2 in the same order in which it has been specified in the cursor statement , i.e.
cursor as select col1, cursor(select * from dual) c1,
cursor(select colname2 from table2) c2

what if I do
open c;
fetch c into ref_cursor_for_c2,ref_cursor_for_c1

Is my understanding wrong!

Please explain
Venkat


Tom Kyte

Followup  

May 22, 2006 - 7:55 am UTC

22 fetch l_c2 into l_user_id;


that is the LINE OF CODE that is failing, l_c2 contains a ref cursor that is pointing to "select * from dual", fetching DUAL.DUMMY which contains the letter "X" into l_user_id which is a NUMBER - well, that just doesn't work.

Forget the ref cursors, they are not the "problem" here, the problem is you fetching the string "X" into the variable l_user_id which is a NUMBER and hence cannot hold the letter "X"

Passing VARCHAR2( as a set of numbers) for WHERE ... IN Condition

May 28, 2006 - 2:54 pm UTC

Reviewer: Ranjan Srivastava from NEW DELHI, INDIA

Hi tom,

-- Scott/tiger, Table : EMP
-- Stored Procedure
CREATE OR REPLACE PROCEDURE EMP_COUNT (empNoList IN VARCHAR2, emp_list_cur OUT SYS_REFCURSOR)
AS
BEGIN
dbms_output.put_line(empNoList);
OPEN emp_list_cur FOR SELECT ename FROM Emp
WHERE EMP.EMPNO IN (empNoList) ;
END ;
/

--
DECLARE
v sys_refcursor;
a varchar2(9);
ename varchar2(20);
BEGIN
a:='7782,7788';
emp_count(a,v);
LOOP
FETCH v into ename;
EXIT when v%notfound;
dbms_output.put_line(ename);
END LOOP;
END;

When I run this code, the query in SP does not accept the set of numbers. If I type out the number in the IN part (like EMP.EMPNO IN (7782,7788)), then I get the result.

How to pass the string to the IN part?

Regards

Tom Kyte

Followup  

May 28, 2006 - 6:27 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061 <code>


Very useful feature, but need some more help on this.

September 25, 2006 - 6:34 am UTC

Reviewer: Vijay Sehgal from India

Dear Tom,
good day to you as always, I need your help on this.
The structures for requirement we have are as follows.

user_master
user_id(pk)
login_id
username
ugroup_id (fk to user_group.group_id)

user_group
ugroup_id (pk)
group_name
role_id (fk to role_id of roles_master.role_id)

roles_master
role_id(pk)
role_name
role_status

security_master
security_id(pk)
role_id(fk to roles_master.role_id)
use_case_id (fk to use_case.use_case_id)

use_case
use_case_id(pk)
use_case_name
use_case_action

portfolio_master
portfolio_id
portfolio_name
portfolio_group_id

portfolio_group
portfolio_group_id
group_name

access_mode
access_id
access_mode

portfoliogroup_user_mapping
id
portfolio_group_id(fk to portlio_master.portfolio_id)
user_id(fk to user_master.user_id)
access_id (fk to access_mode.access_id)

portfoliogroup_user_group_map
id
ugroup_id(fk to user_group.ugroup_id)
portfolio_group_id(fk to portfolio_group.portfolio_group_id)
access_id (fk to access_mode.access_id)

We need to fetch the result as follows

user_id,ugroup_id,role_id,use_case_name,use_case_action,portfolio_group_
id (direct access via portfoliogroup_user_mapping), access_id (on the portfolio_group direct access via portfoliogroup_user_mapping), portfolio_group_id(access via portfoliogroup_user_group_map), access_id (on the portfolio_group access via portfoliogroup_user_group_map ), portfolios(access to all portfolios access via portfoliogroup_user_mapping and portfoliogroup_user_group_map)

I hope I was able to make my point clear to you, so going forward this can be done by breaking the requirement into multiple queries and returning the data to java via refcursor and or object types other way is via the CURSOR FUNCTION as you have mentioned in this thread.

I would like to have your advise on this, which mechanism will be better in long run.


your help in this regard is much appreciated as always.

Kind Regards,
Vijay Sehgal


Tom Kyte

Followup  

September 25, 2006 - 2:53 pm UTC

not really clear.

You can

a) just join, joins work pretty good.

b) use the CURSOR() construct if it makes sense for you to do so

Returning the Result of a Select in a Procedure

November 19, 2006 - 9:44 am UTC

Reviewer: Avishay from ISRAEL

Hi Tom,

I'm trying to return the result of a select statment in a procedure.
I know I'm missing something here.
I tried to use a ref cursor but it keeps failing.

here is an example of the procedure:
create or replace procedure test1 ( x_1 in number,x_2 in number, ->What_goes_here out ) is
begin

select acc.acc_num
acc.name,
acc.phone_no,
acc.is_avail,
acc.approved
from accounts acc
where acc.area= x_1
and acc.acc_branch = x_2;

-- Need to return the result of this query

end ;

which cursor should I? use and how?

I know this basic, but what ever I tries I'm doing something wrong here.
Sorry for not providing a more detailed testcase. I'm not a my computer right now.

Avishay



Tom Kyte

Followup  

November 20, 2006 - 2:46 am UTC

if you were not at a computer.....
how did you submit this??????

but you are right, this is pretty basic, it is well covered in the documentation for example, heavily and well covered.

but here is an example:
</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

you need a computer to see it though :)

Thanks

November 21, 2006 - 2:24 am UTC

Reviewer: Avishay from Israel

Hi Tom,

Thanks, that helped.
I meant to say that I wasn't at MY computer. :)

I guess I'll survive the banter :)

Problems with error ORA-1722

February 12, 2007 - 10:43 am UTC

Reviewer: Tomas Gonzalez from Santiago of Chile.

Hi Tom,

I'm working with JAVA and PL/SQL. I have installed JDK 1.4_10 and ORACLE DB is 9i.

When i invoke the procedure, in any machine, this work correctly, but in othres fail wiht error ORA-1722.

PROCEDURE RPT_TIEMPOS_LLAMADAS_PLAT
(
idSupervisor IN VARCHAR2,
idSite IN VARCHAR2,
fechaInicio IN VARCHAR2, /* fecha de inicio de la consulta formato yyyymmdd */
fechaFinConsulta IN VARCHAR2,
RETORNO OUT INTEGER,
DESCRIPCION_ERROR OUT VARCHAR2,
CURSOR_CONSULTA OUT tcursor
) AS /* Resumen Hold transf lib abn hol */

qrySql VARCHAR2(10000);
BEGIN
/* Validación de parámetros obligatorios*/
RETORNO := 0;
DESCRIPCION_ERROR := 'NO HAY ERROR';

IF (fechaInicio ='0') THEN
RETORNO := 1;
DESCRIPCION_ERROR := 'Falta parámetro FECHA_INICIO CONSULTA';
RETURN;
END IF;

IF (fechaFinConsulta='0') THEN
RETORNO := 2;
DESCRIPCION_ERROR := 'Falta parámetro FECHA_HASTA CONSULTA';
RETURN;
END IF;

qrySql := 'SELECT PLAT.NOMBRE_PLAT, ' ||
' SUM(NVL(INDEAC.LLAM_ATEN, 0)) suma_LLAM_ATEN,' ||
' SUM(NVL(INDEAC.LLAM_TRANSF, 0)) suma_LLAM_TRANSF, ' ||
' SUM(NVL(INDEAC.SEG_ACD, 0)) suma_SEG_ACD, ' ||
' SUM(NVL(INDEAC.SEG_HOLD, 0)) suma_SEG_HOLD,' ||
' SUM(NVL(INDEAC.SEG_ENTRADA, 0)) suma_SEG_ENTRADA, ' ||
' SUM(NVL(INDEAC.SE_OUT, 0)) suma_SE_OUT, ' ||
' SUM(NVL(INDEAC.SEG_ACW_CC, 0)) suma_SEG_ACW_CC, ' ||
' SUM(NVL(INDEAC.SEG_ACW_SC, 0)) suma_SEG_ACW_SC, ' ||
' SUM(NVL(INDEAC.LLAM_LIB, 0)) suma_LLAM_LIB, ' ||
' SUM(INDEAC.LLAM_ABN_EN_HOLD) suma_LLAM_ABN_EN_HOLD, ' ||
' ROUND(DECODE(SUM(NVL(INDEAC.LLAM_ATEN, 0)), 0, 0, SUM(NVL(INDEAC.LLAM_TRANSF, 0)) / SUM(NVL(INDEAC.LLAM_ATEN, 0))) * 100, 2) suma_PORC_LLAM_TRANSF, ' ||
' ROUND(DECODE(SUM(NVL(INDEAC.SEG_ACD, 0) + NVL(INDEAC.SEG_HOLD, 0) + NVL(INDEAC.SEG_ENTRADA, 0) + NVL(INDEAC.SE_OUT, 0) + NVL(INDEAC.SEG_ACW_CC, 0)), 0, 0, SUM(NVL(INDEAC.SEG_HOLD, 0)) / SUM(NVL(INDEAC.SEG_ACD, 0) + NVL(INDEAC.SEG_HOLD, 0) + NVL(INDEAC.SEG_ENTRADA, 0) + NVL(INDEAC.SE_OUT, 0) + NVL(INDEAC.SEG_ACW_CC, 0))) * 100, 2) PORC_HOLD_SOBRE_TMO, '||
' ROUND(DECODE(SUM(NVL(INDEAC.LLAM_ATEN, 0)), 0, 0, SUM(NVL(INDEAC.LLAM_LIB, 0)) / SUM(NVL(INDEAC.LLAM_ATEN, 0))) *100, 2) suma_PORC_LLAM_LIB, ' ||
' ROUND(DECODE(SUM(NVL(INDEAC.LLAM_ATEN, 0)), 0, 0, SUM(NVL(INDEAC.LLAM_ABN_EN_HOLD, 0))/SUM(NVL(INDEAC.LLAM_ATEN, 0))) * 100, 2) suma_PORC_LLAM_ABN_HOLD ' ||
' FROM T_CC_PLATAFORMA plat, ' ||
' T_CC_SUPERVISOR sup, ' ||
' T_CC_SUPERVISOR_EJECUTIVO supeac, ' ||
' T_CC_INDICES_EJECUTIVO indeac, ' ||
' T_CC_EJECUTIVO_EAC ejec ' ||
'WHERE ejec.LOGIN_EAC IS NOT NULL ';

IF idSupervisor <> '0' THEN
qrySql := qrySql || 'AND sup.ID_SUPERVISOR =''' || idSupervisor || '''';
END IF;

IF idSite <> '0' THEN
qrySql := qrySql || ' AND indeac.ID_SITE = ''' || idSite || '''';
END IF;

qrySql := qrySql || ' AND indeac.LOGIN_EAC = ejec.LOGIN_EAC ' ||
' AND sup.ID_SUPERVISOR = supeac.ID_SUPERVISOR ' ||
' AND ejec.RUT = supeac.RUT ' ||
' AND ejec.Dv = supeac.Dv ' ||
' AND indeac.ID_PLATAFORMA = plat.ID_PLATAFORMA';


qrySql := qrySql || ' AND TRUNC(INDEAC.FECHA) BETWEEN TO_DATE(''' || fechaInicio || ''', ''YYYYMMDD'') AND TO_DATE(''' || fechaFinConsulta || ''', ''YYYYMMDD'')';

qrySql := qrySql || ' GROUP BY plat.NOMBRE_PLAT';

DESCRIPCION_ERROR := qrySql;
OPEN CURSOR_CONSULTA FOR qrySql;
EXCEPTION
WHEN OTHERS THEN
RETORNO := SQLCODE;
DESCRIPCION_ERROR := SQLERRM;
END;

In J2EE is invoke of this way:

CallableStatement stmt = con
.prepareCall("{ call Pkg_Reportes_Call_Center.RPT_TIEMPOS_LLAMADAS_PLAT (?,?,?,?,?,?,?) }");

stmt.setString(1, idSupervisor);
stmt.setString(2, idSite);
stmt.setString(3, fechaInicio);
stmt.setString(4, fechaFinConsulta);
stmt.registerOutParameter(5, OracleTypes.INTEGER);
stmt.registerOutParameter(6, OracleTypes.VARCHAR);
stmt.registerOutParameter(7, OracleTypes.CURSOR);
stmt.execute();


if ("0".equals(stmt.getString(5))) {
rs = (ResultSet) stmt.getObject(7);


And in the run-time fail with a exception SQL java.sql.SQLException: ORA-01722

This is only machine. ¿Why? Please, your help whith this problem.

Is a web apliccation with tomcat 5.0.28

Regards,
Tomas from Santiago of Chile


Tom Kyte

Followup  

February 12, 2007 - 11:39 am UTC

are you using strings to store numbers.
are you implicitly converting strings to numbers.

The maximum number of arguments in a CASE expression is 127

June 20, 2007 - 10:00 am UTC

Reviewer: vithal from Singapore

Hi,

the following case stament is working in 9i where number of arguments in a CASE expression are 148 but the same is not working in oracle 10g.

Can you please suggest, is this a bug in 10g?




create table emp(no number)

select case
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
when no = 1 then 1
end from emp
Tom Kyte

Followup  

June 20, 2007 - 11:21 am UTC

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions004.htm#i1033392


...
The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN ... THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression.
....

case

June 20, 2007 - 12:41 pm UTC

Reviewer: A reader

Tom,

that's fine the same was present in 9i docs. but the sql query which i have sent you is working fine in 9i...

Why this is not working in 10g?


Tom Kyte

Followup  

June 20, 2007 - 1:29 pm UTC

they fixed something that wasn't working properly in the earlier release. You are pointing at the wrong version technically here - it was a bug that it worked before.

Case expression

June 21, 2007 - 2:22 am UTC

Reviewer: A reader

Tom,

That¿s perfectly fine, but now the issue is while database upgrade, we have to change lots of code just because of this fix.

I am planning to raise the TAR/SR to oracle to get this fix.

Can you please suggest, is this correct?

Many Thanks,
Vithal

Tom Kyte

Followup  

June 21, 2007 - 10:26 am UTC

they will tell you simply "it is working as documented", sorry - don't know what else to tell you.

out of scope

December 05, 2008 - 2:04 pm UTC

Reviewer: dxl from uk

i have a question about the test case below...my second embedded cursor select won't run because the column that is joined on cannot be seen by the outer select statement.

How can i get around this?

set lines 200
set pages 2000
variable p_start_date varchar2(10)
variable p_end_date varchar2(10)

exec :p_start_date := '04/12/2008';
exec :p_end_date := '14/12/2008';

create table t1 ( c1 date );

insert into t1 values (TRUNC(SYSDATE-1));
insert into t1 values (TRUNC(SYSDATE-2));
insert into t1 values (TRUNC(SYSDATE-3));
insert into t1 values (TRUNC(SYSDATE-4));
insert into t1 values (TRUNC(SYSDATE-5));
insert into t1 values (TRUNC(SYSDATE-6));

select * from t1;

select dummy_date,
CURSOR(
select c1
from t1
where t1.c1 = d.dummy_date
) as inner_cursor
FROM (
SELECT CAST( TRUNC(( TO_DATE(:p_start_date, 'DD/MM/YYYY') ) + (ROWNUM) - 1) AS DATE) as dummy_date
FROM all_tab_columns
WHERE ROWNUM <= ( (( TO_DATE(:p_end_date, 'DD/MM/YYYY') - ( TO_DATE(:p_start_date, 'DD/MM/YYYY') )) + 1) * 1 )
) d;

---------------
-- The results i get are:

C1
-------------------
04/12/2008 00:00:00
03/12/2008 00:00:00
02/12/2008 00:00:00
01/12/2008 00:00:00
30/11/2008 00:00:00
29/11/2008 00:00:00

6 rows selected.


DUMMY_DATE INNER_CURSOR
------------------- --------------------
04/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

C1
-------------------
04/12/2008 00:00:00

05/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
06/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
07/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
08/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
09/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
10/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
11/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
12/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
13/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


DUMMY_DATE INNER_CURSOR
------------------- --------------------
14/12/2008 00:00:00 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


11 rows selected.



-- Now using this sql and restricting the inner selects using a rownum doesn't run:


select dummy_date,
CURSOR(
select c1
from (
select c1
from t1
where t1.c1 = d.dummy_date
)
where rownum < 3
) as inner_cursor
FROM (
SELECT CAST( TRUNC(( TO_DATE(:p_start_date, 'DD/MM/YYYY') ) + (ROWNUM) - 1) AS DATE) as dummy_date
FROM all_tab_columns
WHERE ROWNUM <= ( (( TO_DATE(:p_end_date, 'DD/MM/YYYY') - ( TO_DATE(:p_start_date, 'DD/MM/YYYY') )) + 1) * 1 )
) d;



-- i get this error


where t1.c1 = d.dummy_date
*
ERROR at line 7:
ORA-00904: "D"."DUMMY_DATE": invalid identifier

Tom Kyte

Followup  

December 09, 2008 - 12:14 pm UTC

        CURSOR(
                select c1
                from ( 
                        select c1
                        from t1
                        where t1.c1 = d.dummy_date
                       )
                where rownum < 3                       
                )  as inner_cursor



you do not need the inline view given your sql.

        CURSOR(
                        select c1
                        from t1
                        where t1.c1 = d.dummy_date and rownum < 3
                )  as inner_cursor


Nested Cursor and Strong Ref Cursor

April 30, 2009 - 6:44 pm UTC

Reviewer: Ed Girard from Dallas, TX


Below is a package and execution results showing the passing of PL/SQL OUT parameter containing a nested cursor.

The first procedure - get_Dept_Emps_Weak - uses a weak cursor (SYS_REFCURSOR) which works fine. However, I'd prefer to use a strong ref cursor as demonstrated in the second procedure - get_Dept_Emps_Strong.

For the sake of compilation I've commented out the version of get_Dept_Emps_Strong I'd like to use. The procedure get_Dept_Emps_Strong uses the dept_Employees_curType record type definition in the spec and this type definition uses the get_Emp_Job_recType type definition declared previously to hold the employees from the nested cursor. When un-commented, it produces the error "PLS-00382: expression is of wrong type".

Am I doing something wrong or do nested cursors not support strong (defined) ref cursors when used as a PL/SQL OUT parameter.

Thank you.


scott@dev> CREATE OR REPLACE PACKAGE TEST_NESTED_PKG IS
  2  
  3     -- Type Declarations
  4     TYPE get_Emp_Job_recType IS RECORD(
  5        emp_Name scott.emp.ename%TYPE,
  6        job      scott.emp.job%TYPE);
  7     TYPE emp_Job_curType IS REF CURSOR RETURN get_Emp_Job_recType;
  8  
  9     TYPE get_Dept_Employees_recType IS RECORD(
 10        dept_Name scott.dept.dname%TYPE,
 11        employees get_Emp_Job_recType);
 12     TYPE dept_Employees_curType IS REF CURSOR RETURN get_Dept_Employees_recType;
 13  
 14  
 15     -- Procedure Declarations
 16     PROCEDURE get_Dept_Emps_Weak(po_Dept_Emps_curVar OUT SYS_REFCURSOR);
 17  
 18     PROCEDURE get_Dept_Emps_Strong(po_Dept_Emps_curVar OUT SYS_REFCURSOR);
 19     --PROCEDURE get_Dept_Emps_Strong(po_Dept_Emps_curVar OUT dept_Employees_curType);
 20  
 21  END TEST_NESTED_PKG;
 22  /

Package created.

scott@dev> show errors;
No errors.
scott@dev> 
scott@dev> 
scott@dev> CREATE OR REPLACE PACKAGE BODY TEST_NESTED_PKG IS
  2  
  3     /* Retrieve Departments and Employes in a NESTED Cursor (Weak Ref Cursor  */
  4     PROCEDURE get_Dept_Emps_Weak(po_Dept_Emps_curVar OUT SYS_REFCURSOR) IS
  5     BEGIN
  6  
  7        OPEN po_Dept_Emps_curVar FOR
  8           SELECT d1.dname AS dept_Name,
  9                  CURSOR (SELECT e2.ename,
 10                                 e2.job
 11                          FROM   scott.emp e2
 12                          WHERE  e2.deptno = d1.deptno)   employees
 13           FROM   scott.dept d1;
 14  
 15     END get_Dept_Emps_Weak;
 16  
 17     /* Retrieve Departments and Employes in a NESTED Cursor (Strong Ref Cursor  */
 18     PROCEDURE get_Dept_Emps_Strong(po_Dept_Emps_curVar OUT SYS_REFCURSOR) IS
 19     --PROCEDURE get_Dept_Emps_Strong(po_Dept_Emps_curVar OUT dept_Employees_curType) IS
 20     BEGIN
 21  
 22        OPEN po_Dept_Emps_curVar FOR
 23           SELECT d1.dname AS dept_Name,
 24                  CURSOR (SELECT e2.ename,
 25                                 e2.job
 26                          FROM   scott.emp e2
 27                          WHERE  e2.deptno = d1.deptno)   employees
 28           FROM   scott.dept d1;
 29  
 30     END get_Dept_Emps_Strong;
 31  
 32  END TEST_NESTED_PKG;
 33  /

Package body created.

scott@dev> show errors;
No errors.
scott@dev> 
scott@dev> 
scott@dev> var l_test_cursor refcursor
scott@dev> 
scott@dev> set autoprint on
scott@dev> 
scott@dev> exec TEST_NESTED_PKG.get_Dept_Emps_Weak(:l_test_cursor);

PL/SQL procedure successfully completed.


DEPT_NAME      EMPLOYEES
-------------- --------------------
ACCOUNTING     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME      JOB
---------- ---------
CLARK      MANAGER
KING       PRESIDENT
MILLER     CLERK

RESEARCH       CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME      JOB
---------- ---------
SMITH      CLERK
JONES      MANAGER
SCOTT      ANALYST
ADAMS      CLERK
FORD       ANALYST

SALES          CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME      JOB
---------- ---------
ALLEN      SALESMAN
WARD       SALESMAN
MARTIN     SALESMAN
BLAKE      MANAGER
TURNER     SALESMAN
JAMES      CLERK

6 rows selected.

OPERATIONS     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2


Tom Kyte

Followup  

April 30, 2009 - 9:23 pm UTC

the cursor returned by the query is always a weak one - it doesn't have a 'type', it is not bound to any specific type - it's scope is the sql statement.

I don't know of any construct that would let you have this be a strongly typed ref cursor.


what you have is a cursor that returns a varchar and a cursor variable (specifically NOT a varchar and a record - that cursor structure is a varchar + ref cursor)

And if you try to put the ref cursor in there, you'll just get:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/4     PL/SQL: Declaration ignored
12/7     PLS-00989: Cursor Variable in record, object, or collection is
         not supported by this release

Nested Cursor and Strong Ref Cursor

May 01, 2009 - 10:47 am UTC

Reviewer: Ed Girard from Dallas, TX


Thank you Tom for the fast response.

As you mentioned, the "PLS-00989: Cursor Variable in record, object, or collection is not supported by this release" error is exactly what I too received when I tried to include either a ref cursor or record type in the second TYPE definition.

Regarding the PLS-00989 error: it seems to infer that including a cursor variable in a record or collection may be supported in some future release hence why this type of verbiage. Is this true?

Thank you as always for your assistance.

Tom Kyte

Followup  

May 01, 2009 - 11:05 am UTC

is it true that the message indicates that it might be? sure

is it on the drawing board? not that I'm aware of


cursor

May 01, 2009 - 9:55 pm UTC

Reviewer: A reader

Tom:

1.  Instead of writing this, can't you just do it in SQL join like

 for x in ( select * from users )
   loop
        for y in ( select * from contacts where userid=x.userid )
        loop
           ...
        end loop;
        for z in ( select * from addresses where userid=x.userid )
        loop
            ...
        end loop;
   end loop;


for x in (select * from users a, contacts b, addresses c
          where a.userid = b.userid and a.userid=c.userid )
loop
.....
end loop;


2. Also why use CURSOR in SQL. cant we do this like this

select u.userid,
(select contactid from usercontacts where userid=a.userid),
(select addressid from addresses where userid=a.userid)
from users a

instead of this

SELECT U.UserId, U.LoginName, U.PassWord, U.ForeName, 
       U.SureName, U.Gender, U.Description, U.ModDate, 
       U.ModUser, DSCore.getState(U.State) AS State,
       U.DefaultLanguageId,
       CURSOR(SELECT C.UserContactId, 
                DSCore.getContactType(C.ContactTypeId) AS ContactType,
                C.Value, C.Description, 
                DSCore.getState(C.State) AS State, C.ModUser
              FROM   UserContacts C
          WHERE  C.UserId = U.UserId) AS Contacts,
       CURSOR(SELECT A.UserAddressId, 
                DSCore.getAddressType(A.AddressTypeId) AS AddressType,
                A.StreetNo, A.ZipCode, A.City, A.Country,
                DSCore.getState(A.State) AS State, A.ModUser
          FROM   UserAddresses A
              WHERE  A.UserId = U.UserId) AS Addresses
FROM   Users U
WHERE  U.UserId = pUserId; 

Tom Kyte

Followup  

May 02, 2009 - 12:07 pm UTC

1) Not in general. If a user has 5 addresses, and 10 contacts your query would return 50 rows (5x10). You would be joining users to addresses (5) and users to contacts (10) and then performing a cartesian join (50) of those two sets.


2) think 1:M relationships please. A user has many addresses. A user has many contacts. A scalar subquery as you suggest can only return 0 or 1 rows - else it raises an error

It is almost 2010; do I need to still do this for the java guys?

July 20, 2009 - 12:12 pm UTC

Reviewer: Tom from Leesburg, VA USA

The Java front-end team has a typical search screen where the user enters filtering data. The filtering data is passed to a package procedure where I dynamically create the sql statement (utilizing the latest technique from your site 1=1). The Java team is asking me to make changes to my procedure to allow them to pass me another parameter so they can iterate through the data set from certain starting points. They want to re execute the package procedure every time the user scrolls past another 500 records. I have told them I can¿t believe I would need to do this in 2009. I am opening a ref cursor for them and I would think that would suffice.

I have been told that they are using IBATIS and FLEX. They said that they could iterate up and down the ref_cursor if I let them execute the select statement directly against the tables instead of using the stored procedure. I have said no to them at this time and have asked them to do more research on how stateless web apps handle opening and displaying 10 million rows of data without bring all of the data back to the middle in a single download.

Am I correct in telling them ¿No¿. Can you explain to me how the stateless web world and ref_cursors were designed to work together while minimizing unnecessary data movement across the network?

Thank You!

Tom Kyte

Followup  

July 24, 2009 - 11:47 am UTC

sounds like their 'framework' would suck all of the data out of the database during the first page (brilliant - no?) and then page through it.

Ask them "is that true" and let us know - cause if it is - say in no uncertain terms "NO"

Function returns more than one value

May 19, 2011 - 9:38 am UTC

Reviewer: suriya from India

hi tom,
i want function to return more than one value for the following query,i tried using ref cursor but it didn't wrk ..help me tom.


select department_id,job_id,sum(salary) sal from employees group by rollup(department_id,job_id)
Tom Kyte

Followup  

May 23, 2011 - 10:37 am UTC

I have no idea what you mean at all. Not a clue.


SQL Course Helo

February 01, 2013 - 8:52 am UTC

Reviewer: Shipra

I am not a programmer but I have this SQL subject this session and have to prepare for it. What all topics should be covered in it?
And has anyone studied from this course www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help