No clean way of doing this type of dynamic SQL. Basically you need to
use DBMS_SQL package. If you parse the cursor with DBMS_SQL, you can
find out its fields and data types.
Abbas <Abbas.Rashidi_at_alcatel.de> wrote in message news:<3E83176E.C8549677_at_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 - 15:55:48 CST
