Oracle Conditional CONNECT BY

From: Leo J. Hart IV <leo.hart_at_nospam.fmr.com>
Date: Wed, 13 Mar 2002 09:11:45 -0500
Message-ID: <d3Jj8.105$M3.299@news-srv1.fmr.com>

Hi,

I have a website building application that is based on 1 table, cfit_element. Each row in cfit_element represents 1 link on the website. Originally the requirements were to display the links in hierarchical format for a given website (table of contents). This was easy enough to do as I just needed a simple connect-by clause. Before I go any further, let me give you the original table structure:

CREATE TABLE cfit_element

    (id                            NUMBER NOT NULL,
    name                           VARCHAR2(200) NOT NULL,
    order_number                   NUMBER NOT NULL,
    parent_id                      NUMBER,
    is_parent                      VARCHAR2(1) NOT NULL,
    website_id                     NUMBER NOT NULL,
    link_url                       VARCHAR2(1000))

This worked well, except that we started having situations where 1 website would link to another. The client wanted the parent website's site map and search engine (but not the table of contents) to display the hierarchy all the way down. If there was a link to another website, that other website link's children should also be displayed. So I figured I needed to break out the URLs where there was one website referencing the other to something that could be compared upon:

CREATE TABLE cfit_element

    (id                            NUMBER NOT NULL,
    name                           VARCHAR2(200) NOT NULL,
    order_number                   NUMBER NOT NULL,
    parent_id                      NUMBER,
    is_parent                      VARCHAR2(1) NOT NULL,
    website_id                     NUMBER NOT NULL,
    link_website_id                NUMBER, --if link to another website
    link_element_id                NUMBER, --if link to another element in
website
    link_url                       VARCHAR2(1000)) --if standard link

But now I have a problem. A standard connect-by statement won't work as the CONNECT BY PRIOR clause would vary depending on the data returned for each row. I tried using a case statement and a bunch of DECODE statements, but they aren't really suited for this sort of thing, as far as I can tell.

I did try writing a recursive stored procedure, but keep getting "ORA-01000: maximum open cursors exceeded ORA-06512" errors within the procedure. I'll include that code below:

/***************************************************************************


********************************/

CREATE OR REPLACE
TYPE sitemaptabletype AS TABLE OF SITEMAPRECORDTYPE /

/***************************************************************************


********************************/

CREATE OR REPLACE
TYPE sitemaprecordtype AS OBJECT(

    depth NUMBER,
    id NUMBER,
    parent_id NUMBER,
    name VARCHAR2(500),
    is_parent VARCHAR2(1),

    link_website_id NUMBER,
    link_element_id NUMBER,
    link_url VARCHAR2(4000)

)
/

/***************************************************************************


********************************/

FUNCTION GET_SITE_MAP_H_TRAVERSE
( websiteID IN NUMBER DEFAULT NULL,

        parentID IN NUMBER DEFAULT NULL,
        currentDepth IN NUMBER DEFAULT NULL)
    RETURN SITEMAPTABLETYPE IS
        thisSiteMapTable SITEMAPTABLETYPE := SITEMAPTABLETYPE();
        tempSiteMapTable SITEMAPTABLETYPE := SITEMAPTABLETYPE();

    BEGIN
        FOR elementBranchRecord IN (
            SELECT currentDepth as depth, id, parent_id, name, is_parent,
website_id,
                link_website_id, link_element_id, link_url
            FROM cfit_element
            WHERE NVL(parent_id, -1) = NVL(parentID, -1)
            AND website_id = websiteID
            ORDER BY order_number ASC) LOOP

            thisSiteMapTable.EXTEND;
            thisSiteMapTable(thisSiteMapTable.COUNT) :=
                SITEMAPRECORDTYPE(
                    elementBranchRecord.depth,
                    elementBranchRecord.id,
                    elementBranchRecord.parent_id,
                    elementBranchRecord.name,
                    elementBranchRecord.is_parent,
                    elementBranchRecord.link_website_id,
                    elementBranchRecord.link_element_id,
                    elementBranchRecord.link_url);
            IF elementBranchRecord.link_website_id IS NOT NULL THEN
                tempSiteMapTable :=

GET_SITE_MAP_H_TRAVERSE(elementBranchRecord.link_website_id, elementBranchRecord.link_element_id, currentDepth + 1);

                FOR i IN 1..tempSiteMapTable.COUNT LOOP
                    thisSiteMapTable(thisSiteMapTable.COUNT) :=
tempSiteMapTable(i);
                END LOOP;
            ELSIF elementBranchRecord.is_parent = 'Y' THEN
                tempSiteMapTable :=
                    GET_SITE_MAP_H_TRAVERSE(elementBranchRecord.website_id,
elementBranchRecord.parent_id, currentDepth + 1);
                FOR i IN 1..tempSiteMapTable.COUNT LOOP
                    thisSiteMapTable(thisSiteMapTable.COUNT) :=
tempSiteMapTable(i);
                END LOOP;
            END IF;
        END LOOP;
        RETURN thisSiteMapTable;

END;

/***************************************************************************


********************************/

Does anyone have any suggestions as to how I can create the sitemap of this website?

Thanks!
Leo Received on Wed Mar 13 2002 - 08:11:45 CST

Oracle Conditional CONNECT BY - Possible?