PROCEDURE Populate_temp_data IS
CURSOR c_prop_dept IS (SELECT prddes_prd_id,peu_id,[b][i](SELECT text_line FROM  short_specs WHERE prd_id = prddes_prd_id
 AND spec_name = 'PROCESS NO.')||
(SELECT text_line FROM  short_specs
WHERE prd_id = prddes_prd_id
 AND spec_name = 'DEPARTMENT NO.')[/i][/b]
                         ,NVL( DECODE( :p_description, 'P/L', 
                          fts.fn_get_item_desc(prddes_prd_id, :p_iconv_id),--Added by MESA-IN "p_conv_id" for Task B5.088 on 15-JUL-2009
                          'CAT',
                          decode(substr(spares_support.get_dept_desc(prddes_prd_id, :p_conv_id1),1,2),
                                 '##', decode(SUBSTR(spares_support.get_dept_desc(prddes_prd_id, 'EN'),1,2),
                                              '**',spares_support.get_dept_desc(prddes_prd_id, :p_conv_id1),
                                              '* '||spares_support.get_dept_desc(prddes_prd_id, 'EN')),
                                spares_support.get_dept_desc(prddes_prd_id, :p_conv_id1)),--Added by MESA-IN for Task B5.088 on 15-JUL-2009
                          fts.fn_get_item_desc(prddes_prd_id, :p_iconv_id)--Added by MESA-IN for Task B5.088 on 15-JUL-2009
                        ),
                        fts.fn_get_item_desc(prddes_prd_id, :p_iconv_id)--Added by MESA-IN for Task B5.088 on 15-JUL-2009
                        )   
      
       FROM proposal_elements 
      WHERE --rev_no = :p_rev_no
            expiration_date IS NULL
--Included Expiration_Date clause - By Rchinnaw Dated: 03/11/2005
--Commented by By JSINGH on 12/29/04 for Atlas Event# 5588
--AND expiration_date IS NULL and Added condition rev_no 
				AND prop_id = :p_cement_prop_id 
        AND alternate_no = :p_cement_alt_no
        AND get_prd_type(prddes_prd_id) = 'DEPARTMENT' 
        AND EXISTS ( SELECT 'x' 
                       FROM short_specs
                      WHERE prd_id = proposal_elements.prddes_prd_id 
                        AND spec_name = 'DEPARTMENT NO.' 
                        --AND text_line = SUBSTR(:departments,2,2)
                   )
        AND EXISTS ( SELECT 'x' 
                       FROM short_specs
                      WHERE prd_id = proposal_elements.prddes_prd_id 
                        AND spec_name = 'PROCESS NO.' 
                       -- AND text_line = SUBSTR(:departments,1,1)
                   )
                   );

TYPE rec_prop_dept IS RECORD (prd_id NUMBER,peu_id NUMBER/*,dept_no NUMBER*/,dept_desc VARCHAR2(2000));
--vr_rec_prop_dept rec_prop_dept;

TYPE ty_tab_prop_dept IS TABLE OF  rec_prop_dept
INDEX BY binary_integer;
vr_ty_tab_prop_dept ty_tab_prop_dept;
cnt NUMBER:=0;
fetch_cnt NUMBER:=0;
v_dept_code number;
/*function fn_dept_code(p_prd_id number) return number is
v_dept_code number;
v_code number;
begin
SELECT text_line into v_code
FROM  short_specs 
whERE prd_id = p_prd_id 
AND spec_name = 'PROCESS NO.';
  v_dept_code:=v_code;
 select text_line into v_code
 FROM  short_specs 
 WHERE prd_id = p_prd_id 
 AND spec_name = 'DEPARTMENT NO.';
 v_dept_code:=v_dept_code||v_code;
 return v_dept_code;	
end;*/

BEGIN

	
OPEN c_prop_dept;
/*LOOP EXIT WHEN c_prop_dept%NOTFOUND; 
fetch_cnt:=fetch_cnt+1;  
FETCH c_prop_dept INTO vr_ty_tab_prop_dept(fetch_cnt);
END LOOP;*/
fetch c_prop_dept bulk collect into vr_ty_tab_prop_dept;
CLOSE c_prop_dept;

FOR i IN vr_ty_tab_prop_dept.first..vr_ty_tab_prop_dept.last LOOP
--Dbms_Output.put_line(vr_ty_tab_prop_dept(i).prd_id||'-'||vr_ty_tab_prop_dept(i).peu_id||'-'||vr_ty_tab_prop_dept(i).dept_no||'-'||vr_ty_tab_prop_dept(i).dept_desc);
--cnt:=cnt+1;
--v_dept_code:=fn_dept_code(vr_ty_tab_prop_dept(i).prd_id);
INSERT INTO report_tmp(order_no,rec_level,key_nf3,key_nf4 ,key_nf5,key_cf3)
values(/* cnt,cnt,*/i,i,vr_ty_tab_prop_dept(i).prd_id,vr_ty_tab_prop_dept(i).peu_id,/*v_dept_code*/vr_ty_tab_prop_dept(i).dept_no,vr_ty_tab_prop_dept(i).dept_desc);
END LOOP;
END;

PROCEDURE test IS
BEGIN
  for i in (select sysdate,select sysdate from dual) from dual) loop
  	null;
  	end loop;
END;

SQL> create or replace PROCEDURE test IS
  2  BEGIN
  3    for i in (select sysdate,select sysdate from dual) from dual) loop
  4     null;
  5     end loop;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/12     PL/SQL: SQL Statement ignored
3/27     PL/SQL: ORA-00936: missing expression
3/54     PLS-00103: Encountered the symbol "FROM" when expecting one of
         the following:
         loop

SQL>

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure test is
  2    begin
  3    FOR i IN (SELECT SYSDATE,(SELECT SYSDATE FROM dual) dt FROM dual)
  4    LOOP
  5   Dbms_Output.put_line(i.SYSDATE||'-'||i.dt);
  6    END LOOP;
  7*   END;
SQL> /

i want to know that why this type of select queries(means select query used as column) is not supported in oracle form, its working fine we use this type of queries in backend but why gives error in frontend(oracle form/report)

please suggest..

Procedure created.