join on a data cursor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: join on a data cursor

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    fetch from data cursor and requery *Edit*

    I have a SP that calls another sp which returns a Cursor.

    I want to fetch from this cursor (preferably only the first column if possible), do a bulk collect and then query using the results of the bulk collect in my where clause.

    In the table definition ID is the primary key and the type is
    NUMBER(12).

    Code insert
    ---------------------------------------------------------------------
    PROCEDURE ScottsTestDORatings(DATA_CURSOR OUT Types.CursorType)
    IS
    /***
    OneDNumberArray is just a packaged Type that is Table Of Number indexed by Binary Number
    ***/
    DOIDs OneDNumberArray := OneDNumberArray();

    DOCursor Types.CursorType;
    TYPE DOID IS TABLE OF CAW_DATAOBJECTS.ID%TYPE;
    myDOID DOID;
    TYPE DONAME IS TABLE OF CAW_DATAOBJECTS.NAMEE%TYPE;
    myDONAME DONAME;
    TYPE POINTER IS TABLE OF CAW_DATAOBJECTS.POINTERE%TYPE;
    myPOINTER POINTER;
    TYPE DOSUMMARY IS TABLE OF CAW_DATAOBJECTS.SUMMARYE%TYPE;
    myDOSUMMARY DOSUMMARY;
    TYPE DATECREATED IS TABLE OF CAW_DATAOBJECTS.DATECREATED%TYPE;
    myDATECREATED DATECREATED;
    TYPE DATEMODIFIED IS TABLE OF CAW_DATAOBJECTS.DATEMODIFIED%TYPE;
    myDATEMODIFIED DATEMODIFIED;
    TYPE DOWNLOADCOUNT IS TABLE OF CAW_DATAOBJECTS.DOWNLOADCOUNT%TYPE;
    myDOWNLOADCOUNT DOWNLOADCOUNT;
    TYPE DISPLAYINLINEFLAG IS TABLE OF CAW_DATAOBJECTS.DISPLAYINLINEFLAG%TYPE;
    myDISPLAYINLINEFLAG DISPLAYINLINEFLAG;
    TYPE TOTALRECORDS IS TABLE OF NUMBER;
    myTOTALRECORDS TOTALRECORDS;
    BEGIN
    /********
    call other SP to fill the cursor, the cursor has columns
    id (number), name (varchar), pointer (varchar), summary (varchar), datecreated (date), datemodified (date), downloadcount(number), displayinlineflag (number), totalrecords(number)
    *********/

    Scott(DOCursor);

    --OPEN DOCursor;
    FETCH DoCursor BULK COLLECT INTO DOIDs, myDONAME, myPOINTER, myDOSUMMARY, myDATECREATED, myDATEMODIFIED, myDOWNLOADCOUNT, myDISPLAYINLINEFLAG, myTOTALRECORDS;

    CLOSE DOCursor;

    OPEN DATA_CURSOR FOR
    SELECT ID, NAMEE, POINTERE, DATECREATED
    FROM CAW_DATAOBJECTS
    WHERE ID IN (
    SELECT *
    FROM TABLE(CAST(DOIDS as ONEDNUMBERARRAY))
    ) ;

    END ScottsTestDORatings;


    --------------------------------------------------------------------

    if i try to bulk collect the id into DOIDs i get...
    Return types of Result Set variables or query do not match ORA-06512

    if i try to bulk collect the id into myDOID i get...
    PLS-00642: local collection types not allowed in SQL statements

    I'm just a programmer in a jam with no DB Admin or anyone else that knows anything about oracle around.
    Last edited by luciffer; 06-22-2004 at 11:56 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width