NiceMan331
asked on
Error Writing On A Non Database Form
hi
i have one issue , have form with non_database 2 blocks
i want to bring data in a trigger and fill it in the items of the 2 blocks
but when trigger finish fill the 1st block , the trigger stoped with error
record must be enetered or deleted first
previously i have same issue , experts advised me to use timers
now i have same issue , with same error
i used timer , but still have same error
here is the trigger of the when_button_pressed
and here is the trigger of when_timer_expired
i have one issue , have form with non_database 2 blocks
i want to bring data in a trigger and fill it in the items of the 2 blocks
but when trigger finish fill the 1st block , the trigger stoped with error
record must be enetered or deleted first
previously i have same issue , experts advised me to use timers
now i have same issue , with same error
i used timer , but still have same error
here is the trigger of the when_button_pressed
declare
timer_id Timer;
begin
timer_id :=create_timer('MY_TIMER_1',5,NO_REPEAT);
timer_id :=create_timer('MY_TIMER_2',5,NO_REPEAT);
end;
and here is the trigger of when_timer_expired
declare
Cursor c_ent IS
select unit,sum(PRICE) Gross,sum(DISCPRIC) Net,sum(PRICE) - sum(DISCPRIC) Discount
from cr_gnditem
where
TO_date(dob, 'dd-mm-yyyy') >= to_date(:BASIC_BLOCK.D_from, 'dd-mm-yyyy')
and TO_date(dob, 'dd-mm-yyyy') <= to_date(:BASIC_BLOCK.D_to, 'dd-mm-yyyy')
group by unit
order by sum(PRICE) desc ;
Cursor c_str IS
SELECT STR_NO FROM CR_STORE
WHERE STR_ACT = 1
and str_no not in (
select unit
from cr_gnditem
where
TO_date(dob, 'dd-mm-yyyy') >= to_date(:BASIC_BLOCK.D_from, 'dd-mm-yyyy')
and TO_date(dob, 'dd-mm-yyyy') <= to_date(:BASIC_BLOCK.D_to, 'dd-mm-yyyy')
group by unit)
ORDER BY STR_NO;
Cursor c_cat IS
select CATEGORYID,(PRICE) Gross,((PRICE)- (DISCPRIC)) Discount , (DISCPRIC) Net
from cr_gnditem
where
TO_date(dob, 'dd-mm-yyyy') >= to_date(:BASIC_BLOCK.D_from, 'dd-mm-yyyy')
and TO_date(dob, 'dd-mm-yyyy') <= to_date(:BASIC_BLOCK.D_to, 'dd-mm-yyyy')
group by CATEGORYID
order by sum(PRICE) desc;
vk c_ent%rowtype;
vc c_str%rowtype;
vt c_cat%rowtype;
cat varchar2(70);
itm varchar2(70);
s_gr number(10,2);
begin
IF GET_APPLICATION_PROPERTY(TIMER_NAME) = 'MY_TIMER_1' THEN
Go_Block('BLC_SUMM');
clear_block(no_validate);
first_record;
OPEN c_ent;
loop
fetch c_ent into vk;
exit when c_ent%notfound;
:BLC_SUMM.UNT:= vk.unit;
-- :BLC_SUMM.D_COUNT:= vk.nmon;
:BLC_SUMM.IT_GR:= vk.gross;
:BLC_SUMM.IT_DISC := vk.Discount ;
:BLC_SUMM.IT_NET := vk.net ;
next_record;
end loop;
CLOSE c_ent;
OPEN c_str;
next_record;
loop
fetch c_str into vc;
exit when c_str%notfound;
:BLC_SUMM.UNT:= vc.str_no;
:BLC_SUMM.IT_GR:= 0;
:BLC_SUMM.IT_DISC := 0 ;
:BLC_SUMM.IT_NET := 0 ;
next_record;
end loop;
CLOSE c_str;
first_record;
end if;
IF GET_APPLICATION_PROPERTY(TIMER_NAME) = 'MY_TIMER_2' THEN
select sum(PRICE) into s_gr
from cr_gnditem
where
TO_date(dob, 'dd-mm-yyyy') >= to_date(:BASIC_BLOCK.D_from, 'dd-mm-yyyy')
and TO_date(dob, 'dd-mm-yyyy') <= to_date(:BASIC_BLOCK.D_to, 'dd-mm-yyyy');
-- message(s_gr);
Go_Block('BLC_cat');
clear_block(no_validate);
first_record;
OPEN c_cat;
-- message('enetered');
loop
fetch c_cat into vt;
exit when c_cat%notfound;
select cat_name into cat from cr_cat where cat_catid = vt.CATEGORYID;
--message('looped');
-- :BLC_cat.UNT:= vt.unit;
:BLC_cat.catigory:= cat;
:BLC_cat.cat_GR:= vt.gross;
:BLC_cat.cat_DISC := vt.Discount ;
:BLC_cat.cat_NET := vt.net ;
:BLC_cat.cat_prc := vt.gross/s_gr ;
:BLC_cat.disc_prc := vt.Discount/vt.gross ;
next_record;
end loop;
CLOSE c_ent;
first_record;
end if;
end;
My question is; why do you want to do all of this manual coding to try to get non-database form blocks populated with data, when this is what Oracle Forms does very easily if you simply make these database blocks instead?
ASKER
good question
actually , i'm using this form to just display some data
instead of using report
for example :
1st block will display summary data of sales transactions for all branches within a specific period
(one record for each branch for total sales by amount )
where i have 2 "date" items and push button to run and fill the data in that block
then , when i click one record = ( the record summary of one branch ) , here the trigger will fill the summary per items of sales for that branch in another data block
then , if i select one record in that block ( which means specific product ) , trigger will bring sales transactions for that product of that branch in the 3rd data block
actually , list item is good to display the above data , but honestly , i fail to have all data aligned correctly under each other
look my topic here and the advice of henka
https://www.experts-exchange.com/questions/28360195/using-list-view.html
actually , i'm using this form to just display some data
instead of using report
for example :
1st block will display summary data of sales transactions for all branches within a specific period
(one record for each branch for total sales by amount )
where i have 2 "date" items and push button to run and fill the data in that block
then , when i click one record = ( the record summary of one branch ) , here the trigger will fill the summary per items of sales for that branch in another data block
then , if i select one record in that block ( which means specific product ) , trigger will bring sales transactions for that product of that branch in the 3rd data block
actually , list item is good to display the above data , but honestly , i fail to have all data aligned correctly under each other
look my topic here and the advice of henka
https://www.experts-exchange.com/questions/28360195/using-list-view.html
"i'm using this form to just display some data instead of using report."
OK, Oracle Forms can do that very well and very easily *IF* you use database blocks. Just set the block properties: "Insert allowed", "Update allowed" and "Delete allowed" all to "No". That should make this form *MUCH* easier to build and maintain than if you try to do this with manual triggers in non-database blocks.
OK, Oracle Forms can do that very well and very easily *IF* you use database blocks. Just set the block properties: "Insert allowed", "Update allowed" and "Delete allowed" all to "No". That should make this form *MUCH* easier to build and maintain than if you try to do this with manual triggers in non-database blocks.
ASKER
Can you please use my example and explain to me how to do it in a database blocks ? Be care that I need to retrieve data based on a selected value from other block
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I agree with using database views , but the problem is that our DBA doesn't know that I'm connecting to the database , if I create a view they will know , and they may dis allow me to use the data any more
"our DBA doesn't know that I'm connecting to the database".
That sounds like a business problem to me. You don't have to use views for this, but they would make things easier. You can do this all in Forms, if you have to.
That sounds like a business problem to me. You don't have to use views for this, but they would make things easier. You can do this all in Forms, if you have to.
ASKER
Actually it is not a business problem , I'm the only analyses user for the data , and my IT team must provide me every reports would help me to perform my job as financial controller , but when they didn't answer my many requests , so I hardly go to do it myself , anyhow , what is your final advice , and , regarding views , if I use it as a data block source , how I can filter it based on date creteria in the form ?
Oracle views do not accept filters or parameters. But, Oracle Forms blocks based on views can supply or accept any filters that you build in, or they can accept any query criteria that is provided by the user and use that to filter the rows returned.
An Oracle Form with two database blocks that are based either on tables or views can have a "Relation" specified in the form. This allows Oracle Forms to easily query the second block based on the current record in the first block.
An Oracle Form with two database blocks that are based either on tables or views can have a "Relation" specified in the form. This allows Oracle Forms to easily query the second block based on the current record in the first block.
ASKER
Ok , may I ask you 2 questions : 1- how to da the filter in the form for database block based on a view , 2- is it possible to change the data source of the block by code , in other word , can use radio option to select data source ?
1. How do you use a query filter in a database block based on a view? Exactly like you do in a database block based on a table. You don't have to do anything (or at least not much) different in a block based on a view than a block based on a table. You may need to set the: Block property, Database, Key Mode to something other than the default value (Automatic). You may need this to be either Unique or Non-Updateable. And you may need to indicate which column(s) is/are the primary key column(s). And, you may need to write an "ON-LOCK" trigger that just contains:
begin
null;
end;
/
2. Can you change the data source dynamically at runtime? Yes. Use: set_block_property([block_ name',quer y_data_sou rce_name,' [table_or_ view_name] '):
I've done that in at least one Forms6 form. But, this will only work if you have multiple tables or views with identical column names.
begin
null;
end;
/
2. Can you change the data source dynamically at runtime? Yes. Use: set_block_property([block_
I've done that in at least one Forms6 form. But, this will only work if you have multiple tables or views with identical column names.
Here is an example of a program unit that I wrote in Forms6 to change the QUERY_DATA_SOURCE dynamically in a database block of a form:
We called this program unit from a WHEN-RADIO-CHANGED trigger.
PROCEDURE ck_source (new_source in varchar2, val_ok in boolean default TRUE) IS
v_block block;
v_source varchar2(30);
v_status varchar2(30);
full_source varchar2(30);
BEGIN
full_source := 'inv_trans_history_'||new_source||'_view';
v_block := find_block('MAIN');
v_source := get_block_property(v_block,query_data_source_name);
v_status := get_block_property(v_block,status);
if v_source = full_source then
null;
else
if v_status = 'NEW' then
null;
else
if val_ok then
:control.go_to_field := :system.cursor_item;
go_block('main');
clear_block;
go_item(:control.go_to_field);
else
:control.from_date := :control.prev_from_date;
msg_alert('Sorry, you must clear the main block (below) before making this change.','W',TRUE);
end if;
end if;
set_block_property('main',query_data_source_name,full_source);
end if;
END;
We called this program unit from a WHEN-RADIO-CHANGED trigger.
ASKER
I'm really feel that you added a lot to my little knowledge , and you may entitled for double scores , anyhow please give me a chance to try your last code , and then regarding filtering on the block , I don't know what is the correct code to do filter the data based on a value on an item on the form
"I don't know what is the correct code to filter the data based on a value on an item on the form."
Have you ever used block relationships in Oracle Forms? These directly support the filtering you need to query a child block based on a value (or values) in the parent block.
Have you ever used block relationships in Oracle Forms? These directly support the filtering you need to query a child block based on a value (or values) in the parent block.
ASKER
Yes I do relations , but I'm asking if I have for example 2 non database items to type creteria dates then the block should filter its data as per this creteria
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok , i got it
can you please keep this question open for some times
because now i'm going to install developer 11g
then i will correct the sycle as per advice of markger
thanx
can you please keep this question open for some times
because now i'm going to install developer 11g
then i will correct the sycle as per advice of markger
thanx
If you want to use 2 non database items to type criteria (like date values) into then have the block filter (query) its data based on this criteria, your 2 non-database items need to be in a non-database control block. Then in the "default where" property for your data block, you simply reference your non-database items in the control block.
ASKER
marger
please see the following scenario of the expected form and give me your advice :
1 - the basic table : cr_gnditem , which is containing sales transactions , consist of the following fields :
units number ( = store number )
dob date ( transaction date )
item varchar2
catigory varchar2
qty number
unit_price number
2 - i want to create 4 blocks , bk1 - bk2 - bk3 - bk4
bk1 : will be based on view1 to display summary sales per unit like this :
now , i want if i select a record in bk1 ( means specific record of one store )
here i want to cleare bk2 and filter it to display sales details by catigory for unit value of bk1
like this :
for that catigory like this :
now , the question is :
do you think this scenario good or bad ?
then , how the relationship will be between the blocks
because i didn't find any relationship could be done for blocks : bk1,bk2 and bk3 for the first display
yes for the second display , i can find relationship between the 4 blocks
i think i make it very complicate
and i have no enough space to create 7 blocks in the same canvase
please see the following scenario of the expected form and give me your advice :
1 - the basic table : cr_gnditem , which is containing sales transactions , consist of the following fields :
units number ( = store number )
dob date ( transaction date )
item varchar2
catigory varchar2
qty number
unit_price number
2 - i want to create 4 blocks , bk1 - bk2 - bk3 - bk4
bk1 : will be based on view1 to display summary sales per unit like this :
select unit , sum(qty * unit_price)
from cr_gnditem
group by unit
bk2 : will be based on view2 to display summary sales per catigory for all units :select catigory , sum(qty * unit_price)
from cr_gnditem
group by catigory
bk3 : will be based on view3 to display summary sales per item for all units :select catigory,item , sum(qty * unit_price)
from cr_gnditem
group by catigory,item
then i will create 2 non_database item as per your advice in non_database block to imput date creterianow , i want if i select a record in bk1 ( means specific record of one store )
here i want to cleare bk2 and filter it to display sales details by catigory for unit value of bk1
like this :
select catigory , sum(qty * unit_price)
from cr_gnditem
where unit = :bk1.unit
group by catigory
and , if i select one record of bk2 , to clear bk3 and filter it to display details of itemfor that catigory like this :
select catigory,item , sum(qty * unit_price)
from cr_gnditem
where unit = bk1.unit
and catigory = bk2.catigory
group by catigory,item
then , if i select one record of bk3 , to display transaction details for that item in bk4 like thisselect * from cr_gnditem
where unit = bk1.unit
and catigory = bk2.catigory
and item = bk3.item
of course , considering date range for all queriesnow , the question is :
do you think this scenario good or bad ?
then , how the relationship will be between the blocks
because i didn't find any relationship could be done for blocks : bk1,bk2 and bk3 for the first display
yes for the second display , i can find relationship between the 4 blocks
i think i make it very complicate
and i have no enough space to create 7 blocks in the same canvase
It looks to me like you need six blocks:
1. the basic table : cr_gnditem
2. a control block to hold your non-database date parameter values
3, 4, 5 and 6: the four child blocks you described as: bk1 - bk2 - bk3 - bk4
It is not clear to if your first description of the contents for the child blocks:
(bk1 : will be based on view1 to display summary sales per unit like this :
bk2 : will be based on view2 to display summary sales per category for all units :
bk3 : will be based on view3 to display summary sales per item for all units :)
includes any connection (relationship) to the record(s) displayed in the basic table block or not.
The following tasks are very easy to do, if you set up block relationships:
if I select a record in bk1 ( means specific record of one store )
here I want to clear bk2 and filter it to display sales details by category for unit value of bk1
like this :
if I select one record of bk2 , to clear bk3 and filter it to display details of item
for that category like this :
if I select one record of bk3 , to display transaction details for that item in bk4
1. the basic table : cr_gnditem
2. a control block to hold your non-database date parameter values
3, 4, 5 and 6: the four child blocks you described as: bk1 - bk2 - bk3 - bk4
It is not clear to if your first description of the contents for the child blocks:
(bk1 : will be based on view1 to display summary sales per unit like this :
bk2 : will be based on view2 to display summary sales per category for all units :
bk3 : will be based on view3 to display summary sales per item for all units :)
includes any connection (relationship) to the record(s) displayed in the basic table block or not.
The following tasks are very easy to do, if you set up block relationships:
if I select a record in bk1 ( means specific record of one store )
here I want to clear bk2 and filter it to display sales details by category for unit value of bk1
like this :
if I select one record of bk2 , to clear bk3 and filter it to display details of item
for that category like this :
if I select one record of bk3 , to display transaction details for that item in bk4
ASKER
for those
(bk1 : will be based on view1 to display summary sales per unit like this :
bk2 : will be based on view2 to display summary sales per category for all units :
bk3 : will be based on view3 to display summary sales per item for all units :)
the 3 views are based on same table , but with different selection
as i described before
but actually i didn't find any relationship between the blocks selections
(bk1 : will be based on view1 to display summary sales per unit like this :
bk2 : will be based on view2 to display summary sales per category for all units :
bk3 : will be based on view3 to display summary sales per item for all units :)
the 3 views are based on same table , but with different selection
as i described before
but actually i didn't find any relationship between the blocks selections