ora92>select deptno,cursor (select count(*) from emp where deptno = dept.deptno)
2 from dept
3 /

DEPTNO CURSOR(SELECTCOUNT(*
---------- --------------------
10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

COUNT(*)
----------
3

20 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

COUNT(*)
----------
5

30 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

COUNT(*)
----------
6

40 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

COUNT(*)
----------
0

Can we get rid of "CURSOR STATEMENT : 2"(Also I don't know what is its significance there) from the output so that the output is in a readable format.

Thanks

Hi Tom,

I think there exists one more difference:-

In case of SCALAR subqueries you can't select multiple columns on the inner select, it will return

ORA-00913: too many values.

But for Cursor expressions this is not an issue. Since it is a cursor variable it returns whatever number of columns you need.

Please validate this.

Thanks

Vipin

well.... there are tricks



ops$tkyte@ORA920> create or replace type myScalarType as object
  2  ( ename varchar2(30),
  3    job      varchar2(10),
  4    hiredate date )
  5  /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select dname, t.data.ename, t.data.job, t.data.hiredate
  2    from (
  3  select dname, (select myScalarType(ename,job,hiredate)
  4                   from emp
  5                  where rownum = 1
  6                    and emp.deptno = dept.deptno ) data
  7    from scott.dept
  8         ) t
  9  /

DNAME          DATA.ENAME                     DATA.JOB   DATA.HIRE
-------------- ------------------------------ ---------- ---------
ACCOUNTING     CLARK                          MANAGER    09-JUN-81
RESEARCH       SMITH                          CLERK      17-DEC-80
SALES          ALLEN                          SALESMAN   20-FEB-81
OPERATIONS

ops$tkyte@ORA920>
ops$tkyte@ORA920> select dname,
  2         ltrim(substr( data, 1, 30 )) ename,
  3         ltrim(substr( data, 31, 10 )) job,
  4         to_date(substr( data, 40 ),'yyyymmddhh24miss') hiredate
  5    from (
  6  select dname,
  7        (select rpad(ename,30)||rpad(job,30)||to_char(hiredate,'yyyymmddhh24miss')
  8           from emp
  9          where rownum = 1
 10            and emp.deptno = dept.deptno) data
 11    from scott.dept
 12         )
 13  /

DNAME          ENAME                          JOB        HIREDATE
-------------- ------------------------------ ---------- ---------
ACCOUNTING     CLARK                          MANAGER    09-JUN-81
RESEARCH       SMITH                          CLERK      17-DEC-80
SALES          ALLEN                          SALESMAN   20-FEB-81
OPERATIONS

ops$tkyte@ORA920>
 

Hi Tom,

But the select statement within the select statement should always return single row , right?

Otherwise we will get the following error

ORA-01427: single-row subquery returns more than one row.

But this is not be the case with Cursor expression.


Please comment on this

Hi Tom,

If we use cursor expressions, isn't there a risk of overflowing the open_cursors parameter and finally get the infamous error "Maximum number of cursors exceeded....".

Suppose my query is returning 5000 rows and I am using this query to form my XML document (where in cursor expressions are used), there are all potential chances that I might overflow this value.

Please advise.

And you won't be able to close them when you use them in a cursor expression. Am I right?

Hi Tom,

If we execute the following query and if it returns 5000 rows, won't you get the MAXIMUM OPEN CURSORS..error here if open_cursors parmater value is less than 5000?

select deptno,cursor (select empno, ename, sal
from emp
where deptno = dept.deptno)
from dept


Now how can we ensure that the cursors are closed here and hence prevent this error.

I think the only way is to set the open_cursors value to as high as 5000 (which will surely have other repercussions.)

So surely I will not use CURSOR expressions when so many rows are returned. But wouldn't this be a restriction to cursor expression when we compare them with SCALAR subqueries (select in select) or else can this be averted.

Please advise.

hi Tom
this is an interesting question. If I run the above
query in sqlplus, why dont we run into "max cursor"
issue - if there are 5000 cursors? I understand that the
client has to close them - but if you open simultaneously
5K cursors then you would have exceeded the limit before
the client even closes the cursors , is n't it?

Thanx!

that explains it!!
So if our cursor limit is 4000 - but we set
arraysize to 5000 - say and then run the above query
AND if the query returns more than 4000 rows - then we will
hit the "max open cursor" error, right?

Thanx!

give it a try. (and you'll find no, it doesn't)

the cursor isn't opened till you use it, it is closed when you close it.  


ops$tkyte@ORA920LAP> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     30
ops$tkyte@ORA920LAP> set arraysize 100
ops$tkyte@ORA920LAP> select object_name, cursor(select * from dual) from all_objects where rownum <= 100;


.....
/13b65381_AccessibleState      CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

D
-
X


100 rows selected.

ops$tkyte@ORA920LAP>


it just works. 

More over if you have OPEN_CURSORS value set to say 25, even if your array size is 15, if 11 other concurrent sessions are opening a cursor each in their sessions, again I think you are at a potential threat while you retreive your next set of 15 cursors.

Tom, could you please enlighten/correct us on this.

that example explains it!

sorry, my mistake.

But there are still doubts which lingers. As you said unless you open these cursors simultenously it is not a problem. If you refer to my first doubt :-

"Suppose my query is returning 5000 rows and I am using this query to form my XML document (where in cursor expressions are used), there are all potential chances that I might overflow this value."

If I am using DBMS_XMLGEN to generate an XML using cursor expressions then all these cursors will be simultenously opened and wouldn't I get the MAX CURSORS error here???

Hi Tom,

I just tried out this in the following manner:-

SQL> sho parameters open%
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
open_links                           integer     4
open_links_per_instance              integer     4
read_only_open_delayed               boolean     FALSE
session_max_open_files               integer     10
SQL> 


As discussed earlier, I was trying to use cursor expressions in XML generations as shown below.

DECLARE
v_clob CLOB;
v_ctx  dbms_xmlgen.ctxtype;
BEGIN
  v_ctx := dbms_xmlgen.newcontext('select object_name, cursor(select count(*) from all_objects b
  WHERE b.owner = a.owner
  AND   b.object_name = a.object_name)
  FROM  all_objects a');

/* Please note below that I have set maxrows to 100. */

  dbms_xmlgen.setmaxrows(v_ctx,100);   
  v_clob := dbms_xmlgen.getxml(v_ctx);
END; 



This ran without an error and please note the query results for current open cursors in the same session:-



select b.name, a.value
from
v$mystat a,
v$statname b
where a.statistic# = b.statistic#
and b.name like 'opened cursor%'

opened cursors cumulative    122
opened cursors current            103

Now I ran the XML generation block once again which again ran without error and open cursor query showed me the
following results:-

opened cursors cumulative    234
opened cursors current            204


Now I ran the XML generation block once again and I got the maximum open cursors exceeded error.open cursor query showed me the following results:-

opened cursors cumulative    341
opened cursors current            205


So it means that If we try to generate this XML with setmaxrows as any thing more than 300 or probably nearer to 300, it will give me the error (which it gave when I ran in another session.)

Please comment.


 

Tom

The docs state quite clearly under Restrictions that we cannot have nested cursor expressions inside views. This appears to be untrue to a degree and I'd like some clarification. The demo below shows a cursor expression being happily contained in a view, but this is only accessible from server-side sqlplus. When I try to select from the view using latest TOAD, I get rows returned but with NULL data. Same of SQL*Plus. If I try a really old SQL*Plus client (such as 8.0.6) I get internal error codes.

Can you shed some light on what we should do with CURSOR and views ? Version = 9.2.0.3.

920>
920> create type myRowType as object
2 ( x int
3 , y char(1)
4 );
5 /

Type created.

920>
920> create type myTableType as table of myRowType;
2 /

Type created.

920>
920> create function plfx (
2 cur_in sys_refcursor
3 ) return myTableType pipelined as
4
5 type typ_aa_allobs is table of all_objects%rowtype
6 index by binary_integer;
7 aa_allobs typ_aa_allobs;
8
9 rec_out myRowType := myRowType(null, null);
10
11 begin
12
13 fetch cur_in bulk collect into aa_allobs;
14
15 if aa_allobs.count > 0 then
16
17 for i in aa_allobs.first .. aa_allobs.last loop
18
19 rec_out := myRowType ( aa_allobs(i).object_id,
20 substr(aa_allobs(i).object_type,1,1) );
21
22 pipe row (rec_out);
23
24 end loop;
25
26 end if;
27
28 return;
29
30 end;
31 /

Function created.

920>
920> select *
2 from table ( plfx ( cursor (
3 select *
4 from all_objects
5 where rownum <= 5
6 ) ) );

X Y
---------- -
1474420 J
1464692 J
1479676 J
1470748 J
1479479 J

920>
920> create view vplfx
2 as
3 select *
4 from table ( plfx ( cursor (
5 select *
6 from all_objects
7 where rownum <= 5
8 ) ) );

View created.

920>
920> select *
2 from vplfx;

X Y
---------- -
1474420 J
1464692 J
1479676 J
1470748 J
1479479 J

Regards
Adrian

I think they were talking about this cursor expression:

ops$tkyte@ORA920> create or replace view v as select dname, cursor(select ename from emp where emp.deptno=dept.deptno)
  2  from dept;
create or replace view v as select dname, cursor(select ename from emp where emp.deptno=dept.deptno)
                                          *
ERROR at line 1:
ORA-22902: CURSOR expression not allowed



a selected cursor variable.  


Please -- please -- file a bug for your other observed behaviour with support! 

Thanks. I see what they mean now.

As for the odd behaviour from client tools, I'll raise an iTAR. It's wierd, even server-side operations such as a CTAS or an INSERT..SELECT from the view executed from the client tools populate with NULL data...

Regards
Adrian

Dear Sir,
As we are new to Oracle ,We want to know what is a "scalar
subquery".How is it different from other sub queries?Could you please explain with some examples?Clarity is expected.

Back to the original question, I too am looking for a way in just sql to "unnest" if you will the individual elements in a refcursor.  My motivation is that I have many many functions that return refcursors and I would like to reuse all this code inside of SQL without having to write wrapper functions to convert the cursor expressions and refcursors into object tables.  Here is a somewhat lengthy example to clarify.  In the end I am looking for some kind of sytanx, Oracle supplied function call, or whatever that will allow for the unpacking of the cursor expression and refcursor.  As you can see the last example gets close but goes way out of the way to do what I need.


In this code stream you will see:
1) a select that uses a cursor expression.
2) a similar select casting a nested select into a database type.
3) a function that returns a recursor substituted into setup #1 that works.
4) same function put in step #2 but don't work (not a surprise really).
5a) example of how XMLSEQUENCE can return a set of rows from a refcursor.
5b) example of potential solution using XMLSEQUENCE and the function.

