Re: ORA-907 using CAST with a table of records
Date: Wed, 3 Mar 2010 00:24:18 +0200
Message-ID: <6e49b6d01003021424i50aa07bam9e259a4798e7006f_at_mail.gmail.com>
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 ;)
Gints Plivna
http://www.gplivna.eu
2010/3/2 Rich Jesse <rjoralist_at_society.servebeer.com>:
> Howdy all,
>
> In 10.1.0.5 and 10.2.0.3, I'm trying to compile this seemingly simple
> procedure (needing to grant appropriate privs on sys.dbms_stats before
> compiling!):
>
> 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);
>
> SELECT CAST ( MULTISET ( SELECT * FROM TABLE(t_objlist) tt ORDER BY
> tt.objname ASC ) AS sys.dbms_stats.ObjectTab ) INTO t_objsort FROM dual;
> END list_stats_test;
> /
>
> ...but it errors out with:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 12/1 PL/SQL: SQL Statement ignored
> 12/103 PL/SQL: ORA-00907: missing right parenthesis
>
> I'm trying to sort the list of stale tables, but I'll be darned if I can
> figure out what I'm doing wrong -- it certainly can't be missing parens, can
> it?
>
> MOS/Google comes up with garbage and no hits of what I've been able to sift
> through.
>
> Thoughts anyone?
>
> Rich
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 02 2010 - 16:24:18 CST