Re: ORA-907 using CAST with a table of records

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Wed, 3 Mar 2010 17:47:29 +0200
Message-ID: <6e49b6d01003030747t4768d22u63ecedc0e50a0583_at_mail.gmail.com>

Procedure or package doesn't matter. The problem is that SQL engine cannot see types defined in procedural units. Therefore CAST MULTISET on package types doesn't work. On the other hand if you create similar object type, I don't know how to cast from object type to local package type and/or vice versa.

But you can get along with a temp table: create table tab1 (

  ownname     varchar2(32),     -- owner
  objtype     varchar2(6),      -- 'TABLE' or 'INDEX'
  objname     varchar2(32),     -- table/index

  partname    varchar2(32),     -- partition   subpartname varchar2(32),     -- subpartition   confidence  number);

and then it works:

CREATE OR REPLACE PROCEDURE list_stats_test AS

       t_objlist               sys.dbms_stats.ObjectTab;
       t_objsort               sys.dbms_stats.ObjectTab;

BEGIN
dbms_stats.gather_database_stats (

       stattab => NULL,
       statid => NULL,
       options => 'LIST STALE',
       objlist => t_objlist,
       statown => NULL);

forall i in t_objlist.first..t_objlist.last   insert into tab1 values t_objlist(i);
select * bulk collect into t_objsort
  from tab1
  order by objname;
END list_stats_test;
/

Not too much code, although with one temporal object :)

It seems other people agree it is not possible, at least for example here http://technology.amis.nl/blog/1217/sorting-plsql-collections-the-quite-simple-way-part-two-have-the-sql-engine-do-the-heavy-lifting and here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5189710445984 they say that sql object types have to be used.

Gints Plivna
http://www.gplivna.eu

2010/3/3 Rich Jesse <rjoralist_at_society.servebeer.com>:
> Hi Gints,
>
>> I'm 99% sure that one cannot cast (multiset) using type defined in
>> package. It has to be OBJECT TYPE defined in database. That's the
>> reason why Oracle wants ")" after the second keyword i.e. sys.whatever
>> is allowed if it is object type, but sys.package.type is too much for
>> an object type.
>>
>> Cannot find on the spot absolutely precise definition in the docs
>> though, therefore 1% for doubts ;)
>
> To test your theory, I pulled the ObjectTab definition and it's underlying
> record type into the proc:

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 03 2010 - 09:47:29 CST