Home » Developer & Programmer » Forms » error in cursor query (form 10g window 7)
error in cursor query [message #447539] Mon, 15 March 2010 22:58 Go to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
hi all,

i have one forntend procedure which contain one cursor.

it gives error for cursor query at compile time.

the error generated when using select query which is in italic- bold, without that query it working fine. but same code it working fine in backend.Please suggest what is the problem in that select query.



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;

Best regards
Jitender sadh
==============
Re: error in cursor query [message #447577 is a reply to message #447539] Tue, 16 March 2010 03:40 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The same as your previous question - move that code into a stored procedure and call it from a form.
Re: error in cursor query [message #447631 is a reply to message #447539] Tue, 16 March 2010 22:09 Go to previous messageGo to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
hi,

its fine that bulk collect is not supported by oracle forms and it gives you informative message when you are trying to use that, but is it also the case that oracle forms does not support below query.

Quote:
(select sysdate,select sysdate from dual) from dual)


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


its fine when you are using both from backend.

Best regards
jitender sadh
==============
Re: error in cursor query [message #447749 is a reply to message #447631] Wed, 17 March 2010 13:42 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you kidding? Since when is such a syntax valid?
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>

Could you explain what you mean?
Re: error in cursor query [message #447791 is a reply to message #447749] Wed, 17 March 2010 22:15 Go to previous messageGo to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
hi,

sorry for last mail actually i forget to put bracket before inner select query. the code is like
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.


best regards
Jitender sadh
=============
Re: error in cursor query [message #447833 is a reply to message #447791] Thu, 18 March 2010 03:35 Go to previous message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know why it is not supported. Probably Forms and database PL/SQL engines aren't the same.
Previous Topic: Image
Next Topic: record no longer present after commit in form
Goto Forum:
  


Current Time: Tue Sep 27 11:05:54 CDT 2016

Total time taken to generate the page: 0.13102 seconds