You will see that step#5 uses a function returning a refcursor and thus does not tie me to a specific datatype. Via LEFT-CORRELATION the function can take the parameter from table expressions to its left in the from clause and thus returns the rows desired. Please also note that XMLSEQUENCE converts the returned refcursor into a table construct (aka set of rows) from which individual elements can be referenced (or in this case extracted).

Seems rather round about however as the cursor expression clearly understands the elements it is giving, as indicated by the results of step#1 (all columns are named and type correctly in the nested cursors). Seems to me there ought to be some Oracle supplied function that works just like XMLSEQUENCE by accepting a refcursor but that returns a real collection of rows thus allowing the conversion of cursor expressions/refcursors into virtual tables so to speak. But alas I know not what it is.

Can anyone help me. Thanks, Kevin

Please forgive the uglyness of the post, I don't know how to format it here.

create table dept (deptno number,dename varchar2(10),loc varchar2(10))
/

insert into dept values (1,'dept1','loc1');
insert into dept values (2,'dept2','loc2');

create table emp (empno number, deptno number,ename varchar2(10),job varchar2(10),hiredate date,sal number)
/

insert into emp values (11,1,'john','cook',sysdate,31000);
insert into emp values (13,1,'jim','cook',sysdate,32000);
insert into emp values (14,1,'joe','cook',sysdate,33000);
insert into emp values (21,2,'sue','cook',sysdate,31000);
insert into emp values (22,2,'sally','cook',sysdate,32000);
insert into emp values (23,3,'sarah','cook',sysdate,33000);

