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:[EMAIL PROTECTED]]
>
>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);
