Oracle Forms

I am using web forms 6.0.8.18.3 (6i patch9) and 10g database
I am making a new form from another very similar screen
but presenting format as child to parent view of data
(reversing similar screens parent to child format)
The new screen has 2 t tabs, 2 blocks per tab(not master/detail)
I want to preserve the prior form’s same
Query_data_source_name block property selects (if possible)
(This preserves the enter-query/execute query on the block(works fine)

I want to populate a tabular(table based) block(query only)
based on user entering a number (child) returning one or many rows
(the first field is enterable (control number)and the rest are display)
goal: so the user can query by number field and get child/parent data.
I can do a direct ‘select into’ the fields and create one record ok.
But fails if to_many_records, returns(error)

That’s why I need the cursor/loop/fetch/next_record set up
To return many records.


In a proc called by a key_exeqry trigger to populate the block,
I am using next_record in my loop but get FRM-41009
How can I achieve this via a key_exeqry?
if not
is there a way to ‘break away from the key_exeqry’,
do the loop(in a place where I will not draw a error)
based on passed parameters to populate the block?

Here is my block/Key-exeqry logic --(not ideal)

If entered field is null then
Null; --allows f7/f8 full query usin query data source name(ok)
Elsif not null
Do validate select-–validate number with select
If no data found error message(ok)
If to many rows then
Call multi record loop proc
Do the multi record procedure(program unit)cursor select/loop/fetch(fails /no next_record allowed!)
I am rotating through the loop and land on last record
(The problem is the next_record is not allowed.)
If field not null and a flag not on(=N)
Call single_record_populate procedure(select into) (works fine-ok)
End if;
I saw A. Weiden post that ‘key-exeqry fires only once for the query,
so you won’t have a chance To do a per-record operation in it’.
(so there is a better way)
But Is there any way to do this--do a block populate(with loop) based on a key-exeqry trigger?
(75% of srcreen requirements work ok so far)
Thanks.
here is actual KEY-EXEQRY code

declare
v_parent_mode_id number;
v_ep_num varchar2(4);
v_ep_description varchar2(120);
v_cd_Id number;
v_cd_capture_percent number(6,3);
dummy_alt number;
v_error_flag varchar2(1);
action varchar(80);
p_out_parent_mode_id number(10) := v_parent_mode_id;
p_out_cd_Id number(10) := v_cd_Id;
p_out_ep_num varchar2(4) := v_ep_num;
p_out_cd_Id_Num varchar2(4) := :emission_points.cd_id_num;
begin
v_error_flag := 'N';
if (:emission_points.cd_id_num is null) then
execute_query;
v_error_flag := 'Y';
elsif
(:emission_points.cd_id_num is not null) then --validate check if entry(chld) exists under parent
begin
select parent_mode_id, cd_id, cd.capture_percent, ep.num
into v_parent_mode_id, v_cd_id, :emission_points.capture_percent, v_ep_num
FROM EMISSION_POINTS EP,
EP_MODES EPM,
CAPTURING_DEVICES CD,
CONTROL_DEVICES CDT
WHERE EP.ID = EPM.EP_ID AND
EPM.ID = CD.PARENT_MODE_ID AND
CD.CD_ID IS NOT NULL AND
CD.PARENT_MODE_ID IS NOT NULL AND
CD.STACK_ID IS NULL And
cd.fac_id = :FACILITIES.ID and
cd.cd_id = cdt.id and
cdt.num = :emission_points.cd_id_num;
exception
when no_data_found then
disp_warn('Control number '||:emission_points.cd_id_num||' does not exist under parent points for id_number '
||:facilities.scr_fac1);
raise form_trigger_failure;
v_parent_mode_id := null;
v_cd_id := null;
:emission_points.capture_percent := null;
v_ep_num := null;
when too_many_rows then
v_error_flag := 'Y';
--process for multiple records returned
populate_emission_points(p_out_parent_mode_id, p_out_cd_Id, p_out_ep_num, p_out_cd_Id_Num);
end;
end if;

if (:emission_points.cd_id_num is not null) and v_error_flag = 'N' then
DISP_WARN(':emission_points.cd_id_num=>'||:emission_points.cd_id_num||' v_error_flag=>'||v_error_flag);
--process for single record returned
emission_points_record;
end if;

end;

Edited by: Doug Galayda on Apr 16, 2009 12:36 PM