commit
/


SQL> set echo on
SQL> set doc on
SQL> /*
DOC>create or replace type o_emp is object
DOC>(
DOC>  EMPNO                          NUMBER
DOC>, DEPTNO                          NUMBER
DOC>, ENAME                          VARCHAR2(10)
DOC>, JOB                          VARCHAR2(10)
DOC>, HIREDATE                          DATE
DOC>, SAL                          NUMBER
DOC>)
DOC>/
DOC>
DOC>create or replace type c_emp is table of o_emp
DOC>/
DOC>
DOC>*/
SQL> 
SQL> 
SQL> clear columns
SQL> 
SQL> col ename format a6
SQL> col job format a4
SQL> 
SQL> select dept.*,cursor(select * from emp where emp.deptno = dept.deptno) c1
  2  from dept
  3  /

    DEPTNO DNAME      LOC        C1
---------- ---------- ---------- --------------------
         1 dept1      loc1       CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

     EMPNO     DEPTNO ENAME  JOB  HIREDATE         SAL
---------- ---------- ------ ---- --------- ----------
        11          1 john   cook 29-MAY-05      31000
        13          1 jim    cook 29-MAY-05      32000
        14          1 joe    cook 29-MAY-05      33000

3 rows selected.

         2 dept2      loc2       CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

     EMPNO     DEPTNO ENAME  JOB  HIREDATE         SAL
