Re: ORA-907 using CAST with a table of records
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
- Date: Wed, 3 Mar 2010 17:47:29 +0200
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@xxxxxxxxxxxxxxxxxxxxx>:
> 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:
--
//www.freelists.org/webpage/oracle-l
Other related posts:
