Re: table operator on table of rowids

From: VC <boston103_at_hotmail.com>
Date: Fri, 30 Jan 2004 18:56:56 GMT
Message-ID: <YNxSb.190909$na.317512@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 - 12:56:56 CST