FAQ
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

Search Discussions

  • Gints Plivna at Mar 2, 2010 at 10:24 pm
    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 :
    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-l
  • Gints Plivna at Mar 3, 2010 at 3:47 pm
    Procedure or package doesn't matter. The problem is that SQL engine
    cannot see types defined in procedural units. Therefore CAST MULTISET
    on package types doesn't work. On the other hand if you create similar
    object type, I don't know how to cast from object type to local
    package type and/or vice versa.

    But you can get along with a temp table:
    create table tab1 (

    ownname     varchar2(32),     -- owner
    objtype     varchar2(6),      -- 'TABLE' or 'INDEX'
    objname     varchar2(32),     -- table/index

    partname    varchar2(32),     -- partition
    subpartname varchar2(32),     -- subpartition
    confidence  number);

    and then it works:

    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);

    forall i in t_objlist.first..t_objlist.last
    insert into tab1 values t_objlist(i);
    select * bulk collect into t_objsort
    from tab1
    order by objname;
    END list_stats_test;
    /

    Not too much code, although with one temporal object :)

    It seems other people agree it is not possible, at least for example here
    http://technology.amis.nl/blog/1217/sorting-plsql-collections-the-quite-simple-way-part-two-have-the-sql-engine-do-the-heavy-lifting
    and here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5189710445984
    they say that sql object types have to be used.

    Gints Plivna
    http://www.gplivna.eu

    2010/3/3 Rich Jesse :
    Hi Gints,
    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;)
    To test your theory, I pulled the ObjectTab definition and it's underlying
    record type into the proc:
    --
    http://www.freelists.org/webpage/oracle-l
  • Rich Jesse at Mar 3, 2010 at 4:14 pm

    Procedure or package doesn't matter. The problem is that SQL engine
    cannot see types defined in procedural units. Therefore CAST MULTISET
    on package types doesn't work. On the other hand if you create similar
    object type, I don't know how to cast from object type to local
    package type and/or vice versa. [snip]
    Not too much code, although with one temporal object :)
    I try to avoid temp objects if possible (unlike SQL Server!). From your
    reply, I'm going to attempt this with a defined TYPE in the schema. Worth a
    shot anyway. :)
    Yup! I've been attempting to adapt that example to my own situation. It
    seems to be *the* web article that everyone uses when trying to sort
    collections in PL/SQL.
    and here
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5189710445984
    they say that sql object types have to be used.
    Nice! I missed that one. The reply near the bottom spells it out plainly.

    Thanks again, Gints!

    Rich

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 2, '10 at 8:19p
activeMar 3, '10 at 4:14p
posts4
users2
websiteoracle.com

2 users in discussion

Rich Jesse: 2 posts Gints Plivna: 2 posts

People

Translate

site design / logo © 2022 Grokbase