Home » Developer & Programmer » Forms » Data storing in multi record block (Oracle Forms 11g, windows 7)
Data storing in multi record block [message #592639] Sat, 10 August 2013 02:27 Go to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
Hi,
i have a form with 3 blocks in our oracle application. Block1 is a single record block. Block2 and block 3 are multi record blocks with no of records 5. Block1 and block2 are in same canvas1 of a window1. Block3 is in canvas2 of window2. There is a relation between block1 and block2.
Another relation with block2 and block3 is also available.
Now my issues is while creating a record, first i entered the data in block1 (which is a single record block). Then i entered data in five rows of second block. Then i entered data in block3(multi record block) by pressing the button in block2 (each button for each row). But before saving the entire form, when i checked the data in block3 by pressing the button of first record of block2, screen shows empty.
Code in button of block2 is as follows
show_window(WIN2');
Go_block('BLOCK3');

Code in button of block3 is as follows
hide_window('WIN2');
go_block('BLOCK2');

If post; is included in block3 button. then problem we will be solved. But we dont want to use post; as it lock the table.
Pls give some solution.

Report message to a moderator

Re: Data storing in multi record block [message #592737 is a reply to message #592639] Sun, 11 August 2013 17:45 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post doesn't lock the table unless you have some other code that causes the lock when post is issued.
So I suggest you find that and fix it.

Report message to a moderator

Re: Data storing in multi record block [message #592745 is a reply to message #592737] Mon, 12 August 2013 01:20 Go to previous messageGo to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
While post; is using pre insert trigger of block1 is raising and ref no is generating. So at the same time , if another person also on working the same screen and post; is working , it will hang waiting to generate the ref no. That is why we are not prefering post;. But pls advise me how to save data in block2 and 3 without post;

Mary

Report message to a moderator

Re: Data storing in multi record block [message #592766 is a reply to message #592745] Mon, 12 August 2013 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
The only way to save data is to put it in the DB using post or commit.
It shouldn't just be going missing though, if oracle thinks it needs to clear the data in the datablocks it should ask if you want to save changes first. I suggest running the form in debug mode to see what triggers get fired when the data goes missing.
Also how are you generating ref no?

Report message to a moderator

Re: Data storing in multi record block [message #592767 is a reply to message #592766] Mon, 12 August 2013 04:33 Go to previous messageGo to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
Please find the attached file. Here i am entering data in block2 and 3 and before saving when i m checking in block3, data will not be there. I have given commit_form in SAVE_BTN but before reaching there, data is losing. Pls help me without using post;. POST; is making hang when more than one user is working together in same screen.
  • Attachment: Screen.jpg
    (Size: 200.68KB, Downloaded 828 times)

Report message to a moderator

Re: Data storing in multi record block [message #592769 is a reply to message #592767] Mon, 12 August 2013 04:39 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looking at your screen layout you have no choice but to fix the post problem.
Block 2 is master to block 3 right?
Forms will only hold data in block 3 that relates to the current selected record in block 2.
So when you change the record in block 2 forms has to clear data in block 3. It will not save it internally it has to be saved in the DB if you want to keep it.
Normally when that happens oracle will ask if you want to save changes. If you save yes it will be saved to the db, if you say no it'll just be wiped. I'm not sure why oracle isn't asking this, which is why I suggested running the form in debug mode.
Regardless you have to save data from block 3 in the DB before you can change record in block 2.
So you need to make post not lock up the table.

Report message to a moderator

Re: Data storing in multi record block [message #592904 is a reply to message #592769] Tue, 13 August 2013 01:13 Go to previous messageGo to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
Yes. Block2 is the master of block3. I am not changing data in block2..Once the data entered in block2 & 3, while viewing in block3 corresponding to first record of block2, block3 shows empty.
Can we advise me how to use post; without locking.

Report message to a moderator

Re: Data storing in multi record block [message #592948 is a reply to message #592904] Tue, 13 August 2013 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
mary.yujin wrote on Tue, 13 August 2013 07:13
Yes. Block2 is the master of block3. I am not changing data in block2

You don't need to change the data in block 2 to have an issue, just click on a different record.

mary.yujin wrote on Tue, 13 August 2013 07:13

Can we advise me how to use post; without locking.

Like I say, post doesn't do that. Some code you're written that is kicked off by the post command does that.
I can't help you fix it unless I know what the code is.

Report message to a moderator

Re: Data storing in multi record block [message #593126 is a reply to message #592948] Wed, 14 August 2013 07:16 Go to previous messageGo to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
Please find the below code in Pre-insert trigger of BLOCK1
PROCEDURE NEW_PO_REF IS

v1 number;
v2 number;
vnote1 varchar2(15);
vissdate date;
zvalue varchar2(5);
LOC VARCHAR2(2):=' ';
loc_ref varchar2(10);
curr_year number;
begin

Select srs_loc_ref,to_char(:pur_lpomain.lpo_date,'yyyy') into loc_ref,curr_year from sec_reg_settings where srs_org_code = :global.orgcode;

if loc_ref is not null then
IF :GLOBAL.ORGCODE=2 THEN
LOC:='U';
Elsif :GLOBAL.ORGCODE=3 THEN
LOC:='Q';
Elsif :GLOBAL.ORGCODE=4 THEN
LOC:='S';
END IF;

if :pur_lpomain.location = 'UAE (Local)' then
vnote1 := substr(curr_year,3,4)||'LPO-U'||LOC||'-'||loc_ref||'-';

elsif :pur_lpomain.location = 'UAE (Overseas)' then
vnote1 := substr(curr_year,3,4)||'OPO-U'||LOC||'-'||loc_ref||'-';

elsif :pur_lpomain.location = 'Das (Local)' then
vnote1 := substr(curr_year,3,4)||'LPO-D'||LOC||'-'||loc_ref||'-';

end if;
If :global.orgcode != 3 then

if :pur_lpomain.sub_po_type = 23 then
vnote1 := substr(curr_year,3,4)||'LPO-ADM'||LOC||'-'||loc_ref||'-';
end if;
end if;

If substr(curr_year,3,4)IN ('08') then

select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and location = :pur_lpomain.location
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103 and lpo_ref like vnote1||'%';

:pur_lpomain.LOC_REF := null;
Else
:pur_lpomain.LOC_REF := loc_ref;

if :global.orgcode!=3 then

select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
and nvl(sub_po_type,0)!= 23 and lpo_ref like vnote1||'%';

else
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
and lpo_ref like vnote1||'%' ;
end if;
End if;
:pur_lpomain.old := 'N';
If :global.orgcode != 3 then
if :pur_lpomain.sub_po_type = 23 then
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and nvl(sub_po_type,0) = 23 and lpo_ref like vnote1||'%';
end if;
End if;
v2 := (nvl(v1,0)+1);
:pur_lpomain.refnum := v2;
:pur_lpomain.type := 'PO';
if v2 >= 1 and v2 <= 9 then
:pur_lpomain.lpo_ref := vnote1||'000'||v2;
end if;
if v2 >= 10 and v2 <=99 then
:pur_lpomain.lpo_ref := vnote1||'00'||v2;
end if;
if v2 >= 100 and v2 <=999 then
:pur_lpomain.lpo_ref := vnote1||'0'||v2;
end if;
if v2 >999 then
:pur_lpomain.lpo_ref := vnote1||v2;
End if;
select sysdate into :pur_lpomain.lpo_date from dual;
:pur_lpomain.rev := '0';
if :pur_lpomain.sub_po_type = 23 or :pur_lpomain.po_type = 'D' then --checking whether it is travel PO
Set_item_property('pur_lpodet.sector_no',visible,property_true);
Set_item_property('pur_lpodet.sec_name',visible,property_true);
Set_item_property('pur_lpodet.empid_btn',visible,property_true);
Set_item_property('pur_lpodet.sec_btn',visible,property_true);
Set_item_property('pur_lpodet.sector_no',enabled,property_true);
Set_item_property('pur_lpodet.sec_name',enabled,property_true);
Set_item_property('pur_lpodet.empid_btn',enabled,property_true);
Set_item_property('pur_lpodet.sec_btn',enabled,property_true);
Set_item_property('pur_lpodet.thickness',visible,property_false);
Set_item_property('pur_lpodet.dimen',visible,property_false);
Set_item_property('pur_lpodet.density',visible,property_false);
else
Set_item_property('pur_lpodet.sector_no',visible,property_false);
Set_item_property('pur_lpodet.sec_name',visible,property_false);
Set_item_property('pur_lpodet.empid_btn',visible,property_false);
Set_item_property('pur_lpodet.sec_btn',visible,property_false);
Set_item_property('pur_lpodet.thickness',visible,property_true);
Set_item_property('pur_lpodet.dimen',visible,property_true);
Set_item_property('pur_lpodet.density',visible,property_true);
End if;
End if;
:pur_lpomain.new_lpo_ref := :pur_lpomain.lpo_ref;
END;

:Pur_lpomain means BLOCK1 and :pur_lpodet means BLOCK2.
In save button, code is commit_form;

Please advise me.

Report message to a moderator

Re: Data storing in multi record block [message #593127 is a reply to message #593126] Wed, 14 August 2013 07:36 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's pretty hard to read, format your code and repost it in code tags as described here: How to use [code] tags and make your code easier to read?

Report message to a moderator

Re: Data storing in multi record block [message #593204 is a reply to message #593127] Wed, 14 August 2013 23:11 Go to previous messageGo to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
In Pre insert trigger of Block1, calling a procedure

NEW_PO_REF;

which is showed below.

PROCEDURE NEW_PO_REF IS
 
v1 number;
 v2 number;
 vnote1 varchar2(15);
 vissdate date;
 zvalue varchar2(5);
 LOC VARCHAR2(2):=' ';
 loc_ref varchar2(10);
 curr_year number; 
begin

 Select srs_loc_ref,to_char(:pur_lpomain.lpo_date,'yyyy') into loc_ref,curr_year from sec_reg_settings where srs_org_code = :global.orgcode;

 if loc_ref is not null then 
IF :GLOBAL.ORGCODE=2 THEN
 LOC:='U';
 Elsif :GLOBAL.ORGCODE=3 THEN
 LOC:='Q';
 Elsif :GLOBAL.ORGCODE=4 THEN
 LOC:='S';
 END IF;
 
if :pur_lpomain.location = 'UAE (Local)' then
 vnote1 := substr(curr_year,3,4)||'LPO-U'||LOC||'-'||loc_ref||'-';

 elsif :pur_lpomain.location = 'UAE (Overseas)' then
 vnote1 := substr(curr_year,3,4)||'OPO-U'||LOC||'-'||loc_ref||'-';

 elsif :pur_lpomain.location = 'Das (Local)' then
 vnote1 := substr(curr_year,3,4)||'LPO-D'||LOC||'-'||loc_ref||'-';

 end if;
 If :global.orgcode != 3 then
 
if :pur_lpomain.sub_po_type = 23 then
 vnote1 := substr(curr_year,3,4)||'LPO-ADM'||LOC||'-'||loc_ref||'-';
 end if;
 end if;

 If substr(curr_year,3,4)IN ('08') then

 select NVL(max(refnum),0) into v1 from pur_lpomain
 where org_code = :global.orgcode and location = :pur_lpomain.location 
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103 and lpo_ref like vnote1||'%';

 :pur_lpomain.LOC_REF := null; 
Else
 :pur_lpomain.LOC_REF := loc_ref;
 
if :global.orgcode!=3 then

 select NVL(max(refnum),0) into v1 from pur_lpomain
 where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref 
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
 and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
 and nvl(sub_po_type,0)!= 23 and lpo_ref like vnote1||'%';

 else
 select NVL(max(refnum),0) into v1 from pur_lpomain
 where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref 
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
 and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
 and lpo_ref like vnote1||'%' ;
 end if;
 End if; 
:pur_lpomain.old := 'N'; 
If :global.orgcode != 3 then
 if :pur_lpomain.sub_po_type = 23 then
 select NVL(max(refnum),0) into v1 from pur_lpomain
 where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref 
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
 and nvl(sub_po_type,0) = 23 and lpo_ref like vnote1||'%';
 end if;
 End if;
 v2 := (nvl(v1,0)+1);
 :pur_lpomain.refnum := v2;
 :pur_lpomain.type := 'PO';
 if v2 >= 1 and v2 <= 9 then 
:pur_lpomain.lpo_ref := vnote1||'000'||v2;
 end if;
 if v2 >= 10 and v2 <=99 then 
:pur_lpomain.lpo_ref := vnote1||'00'||v2;
 end if;
 if v2 >= 100 and v2 <=999 then 
:pur_lpomain.lpo_ref := vnote1||'0'||v2;
 end if;
 if v2 >999 then
 :pur_lpomain.lpo_ref := vnote1||v2;
 End if; 
select sysdate into :pur_lpomain.lpo_date from dual;
 :pur_lpomain.rev := '0'; 
if :pur_lpomain.sub_po_type = 23 or :pur_lpomain.po_type = 'D' then --checking whether it is travel PO
 Set_item_property('pur_lpodet.sector_no',visible,property_true);
 Set_item_property('pur_lpodet.sec_name',visible,property_true);
 Set_item_property('pur_lpodet.empid_btn',visible,property_true);
 Set_item_property('pur_lpodet.sec_btn',visible,property_true);
 Set_item_property('pur_lpodet.sector_no',enabled,property_true);
 Set_item_property('pur_lpodet.sec_name',enabled,property_true);
 Set_item_property('pur_lpodet.empid_btn',enabled,property_true);
 Set_item_property('pur_lpodet.sec_btn',enabled,property_true);
 Set_item_property('pur_lpodet.thickness',visible,property_false);
 Set_item_property('pur_lpodet.dimen',visible,property_false);
 Set_item_property('pur_lpodet.density',visible,property_false);
 else
 Set_item_property('pur_lpodet.sector_no',visible,property_false);
 Set_item_property('pur_lpodet.sec_name',visible,property_false);
 Set_item_property('pur_lpodet.empid_btn',visible,property_false);
 Set_item_property('pur_lpodet.sec_btn',visible,property_false);
 Set_item_property('pur_lpodet.thickness',visible,property_true);
 Set_item_property('pur_lpodet.dimen',visible,property_true);
 Set_item_property('pur_lpodet.density',visible,property_true);
 End if; 
End if;
 :pur_lpomain.new_lpo_ref := :pur_lpomain.lpo_ref;
 END;



:Pur_lpomain means BLOCK1 and :pur_lpodet means BLOCK2.
In save button, code is commit_form;

Please advise me.

Report message to a moderator

Re: Data storing in multi record block [message #593218 is a reply to message #593204] Thu, 15 August 2013 02:43 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not really formatted is it? If's should be indented. Additional blank lines would also help to make it more readable.
Does that code generate a value for a primary key column?

Report message to a moderator

Re: Data storing in multi record block [message #593264 is a reply to message #593218] Thu, 15 August 2013 06:40 Go to previous messageGo to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
Yes, lpo_ref is the primary key and the value of lpo_ref is generating in the pre-insert trigger of Block1.

Report message to a moderator

Re: Data storing in multi record block [message #593266 is a reply to message #593264] Thu, 15 August 2013 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
So two people in two different sessions adding data at the same time will get the same value for lpo_ref?

Report message to a moderator

Re: Data storing in multi record block [message #593275 is a reply to message #593266] Thu, 15 August 2013 08:36 Go to previous messageGo to next message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
will not get same value. Instead one person will get hang.

Report message to a moderator

Re: Data storing in multi record block [message #593281 is a reply to message #593275] Thu, 15 August 2013 09:04 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
But I assume that it's hanging because the two sessions got the same value.
Why the first session commits can the hanging session save or does it get an ORA-00001 error?

Report message to a moderator

Re: Data storing in multi record block [message #593523 is a reply to message #593281] Sun, 18 August 2013 03:24 Go to previous message
mary.yujin
Messages: 26
Registered: June 2010
Junior Member
Yes..we use to ask the first person to save the data normally. But it is not the good practice. Pls advise some other solution.

Report message to a moderator

Previous Topic: Next-Item trigger
Next Topic: Idle time out for Forms in Oracle 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 01 11:04:43 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.