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!

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,

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,
How does one code PL/SQL to fetch from this cursor?

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

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

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,

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

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!

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++; ... ;)

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

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>  

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

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

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

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;
}

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.

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

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> 

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

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:

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:
 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 

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:

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,

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;

Hello Tom,

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

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)

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

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,

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

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?!

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

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

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.
 

Working in version 9.2.0.5 of oracle

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

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

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,
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

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,

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

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

Robert.

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,
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.

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,
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,
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.

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. 

> 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.

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

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)...
 

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

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?

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)

I had set open_cursors = 1000

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?

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

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

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.

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

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'))

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

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

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.
 

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

I didn't comment
FETCH refcur_mpcipc INTO mpcipc_report
Thanks

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;

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, 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?

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  

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

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

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

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

Hi Tom,

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

I guess I'll survive the banter :)

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

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,

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,

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

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

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

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

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

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

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:

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; 

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!

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)

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