RE: Help - How to declare a cursor inside a FOR ... LOOP ?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 03 Apr 2001 13:28:55 -0700
Message-ID: <F001.002E0545.20010403130704@fatcity.com>

I must say that I am confused by the previous answers. It looks to me that the COLUMN NAMES are changing for the SQL statement. If so, you need to use dynamic SQL. Here's a short example below. You can read all about the dbms_sql procedure for dynamic SQL in the excellent Feuerstein, Dye and Beresniewicz book "Oracle Built-in Packages" (O'Reilly).

set serveroutput on
declare
   c_dynsql pls_integer ;
   ignore pls_integer ;
   num_rows pls_integer ;
   v_emp_no emp.emp_no%type ;
   v_emp_name emp.emp_name%type ;
   v_emp_name_len constant pls_integer := 30 ;
   col1_name varchar2 (30) ;
   col2_name varchar2 (30) ;

begin
   col1_name := 'emp_no' ;
   col2_name := 'emp_name' ;
   c_dynsql := dbms_sql.open_cursor ;
   dbms_sql.parse (c_dynsql,

                   'select ' || col1_name || ', '
                      || col2_name
                      || ' from emp where rownum < 3',
                   dbms_sql.native) ;

   dbms_sql.define_column (c_dynsql, 1, v_emp_no) ;
   dbms_sql.define_column (c_dynsql, 2, v_emp_name, v_emp_name_len) ;
   ignore := dbms_sql.execute (c_dynsql) ;
   num_rows := 1 ;
   while num_rows > 0
   loop

     num_rows := dbms_sql.fetch_rows (c_dynsql) ;
     if num_rows > 0
     then
       dbms_sql.column_value (c_dynsql, 1, v_emp_no) ;
       dbms_sql.column_value (c_dynsql, 2, v_emp_name) ;
       dbms_output.put_line ('Emp#: ' || to_char (v_emp_no)
                             || '  Name: ' || v_emp_name) ;
     end if ;

   end loop ;
   dbms_sql.close_cursor (c_dynsql) ;

exception
   when others then

     if dbms_sql.is_open (c_dynsql)
     then
        dbms_sql.close_cursor (c_dynsql) ;
     end if ;
     raise ;

end ;
/

>-----Original Message-----
>From: Wendy Y [mailto:lannyue_at_yahoo.com]
>
>Hey, Guys:
>I need to decalre a cursor inside a FOR .. LOOP,
> because the variables in SELECT statement
>for the CURSOR are coming from FOR .. LOOP.
> How could I do this?
>DECLARE
>  V_AKTIV_NR    aktiv.AKTIV_NR%TYPE;
>  V_PROBLEM    aktiv.PROBLEM%TYPE;
>  V_ENDDAT   aktiv.ENDDAT%TYPE;
>  V_ENDUHR   aktiv.ENDUHR%TYPE;
>  V_AUSSAGEW   aktiv.AUSSAGEW%TYPE;
>  V_LANGTEXT   aktiv.LANGTEXT%TYPE;
>  V_LONGTEXT    VARCHAR2(20000);
>  V_LONGTEXT_CUR  VARCHAR2(13000);
>  V_LONGTEXT_TRIM VARCHAR2(2000);
>  p_PROBLEM    PROBLEM.PROBLEM%TYPE;  
>############# This part need to be inside FOR.. LOOP, otherwise, I wouldn't get anything.
>    CURSOR AKLangTextCur IS 
> SELECT  TO_CHAR(V_ENDDAT, 'YYYYMMDD')||' '||
>   V_ENDUHR||' '||
>  rtrim(V_AUSSAGEW)||' '||
>  rtrim(V_LANGTEXT)   thisText
>        FROM AKTIV
>        WHERE V_PROBLEM = p_PROBLEM
>        ORDER BY Aktiv_NR; 
>    AKLangTextRec   AKLangTextCur%ROWTYPE;     
>################################### Above
>BEGIN
>  FOR v_LoopIndex IN 1..pkgFreeText.v_NumEntries LOOP
>    V_AKTIV_NR := pkgFreeText.V_AKTIV_NR_P(v_LoopIndex);
>    V_PROBLEM  := pkgFreeText.V_PROBLEM_P(v_LoopIndex);
>    V_ENDDAT   := pkgFreeText.V_ENDDAT_P(v_LoopIndex);
>    V_ENDUHR   := pkgFreeText.V_ENDUHR_P(v_LoopIndex);
>    V_AUSSAGEW := pkgFreeText.V_AUSSAGEW_P(v_LoopIndex);
>    V_LANGTEXT := pkgFreeText.V_LANGTEXT_P(v_LoopIndex);
>  SELECT PROBLEM INTO p_PROBLEM FROM PROBLEM;
>########## CURSOR DECLARE should be HERE ##########
>    BEGIN
>     OPEN AKLangTextCur;
>          LOOP
>            FETCH AKLangTextCur into AKLangTextRec;
>             EXIT WHEN AKLangTextCur%NOTFOUND;
>            V_LONGTEXT_CUR := V_LONGTEXT_CUR ||AKLangTextRec.thisText;                 
>         END LOOP;
>     CLOSE AKLangTextCur;
>    END;                  
>  V_LONGTEXT := V_LONGTEXT || V_LONGTEXT_CUR;   
>  END LOOP;
>   V_LONGTEXT_TRIM := RTRIM(V_LONGTEXT, 2000);

Received on Tue Apr 03 2001 - 15:28:55 CDT