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