[Oracle 8.1.7 + Cursors] van een cursor naar ref cursor

[Wezzie]

Ik zit met het volgende probleem. Om bepaalde data uit de db te halen moet ik gebruik maken van twee cursors. Deze gaan door de datasets heen en halen de goede dat op. Dit gaat ook goed, maar ik wil gebruik maken van een REF CURSOR in plaats van een normale. Dit omdat ik de results door wil geven naar het bovenliggende programma. Ik krijg alleen de cursor niet een in REF CURSOR. Iemand enig idee hoe dit gedaan kan worden.

Hieronder heb ik het volledige script staan:

code:

SET SERVEROUTPUT ON

DECLARE 
 type MyRefCur is REF CURSOR;
 nCountryId number;
 nDepartmentId number;
 CheckCount number; 
 nPageId number; 
 MessageCur MyRefCur;
 CURSOR C1 IS
     SELECT DISTINCT COUNTRYDEPARTMENTCATEGORY.DEPARTMENTID 
     FROM   COUNTRY , COUNTRYDEPARTMENTCATEGORY 
     WHERE  ((COUNTRY.COUNTRYID = COUNTRYDEPARTMENTCATEGORY.COUNTRYID) AND
         (COUNTRY.COUNTRYID = nCountryId));
 /* */
 CURSOR C2 IS  
   SELECT Message.*
   FROM   MESSAGE , COUNTRYDEPARTMENTPAGE 
   WHERE  MESSAGE.PAGEID = COUNTRYDEPARTMENTPAGE.PAGEID 
   AND    COUNTRYDEPARTMENTPAGE.DEPARTMENTID = nDepartmentId
   AND    MESSAGE.MESSAGEID NOT IN (SELECT REACTION.REACTIONID FROM REACTION)
   AND    ROWNUM <= 1;  
BEGIN
    nPageId := 1;
    /*SELECT IF A PAGEID IS FOR A COUNTRY OR FOR A DEPARTMENT
     * 0 = FOR A DEPARMENT. 1 = FOR A COUNTRY*/ 
    SELECT COUNT(*) INTO CheckCount
    FROM PAGE , COUNTRY 
    WHERE PAGE.PAGEID = COUNTRY.PAGEID
    AND PAGE.PAGEID = nPageId ;
    
    /*IF CheckCount = 0 THEN THE PAGE IS A DEPARTMENTPAGE*/     
    IF (CheckCount = 0) THEN
        OPEN MessageCur FOR
            /*CODE FOR DEPARMENT MESSAGES, SELECTS THE LAST 6 MESSAGES FOR A SPECIFIC DEPARTMENT*/
            SELECT * FROM           
                (SELECT     MESSAGE.MESSAGEID, MESSAGE.MESSAGETITLE, MESSAGE.DESCRIPTION,
                            MESSAGE.DATE_TIME
                FROM        MESSAGE
                WHERE       (MESSAGE.PAGEID = nPageId)
                AND         (MESSAGE.MESSAGEID NOT IN (SELECT REACTION.REACTIONID FROM REACTION))
                ORDER BY    DATE_TIME)
            WHERE ROWNUM <= 6
            ORDER BY DATE_TIME DESC;         
    ELSE
        /*CODE FOR COUNTRY MESSAGES*/   
        SELECT  COUNTRY.COUNTRYID INTO nCountryId  
        FROM  COUNTRY , PAGE 
        WHERE  PAGE.PAGEID = COUNTRY.PAGEID AND
        PAGE.PAGEID = 1;  
        
        /*LOOP THROUGH THE DEPARTMENTS FOR A SPECIFIC COUNTRY*/
        FOR TEST IN C1 LOOP
            nDepartmentId := TEST.DEPARTMENTID;
            dbms_output.put_line(TEST.DEPARTMENTID);
            /*GET THE MESSAGE FOR A DEPARTMENT*/
            OPEN C2;
                /*LOAD IT INTO A REF CURSOR */
                OPEN MESSAGECUR FOR := SELECT C2.* FROM C2;
            CLOSE C2;           
        END LOOP;     
    END IF;     
END;

Bij het laatste stuk gaat het mis kijk hier onder

code:

/*LOOP THROUGH THE DEPARTMENTS FOR A SPECIFIC COUNTRY*/
        FOR TEST IN C1 LOOP
            nDepartmentId := TEST.DEPARTMENTID;
            dbms_output.put_line(TEST.DEPARTMENTID);
            /*GET THE MESSAGE FOR A DEPARTMENT*/
            OPEN C2;
                /*LOAD IT INTO A REF CURSOR */
                OPEN MESSAGECUR FOR := SELECT C2.* FROM C2;
            CLOSE C2;           
        END LOOP;

[justmental]

Die := hoort daar niet bij het openen van die cursor.

[Wezzie]

Nee, daar was ik al achter maar hoe doe je het dan wel? Gewoon met een = werkt het ook niet. Ik moet als het ware een pointer aanmaken van een bestaande cursor en die toewijzen aan "messagecur"

[justmental]

Ah, je wilt de ene ref cursor binnen de andere gebruiken.
Ziehier

[ Voor 8% gewijzigd door justmental op 14-05-2003 11:03 ]

[Wezzie]

