ORA-907 using CAST with a table of records

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Tue, 2 Mar 2010 14:19:54 -0600 (CST)
Message-ID: <8657f7233c2a9193222cf4ff2a40df3f.squirrel_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
Received on Tue Mar 02 2010 - 14:19:54 CST

Original text of this message