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,
BEGIN
dbms_stats.gather_database_stats (
12/103 PL/SQL: ORA-00907: missing right parenthesis
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-lReceived on Tue Mar 02 2010 - 14:19:54 CST