Nee, ik heb de cursor "C2" deze bevat de goede data. ik wil nu alleen een pointer aan maken naar de "C2" cursor. In de C2 cursor gebruik ik de C1 cursor al.

[Swa-baldie]

Je kunt ook parameters meegeven aan een cursor.

gaat als volgt

cursor c_1 (p_1 in number)
is
select *
from x
where a = p1;

Bij de aanroepen naar je cursor zou je dan dit doen

open c_1( parameter uit andere cursor);

Of is dat niet wat je bedoel?

[Wezzie]

Nee, dat bedoel ik niet. Ik heb de cursor al gevuld en moet em alleen in een reference cursor proppen om dan vervolgens de reference cursor terug te geven in een stored procedure. De code heb ik nog even zonder stored procedure code omdat het dan wat makkelijker te debuggen is.

[justmental]

Kun je niet gewoon messagecur := c2; doen?
Een ref cursor is in principe een pointer.

[Wezzie]

Nee dat werkt ook niet. Juist omdat c2 een normale cursor is en geen pointer. Dan werkt het niet. Ik heb de oplossing al wel. Deze zal ik ook even posten voor het archief... Het is niet de mooiste oplossing maar het werkt wel. Ik ga alles gewoon in een temp table knallen en haal het erna weer eruit.

code:

procedure sp_getMessages3
    (nPageId in Number, MessageCur OUT MyRefCur)
    is
    nCountryId number;
    nDepartmentId number;
    CheckCount number;  
    CURSOR C1 IS
        SELECT DISTINCT COUNTRYDEPARTMENTCATEGORY.DEPARTMENTID 
        FROM   COUNTRY , COUNTRYDEPARTMENTCATEGORY 
        WHERE  ((COUNTRY.COUNTRYID = COUNTRYDEPARTMENTCATEGORY.COUNTRYID) AND
            (COUNTRY.COUNTRYID = nCountryId));
    /* */
    CURSOR C2 IS  
    SELECT Message.*
    FROM   MESSAGE , COUNTRYDEPARTMENTPAGE 
    WHERE  MESSAGE.PAGEID = COUNTRYDEPARTMENTPAGE.PAGEID 
    AND    COUNTRYDEPARTMENTPAGE.DEPARTMENTID = nDepartmentId
    AND    MESSAGE.MESSAGEID NOT IN (SELECT REACTION.REACTIONID FROM REACTION)
    AND   ROWNUM <= 1;  
    BEGIN
        nPageId := 1;
        /*SELECT IF A PAGEID IS FOR A COUNTRY OR FOR A DEPARTMENT
        * 0 = FOR A DEPARMENT. 1 = FOR A COUNTRY*/ 
        SELECT COUNT(*) INTO CheckCount
        FROM PAGE , COUNTRY 
        WHERE PAGE.PAGEID = COUNTRY.PAGEID
        AND PAGE.PAGEID = nPageId ;
        
        /*IF CheckCount = 0 THEN THE PAGE IS A DEPARTMENTPAGE*/     
        IF (CheckCount = 0) THEN
            OPEN MessageCur FOR
                /*CODE FOR DEPARMENT MESSAGES, SELECTS THE LAST 6 MESSAGES FOR A SPECIFIC DEPARTMENT*/
                SELECT * FROM           
                    (SELECT     MESSAGE.MESSAGEID, MESSAGE.MESSAGETITLE, MESSAGE.DESCRIPTION,
                                MESSAGE.DATE_TIME
                    FROM        MESSAGE
                    WHERE       (MESSAGE.PAGEID = nPageId)
                    AND         (MESSAGE.MESSAGEID NOT IN (SELECT REACTION.REACTIONID FROM REACTION))
                    ORDER BY    DATE_TIME)
                WHERE ROWNUM <= 6
                ORDER BY DATE_TIME DESC;         
        ELSE
            /*CODE FOR COUNTRY MESSAGES*/   
            SELECT  COUNTRY.COUNTRYID INTO nCountryId  
            FROM  COUNTRY , PAGE 
            WHERE  PAGE.PAGEID = COUNTRY.PAGEID AND
            PAGE.PAGEID = 1;  
            
            /*LOOP THROUGH THE DEPARTMENTS FOR A SPECIFIC COUNTRY*/
            FOR DEP IN C1 LOOP
                nDepartmentId := DEP.DEPARTMENTID;
                /*GET THE MESSAGE FOR A DEPARTMENT*/
                DELETE FROM TEMPMESSAGE;
                FOR MES IN C2 LOOP
                    /*LOAD IT INTO A REF CURSOR */
                    /*How do i do this */               
                    INSERT INTO TEMPMESSAGE(MESSAGEID, MESSAGETITLE, DESCRIPTION, THREADID, EMPLOYEEID, PAGEID, DATE_TIME)
                    VALUES(MES.MESSAGEID, MES.MESSAGETITLE, MES.DESCRIPTION, MES.THREADID, MES.EMPLOYEEID, MES.PAGEID, MES.DATE_TIME);
                END LOOP;       
            END LOOP;
        OPEN MessageCur FOR
            SELECT * FROM TEMPMESSAGE;            
        END IF;
        COMMIT;     
END sp_getMessages3;

Iedereen bedankt voor het commentaar!