---------- ---------- ------ ---- --------- ----------
        21          2 sue    cook 29-MAY-05      31000
        23          2 sally  cook 29-MAY-05      32000
        24          2 sarah  cook 29-MAY-05      33000

3 rows selected.


2 rows selected.

SQL> 
SQL> select *
  2  from dept
  3       ,table(cast(multiset(select * from emp where emp.deptno = dept.deptno) as c_emp))
  4  /

    DEPTNO DNAME      LOC             EMPNO     DEPTNO ENAME  JOB  HIREDATE         SAL
---------- ---------- ---------- ---------- ---------- ------ ---- --------- ----------
         1 dept1      loc1               11          1 john   cook 29-MAY-05      31000
         1 dept1      loc1               13          1 jim    cook 29-MAY-05      32000
         1 dept1      loc1               14          1 joe    cook 29-MAY-05      33000
         2 dept2      loc2               21          2 sue    cook 29-MAY-05      31000
         2 dept2      loc2               23          2 sally  cook 29-MAY-05      32000
         2 dept2      loc2               24          2 sarah  cook 29-MAY-05      33000

6 rows selected.

SQL> 
SQL> create or replace function ftemp1 (deptno_p in number) return sys_refcursor as
  2      c1 sys_refcursor;
  3  begin null;
  4      open c1 for select * from emp where deptno = deptno_p;
  5      return (c1);
  6  end;
  7  /

Function created.

SQL> 
SQL> select dept.*,ftemp1(dept.deptno) c1
  2  from dept
  3  /

    DEPTNO DNAME      LOC        C1
---------- ---------- ---------- --------------------
         1 dept1      loc1       CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

     EMPNO     DEPTNO ENAME  JOB  HIREDATE         SAL
---------- ---------- ------ ---- --------- ----------
        11          1 john   cook 29-MAY-05      31000
        13          1 jim    cook 29-MAY-05      32000
        14          1 joe    cook 29-MAY-05      33000

3 rows selected.

         2 dept2      loc2       CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

     EMPNO     DEPTNO ENAME  JOB  HIREDATE         SAL
---------- ---------- ------ ---- --------- ----------
        21          2 sue    cook 29-MAY-05      31000
        23          2 sally  cook 29-MAY-05      32000
        24          2 sarah  cook 29-MAY-05      33000

3 rows selected.


2 rows selected.

SQL> 
SQL> select *
  2  from dept
  3       ,table(cast(multiset(ftemp1(dept.deptno)) as c_emp))
  4  /
    ,table(cast(multiset(ftemp1(dept.deptno)) as c_emp))
                *
ERROR at line 3:
ORA-00904: "MULTISET": invalid identifier


SQL> 
SQL> 
SQL> select value(c1).getclobval()
  2  from table(xmlsequence(ftemp1(1))) c1
  3  /

