Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help - How to declare a cursor inside a FOR ... LOOP ?
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) ;
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 ;
if dbms_sql.is_open (c_dynsql) then dbms_sql.close_cursor (c_dynsql) ; end if ; raise ;
>-----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
![]() |
![]() |