PL/SQL Dynamic Cursor Definition

From: Abbas <Abbas.Rashidi_at_alcatel.de>
Date: Thu, 27 Mar 2003 16:23:26 +0100
Message-ID: <3E83176E.C8549677@alcatel.de>

Hello everyone,

I have a big problem, following situation:

    PROCEDURE doReport2
    IS

        err_num     NUMBER;
        err_msg     VARCHAR2 (2000);
        modulname   VARCHAR2(20) := 'doReport2';
        SQLString   VARCHAR(8000);
        ColCount    NUMBER := 0;

        Type myCursor is REF CURSOR;          --<<<<<<<<<<<
        curPivot    myCursor;                 --<<<<<<<<<<<

        myRow      curPivit%curPivot          --<<<<<<  WRONG !!!        
    BEGIN
        SQLString :=

myPIVOT('SUM','PID_ITEMQTY_N','PROM_KAP_VW','KW_I','PID_PRODUCTACRONYM',1);

• OPEN curPivot FOR SQLString; ?????
• FETCH curPivot INTO myRow; ?????
• OR FOR myRow IN curPivot ?????
  .......
  .......
  

I have a function called myPivot that returns a dynamic built SELECT Statement. The SELECT Statement is stored in "SQLString".

I have declared "curPivot" and want open the cursor. "myRow" Definition is wrong! BUT how can I declare a variable that has the same ROWTYPE as my cursor?
If I open the cursor with OPEN, I have to get the Data with FETCH. FETCH requires one or more variables to put the data in them. The Column Count of my cursor can be variable so I can't use FETCH like:

FETCH curPivot INTO var1, var2 var3...

With FOR is the problem that you can't use the SQLString for the cursor! Isn't it?

I hope I could decribe my problem.

Does anybody know, how I can solve this problem?

regards
Abbas Received on Thu Mar 27 2003 - 09:23:26 CST