VALUE(C1).GETCLOBVAL()
--------------------------------------------------------------------------------
 <ROW>
  <EMPNO>11</EMPNO>
  <DEPTNO>1</DEPTNO>
  <ENAME>john</ENAME>
  <JOB>cook</JOB>
  <HIREDATE>29-MAY-05</HIREDATE>
  <SAL>31000</SAL>
 </ROW>

 <ROW>
  <EMPNO>13</EMPNO>
  <DEPTNO>1</DEPTNO>
  <ENAME>jim</ENAME>
  <JOB>cook</JOB>
  <HIREDATE>29-MAY-05</HIREDATE>
  <SAL>32000</SAL>
 </ROW>

 <ROW>
  <EMPNO>14</EMPNO>
  <DEPTNO>1</DEPTNO>
  <ENAME>joe</ENAME>
  <JOB>cook</JOB>
  <HIREDATE>29-MAY-05</HIREDATE>
  <SAL>33000</SAL>
 </ROW>


3 rows selected.

SQL> 
SQL> 
SQL> clear columns
SQL> 
SQL> col ename format a6
SQL> col job format a4
SQL> 
SQL> select dept.*
  2         ,to_number(extractvalue(value(c1),'/ROW/EMPNO')) empno
  3         ,to_number(extractvalue(value(c1),'/ROW/DEPTNO')) emp_deptno
  4         ,extractvalue(value(c1),'/ROW/ENAME') ename
  5         ,extractvalue(value(c1),'/ROW/JOB') job
  6         ,to_date(extractvalue(value(c1),'/ROW/HIREDATE')) hiredate
  7         ,to_number(extractvalue(value(c1),'/ROW/SAL')) sal
  8  from dept
  9       ,table(xmlsequence(ftemp1(dept.deptno))) c1
 10  /

    DEPTNO DNAME      LOC             EMPNO EMP_DEPTNO ENAME  JOB  HIREDATE         SAL
---------- ---------- ---------- ---------- ---------- ------ ---- --------- ----------
         1 dept1      loc1               11          1 john   cook 29-MAY-05      31000
         1 dept1      loc1               13          1 jim    cook 29-MAY-05      32000
         1 dept1      loc1               14          1 joe    cook 29-MAY-05      33000
         2 dept2      loc2               21          2 sue    cook 29-MAY-05      31000
         2 dept2      loc2               23          2 sally  cook 29-MAY-05      32000
         2 dept2      loc2               24          2 sarah  cook 29-MAY-05      33000

6 rows selected.

SQL> 
SQL> spool off
 

thanks for the answer. Sometimes I guess it just "sorry you can't do that yet".

See you. Kevin

Appears to be purely positional...

SQL>  select cursor(select dummy from dual) from dual;

