how to display cursor result-set in a query?

[bhe]

i have a query like:

select column1
,      column2
,      cursor (select column3
               from   table2
               where  table2.table1_id = table1.id
              ) as column4
from   table1`
where  ...
;

When i execute the query in a query-window, the results are presented as:

value column1, value column2, cursor..
next row, etc.

when i double click the .. next to the cursor i get the following Oracle error message: Ora-01001: invalid cursor.

Is it possible to display the results of the cursor?

thanks.

 

[bhe]

p.s. the query works fine in SQL+ and PL/SQL code. So it's not a problem with the query but with displaying the data.

 

[Marco Kalter]

This works fine for me. I tried the following:

select deptno,
       dname,
       cursor (select empno from emp where emp.deptno = dept.deptno) as employees
  from dept

This is based on the dept/emp demo tables. Can you try this and let me know if this works for you as well?

 

[bhe]

I have no idea why, but today the cursor data displays properly (both with your query and with mine).
So this issue is closed.

Marco, thanks for the reply!

 

[D.]

bhe, Marco,

I have had this happen too, but opening a new session usually fixes it. Only happens occasionally. I had put it down to my Net80 client, and the fact we're still on v8.1.7 of the DB, but maybe it is a sporadic memory problem with the app. or operating system (Win2000). It's not a problem for me though, and I wouldn't call it a bug, as it's not too difficult to sort out.

It's very difficult to replicate, and I can't at the moment with the query which has been known to do it. Sorry.

Just thought I'd let you know that you are not alone.

D.

 

[Marco Kalter]

Okay, thanks.