ORA-907 using CAST with a table of records
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 2 Mar 2010 14:19:54 -0600 (CST)
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
Other related posts:
