Re: ORA-907 using CAST with a table of records
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 3 Mar 2010 08:47:56 -0600 (CST)
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:
CREATE OR REPLACE PROCEDURE t_dba.list_stats_test AS
type MyObjectElem is record (
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); -- not used
type MyObjectTab is table of MyObjectElem;
t_objlist sys.dbms_stats.ObjectTab;
t_objsort MyObjectTab;
BEGIN
dbms_stats.gather_database_stats (
stattab => NULL,
statid => NULL,
options => 'LIST STALE',
objlist => t_objlist,
statown => NULL);
SELECT CAST ( MULTISET ( SELECT * FROM TABLE(t_objlist) tt ORDER BY
tt.objname ASC ) AS MyObjectTab ) INTO t_objsort FROM dual;
END list_stats_test;
/
This time it errors out with ORA-902 invalid datatype at CAST...AS
MyObjectTab. I'm thinking Oracle's not going to let me do this without
defining an object type in the schema (as opposed to declaring a record type
in the procedure).
Unless anyone knows of a way around this...
Thanks for the feedback!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts: