Re: table operator on table of rowids

This looks a little nicer and should be more efficient:

create or replace function f1( p_deptno in number) return sys_refcursor as   l_RID myRIDTable := myRIDTable();
  l_cur sys_refcursor;
begin

• Collect RIDs for x in ( select rowid from emp where deptno=p_deptno ) loop l_RID.extend; l_RID(l_RID.count) := myRIDType( dbms_rowid.rowid_type(x.rowid), dbms_rowid.rowid_object(x.rowid), dbms_rowid.rowid_relative_fno(x.rowid), dbms_rowid.rowid_block_number(x.rowid), dbms_rowid.rowid_row_number(x.rowid) ); end loop;
• open ref cursor open l_cur for select empno, ename, job, deptno from emp where rowid in (select DBMS_ROWID.ROWID_CREATE ( rowid_type, object_number, relative_fno, block_number, row_number) from table(cast(l_RID as myRIDTable))); return l_cur; end; /

VC
"VC" <boston103_at_hotmail.com> wrote in message news:YNxSb.190909$na.317512_at_attbi_s04...
> Hello Chris,
>
>
> "Chris Bateman" <chrisbateman_at_despammed.com> wrote in message
> news:51a52448.0401300611.6096b329_at_posting.google.com...
> > Is it possible to use the table operator on a PL/SQL table containing
> > rowids?
> >
> > As far as I can tell, the table operator can only be used on
> > schema-level table types, not table types declared in PL/SQL. Schema
> > table types are not allowed to be 'table of rowid' and schema level
> > object types - of which I could create a table - are not allowed to
> > have attributes of type rowid.
> >
> > What I'm trying to achieve is something like the following without
> > having to use dynamic SQL to create the in list.
> >
> > OPEN my_ref_cursor FOR
> > SELECT *
> > FROM some_table
> > WHERE rowid IN (SELECT * from TABLE(CAST(plsql_table AS
> > rowid_table)));
> >
> > TIA
> > Chris
>
> You can work around the rowid limitation in the following way (ugly as it
> is):
>
> create or replace type myRIDtype as object(
> rowid_type number,
> object_number number,
> relative_fno number,
> block_number number,
> row_number number
> )
> /
>
> create or replace type myRIDTable as table of myRIDType
> /
>
> create or replace function f1( p_deptno in number) return sys_refcursor as
> l_RID myRIDTable := myRIDTable();
> l_cur sys_refcursor;
> begin
>
> -- Collect RIDs
> for x in ( select rowid from emp where deptno=p_deptno ) loop
> l_RID.extend;
> l_RID(l_RID.count) := myRIDType(
> dbms_rowid.rowid_type(x.rowid),
> dbms_rowid.rowid_object(x.rowid),
> dbms_rowid.rowid_relative_fno(x.rowid),
> dbms_rowid.rowid_block_number(x.rowid),
> dbms_rowid.rowid_row_number(x.rowid)
> );
> end loop;
>
> -- open ref cursor
> open l_cur for select * from emp where
> ( dbms_rowid.rowid_type(rowid),
> dbms_rowid.rowid_object(rowid),
> dbms_rowid.rowid_relative_fno(rowid),
> dbms_rowid.rowid_block_number(rowid),
> dbms_rowid.rowid_row_number(rowid)
> ) in (select * from table(cast(l_RID as myRIDTable)));
> return l_cur;
> end;
> /
>
> SQL> set serverout on
> SQL> var x refcursor
> SQL> exec :x:=f1(10)
>
> PL/SQL procedure successfully completed.
>
> SQL> print x
>
> EMPNO ENAME JOB DEPTNO
> ---------- -------------------- -------------------- ----------
> 7839 KING PRESIDENT 10
> 7782 CLARK MANAGER 10
> 7934 MILLER CLERK 10
>
>
> Rgds.
>
> VC
>
>
Received on Fri Jan 30 2004 - 13:16:30 CST