Link to home
Start Free TrialLog in
Avatar of NiceMan331
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

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;

Open in new window


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;

Open in new window

Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

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?
Avatar of NiceMan331
NiceMan331

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
"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.
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
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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',query_data_source_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.
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:

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;

Open in new window


We called this program unit from a WHEN-RADIO-CHANGED trigger.
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.
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
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 :
select unit , sum(qty * unit_price)
from cr_gnditem
group by unit

Open in new window

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

Open in new window

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

Open in new window

then i will create 2 non_database item as per your advice in non_database block to imput date creteria
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 :
select catigory , sum(qty * unit_price)
from cr_gnditem
where unit = :bk1.unit
group by catigory

Open in new window

and , if i select one record of bk2 , to clear bk3 and filter it to display details of item
for 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

Open in new window

then , if i select one record of bk3 , to display transaction details for that item in bk4 like this
select * from cr_gnditem
where unit = bk1.unit
and catigory = bk2.catigory
and item = bk3.item

Open in new window

of course , considering date range for all queries
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
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
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