CURSOR(SELECTDUMMYFR
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

D
-
X

SQL> ed
Wrote file afiedt.buf

  1  select dummy
  2       , cursor(select dummy from dual)
  3       , cursor(select dummy from dual)
  4* from dual
SQL> /

D CURSOR(SELECTDUMMYFR CURSOR(SELECTDUMMYFR
- -------------------- --------------------
X CURSOR STATEMENT : 2 CURSOR STATEMENT : 3

CURSOR STATEMENT : 2

D
-
X


CURSOR STATEMENT : 3

D
-
X
 

Tom,
I tried running the following query and got a ora-00932 inconsistent datatype expected NUMBER got CURSER error . Is there a parameter setting that I am missing, currently running on 9ir2

select object_name, cursor(select * from dual) from
all_objects where rownum <= 100

Tom,
full version of the database is 9.2.0.2.0.

The problem only happens in TOAD v7.4.0.3
I tried it using sqlplus and apart from exceeding the max number of open cursors when using 100 as the filter it worked fine.
Nothing to cut and paste apart from the message in a pop up error window.

I have experienced a similar problem to the one reported by Vipin in August 2003, and by others later on in the post. I have the problem in 10.1.0.4 though Oracle support suggest it's not in other (current) versions (i.e. cannot reproduce). ANYWAY I have found a simple-ish workround which involves closing the nested cursor. These examples are PL/SQL, but I think the same could be applied in Java if you have a version of Oracle that causes the problem? Note that you need to set the rownum value to whatever would break your own system. In my case max cursors is set to 300, so a value of 300 forces the error. Note also that if you run the SQL statement in the example on its own in SQL*Plus you should also get the error - this workround is just for procedural code.

Hope this helps.
===========================
CREATE OR REPLACE FUNCTION fix_example (p_out OUT sys_refcursor)
RETURN NUMBER
IS
BEGIN
OPEN p_out FOR
SELECT object_name, CURSOR(SELECT 'x' FROM DUAL)
FROM all_objects
WHERE ROWNUM < 300;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
RAISE;
END fix_example;
/

PROMPT this example should not cause the error 1001

DECLARE
p_object_name all_objects.object_name%TYPE;
p_outer_cursor SYS_REFCURSOR;
p_nested_cursor SYS_REFCURSOR;
p_dual VARCHAR2(1);

p_result NUMBER := 0;

BEGIN
p_result := fix_example(p_outer_cursor);
LOOP
FETCH p_outer_cursor
INTO p_object_name, p_nested_cursor;
EXIT WHEN p_outer_cursor%NOTFOUND;
LOOP
FETCH p_nested_cursor INTO p_dual;
EXIT WHEN p_nested_cursor%NOTFOUND;
END LOOP;
CLOSE p_nested_cursor; -- CLOSE NESTED CURSOR
END LOOP;
CLOSE p_outer_cursor;
END;
/

Prompt This example WILL cause the error 1001 to occur

DECLARE
p_object_name all_objects.object_name%TYPE;
p_outer_cursor SYS_REFCURSOR;
p_nested_cursor SYS_REFCURSOR;
p_dual VARCHAR2(1);

p_result NUMBER := 0;

BEGIN
p_result := fix_example(p_outer_cursor);
LOOP
FETCH p_outer_cursor
INTO p_object_name, p_nested_cursor;
EXIT WHEN p_outer_cursor%NOTFOUND;
LOOP
FETCH p_nested_cursor INTO p_dual;
EXIT WHEN p_nested_cursor%NOTFOUND;
END LOOP;
-- CLOSE p_nested_cursor; REMOVED TO PROVE THE PROBLEM
END LOOP;
CLOSE p_outer_cursor;
END;
/

Yes I agree entirely. But what you say does not get around the fact I'm only having to write this because this statement (below) causes an Ora-1001 Max Open Cursors exceeded to occur in **SOME** versions of Oracle when you run it in SQL*Plus. And in **SOME** versions of Oracle, therefore, you don't have to make that close call at all - in fact the close call may even fail as Oracle closes the nested cursor itself, but I can't test that 'cos I have the buggy version that I have to write the workaround for.

SELECT object_name
, (SELECT 'X' FROM DUAL)
FROM all_objects;

Just thinking : probably that both points are right. If you're fetching in PL/SQL then you have to close the nested cursor (whether there's a bug or not), but the 'implicit cursor' in SQL*Plus ought to be closing too. I think that makes sense.

In case anyone else is struggling apart from us! Oracle have confirmed there is a bug in the 9.2 oci which means that if you use the nested cursor construct and the outer cursor retrieves open_cursors -1 rows, then you will have an Ora-1000 max open cursors exceeded error. If you have the problem, this statement will cause it to happen:
SELECT object_name, CURSOR(SELECT 'x' FROM DUAL)
FROM all_objects
WHERE ROWNUM < a_number_greater_than_open_cursors;

I think the answer is to upgrade out of that version of the OCI. It's an unpublished bug, so you won't dig it up on metalink.

Hi Tom,

I used the object type method of getting more two and three columns out from a scalar query, but looking at the explain plan below I do not see any performance gain in that, since Oracle is doing multiple fetches, one for every column.

Is this expected bahviour?

With two columns -
explain plan for
select t.identifier,t.ct.a,t.ct.b from (
select
identifier,
(select tst(c.code,c.meaning)
from ctitle c
where c.identifier = p.ctitle_identifier) ct from persons p ) t where identifier = 1
/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| CODED_TITLES | 1 | 51 | 2 |
| 2 | INDEX UNIQUE SCAN | CTITLE_PK | 1 | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CODED_TITLES | 1 | 51 | 2 |
| 4 | INDEX UNIQUE SCAN | CTITLE_PK | 1 | | |
| 5 | TABLE ACCESS BY INDEX ROWID| PERSONS | 1 | 8 | 2 |
| 6 | INDEX UNIQUE SCAN | PERSON_PK | 1 | | 1 |
-----------------------------------------------------------------------------

With three columns -
explain plan for
select t.identifier,t.ct.a,t.ct.b,t.ct.c from (
select
identifier,
(select tst(c.code,c.meaning,c.notes)
from ctitle c
where c.identifier = p.ctitle_identifier) ct from persons p ) t where identifier = 1
/

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| CODED_TITLES | 1 | 60 | 2 |
| 2 | INDEX UNIQUE SCAN | CTITLE_PK | 1 | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CODED_TITLES | 1 | 60 | 2 |
| 4 | INDEX UNIQUE SCAN | CTITLE_PK | 1 | | |
| 5 | TABLE ACCESS BY INDEX ROWID| CODED_TITLES | 1 | 60 | 2 |
| 6 | INDEX UNIQUE SCAN | CTITLE_PK | 1 | | |
| 7 | TABLE ACCESS BY INDEX ROWID| PERSONS | 1 | 8 | 2 |
| 8 | INDEX UNIQUE SCAN | PERSON_PK | 1 | | 1 |
-----------------------------------------------------------------------------

Many Thanks!

use tkprof -


it shows:

select t.identifier,t.ct.a,t.ct.b,t.ct.c
from
 ( select identifier, (select tst(c.code,c.meaning,c.notes) from ctitle c
  where c.identifier = p.ctitle_identifier) ct from persons p ) t where
  identifier = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID CTITLE (cr=2 pr=0 pw=0 time=18 us)
      1   INDEX UNIQUE SCAN SYS_C009114 (cr=1 pr=0 pw=0 time=7 us)(object id 60451)
      1  TABLE ACCESS BY INDEX ROWID PERSONS (cr=2 pr=0 pw=0 time=38 us)
      1   INDEX UNIQUE SCAN SYS_C009113 (cr=1 pr=0 pw=0 time=16 us)(object id 60449)


from:

ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2> drop table persons;
ops$tkyte%ORA10GR2> drop table ctitle;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table persons ( identifier number primary key, ctitle_identifier number );
ops$tkyte%ORA10GR2> create table ctitle ( identifier number primary key, code number, meaning number, notes number );
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type tst as object ( a number, b number, c number )
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into persons values ( 1, 1 );
insert into persons values ( 1, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C009113) violated


ops$tkyte%ORA10GR2> insert into ctitle values ( 1, 1, 1, 1 );
insert into ctitle values ( 1, 1, 1, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C009114) violated


ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select t.identifier,t.ct.a,t.ct.b,t.ct.c from ( select identifier, (select tst(c.code,c.meaning,c.notes) from ctitle c where c.identifier = p.ctitle_identifier) ct from persons p ) t where identifier = 1
  2  /

IDENTIFIER       CT.A       CT.B       CT.C
---------- ---------- ---------- ----------
         1          1          1          1


Execution Plan
----------------------------------------------------------
Plan hash value: 3774624671

------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    26 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CTITLE      |     1 |    52 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C009114 |     1 |       |
|   3 |  TABLE ACCESS BY INDEX ROWID| CTITLE      |     1 |    52 |
|*  4 |   INDEX UNIQUE SCAN         | SYS_C009114 |     1 |       |
|   5 |  TABLE ACCESS BY INDEX ROWID| CTITLE      |     1 |    52 |
|*  6 |   INDEX UNIQUE SCAN         | SYS_C009114 |     1 |       |
|   7 |  TABLE ACCESS BY INDEX ROWID| PERSONS     |     1 |    26 |
|*  8 |   INDEX UNIQUE SCAN         | SYS_C009113 |     1 |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."IDENTIFIER"=:B1)
   4 - access("C"."IDENTIFIER"=:B1)
   6 - access("C"."IDENTIFIER"=:B1)
   8 - access("IDENTIFIER"=1)

 

Thank you very much !!
Very useful as always :-)