Hierarchical Query Cursor in PL/SQL block

From: Sean McMurray <smcmurr_at_admin.dce.utah.edu>
Date: Thu, 08 Apr 1999 14:22:04 -0600
Message-ID: <370D0FEC.44E93C98@admin.dce.utah.edu>

I can run the following query just fine in SQL*Plus:

    select Max(lvl) ord, dev_tables.t_name from

        (select level lvl, t_name
        from dev_constraints
        connect by prior t_name=c_ptable) hq,
        dev_tables

    where hq.t_name=dev_tables.t_name (+)     group by dev_tables.t_name
    order by ord desc;

However, when I try to compile the following, it doesn't work:

CREATE OR REPLACE PROCEDURE build_tables IS

    CURSOR deltab IS

        select Max(lvl) ord, dev_tables.t_name from
            (select level lvl, t_name
            from dev_constraints
            connect by prior t_name=c_ptable) hq,
            dev_tables
        where hq.t_name=dev_tables.t_name (+)
        group by dev_tables.t_name
        order by ord desc;

BEGIN
    NULL;
END; I get the following error:

    Errors for PROCEDURE BUILD_TABLES:

    LINE/COL ERROR

---

---

    3/9      PL/SQL: SQL Statement ignored
    4/13     PLS-00320: the declaration of the type of this expression
is
             incomplete or malformed

It's probably something really dumb like a misplaced comma, but I can't seem to figure it out. Anybody willing to point out my problem for me? If so, please cc to my email address, too.

Thanks. Received on Thu Apr 08 1999 - 15:22:04 CDT