oracle triiger on list item not continue

i have oracle form
list item
when_mouse_click



 DECLARE
 	tr_c varchar2(10);
 	user_c varchar2(20);
 	  		Cursor c_tran IS
 	 select * from tr_entry
                        
                    WHERE   
                           doc_date >= :norm.dt_fr
                           and  doc_date <= :norm.dt_to
                                    and tr_code = tr_c
                                    and create_alpha_user = user_c 
                                    order by tran_no2 desc
                                    ;
    		
 	  		
			                   vb  c_tran%rowtype;

        
   
       BEGIN
     message('start'); 	
        message(:norm.list_code);   
       	select SUBSTR(:norm.list_code,1,
                 INSTR(:norm.list_code,'-',-1)-1) into tr_c from dual;
            

select SUBSTR(:norm.list_code
                 ,INSTR(:norm.list_code,'-',-1)+1
                 ) into user_c from dual;
              message(tr_c);
               message(user_c);     
 	clear_list('LIST_ENT');
     

 OPEN c_tran;
    loop
       	
         fetch c_tran into vb;
         
         exit when c_tran%notfound;
    IF c_tran%FOUND THEN  -- fetch succeeded
    	
    
  	  ADD_LIST_ELEMENT('LIST_ENT',1,RPAD(RTRIM(vb.tran_no2),10,' ')||'. ' ||RPAD(RTRIM(TO_CHAR(vb.doc_DATE,'DD-MM-YY')),12,' ')||'. ' ||RPAD(RTRIM(vb.amount),7,' ')||'. ' ||RPAD(RTRIM(vb.notes),25,' '),vb.tran_no2);
  	ELSE  -- fetch failed, so exit loop
      EXIT;
   end if;
    END LOOP;
    CLOSE c_tran;
      	
      EXCEPTION WHEN OTHERS THEN  
        RAISE;
    
    END;  

Open in new window

there is push botton having similiar procedure to fill value into this list  "LIST_CODE"
and this trigger here , when_mouse_click , it bringing another RECORDS into another list item "LIST_ENT"
this trigger working well in another form ,
then i copied this those " push button m LIST-CODE AND LIST_ENT  to this form
but it didn't perform any step except the first message      message('start');       
when mouse clicked , the pointer immidiatly back to the  push button :which be the lattest called
i'm worry why it not working here
i thought because a timer , some triggers in this forms required timer to run
then i added a timer like this just to test

declare
      timer_id Timer;
immediate NUMBER(5) := 5;
BEGIN
 message('mode:' ||   :system.mode, acknowledge);

   IF :system.mode = 'ENTER-QUERY'   then
message('before create timer', acknowledge);

        timer_id := CREATE_TIMER('ch_list', immediate, NO_REPEAT); 
        if form_success then
message('after create timer success' , acknowledge);
message(:norm.dt_fr);
message(:norm.list_code);
else
message('after create timer failure', acknowledge);
end if;
   end if;
    

end;

Open in new window

   
it displayed messages about :
system_mode = "enter_query"
before create timer', acknowledge);

     
message('after create timer success' , acknowledge);
message(:norm.dt_fr);
and stopped here not giving any value for the list_code

and here is the simple code when_timer-expired
F GET_APPLICATION_PROPERTY(TIMER_NAME) = 'ch_list' THEN
              
               message('helloe');
                     
       message(:norm.list_code);
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
problem seems to be in
message(:norm.list_code);
does item list_code exist within the block :norm
what value do you expect to see in the message /is it a large message?)
0
NiceMan331Author Commented:
yes the list code exist in block norm which is non database block. ,    the expected message is value of item of cursor and not exceed 3 digits  ,  again the cycle tested and works first in non database form
0
NiceMan331Author Commented:
by the way can I know also why the message not displayed in when timer expired ? it seemed that not created at all
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NiceMan331Author Commented:
i found the mistake
because the form opened in "enter-query" mode which is requested by me to perform the basic codes
when i moved the block :norm to the first priority , the form opened in "normal" mode and the code works
but i still need to be back to open the form in 'enter_query" ,
i have a push button to change form mode like this " your code before"

begin
	 start_change_label; 
IF :SYSTEM.mode = 'NORMAL'THEN
	clear_record;
	clear_block;
                                                                                 	
	ENTER_QUERY;
ELSE 
	Exit_form;
END IF;
		end;

Open in new window


that button working before very well , but now when form open as usual on "enter_query" , the pointer stand on the first item on the database block
here , if i press the button to change mode to be normal in order to run my new codes
the form closed
0
flow01Commented:
And after executing start_change_label system_mode is still in enter_query ?
0
NiceMan331Author Commented:
yes , because when_new-form_instance is :

begin
	 start_change_label; 
  

IF :SYSTEM.mode = 'NORMAL'THEN 
 
	ENTER_QUERY;
ELSE 
	EXECUTE_QUERY;
END IF;
  
end;

Open in new window

0
NiceMan331Author Commented:
please look at the attached image
1- i moved the non database block "norm" to be 1st block and the system mode is "normal"  as per labeled below in the  3rd button of the second layer
2- if i press button "find" the list-code filled by values
3- when mouse click on list-code , it will filter values in list_ent
4- when mouse click on list-ent , its value copied to "entry_no" which is the 1st item in the database block "block1"
5-in this point key_next_item of "block1.entry_no" will execuet database proceduer to bring records from another table here when mode is "enter_query" ,
6- i just build radio button in below to select entry_mode as the following :
  execuet - system to be enter-query and execuet the database proceduer
  query - system to be enter-query and query only records
manual - system to be normal then manual data entry


the problems :
1- now once form opened , i can perform the steps 2,3 and 4 above , but step no 5 not because the system mode is normal , even i change it to enter_query as per the 3rd button of 2ne layer , it fail to execuet the database proceduer
2- once form open , i can't change its mode to " enter-query" via the button mentioned above
3- to perform the step no 6 , and for the radio button i pout this trigger
begin
if :norm.gr_sel in(1,2) then

IF :SYSTEM.mode = 'NORMAL' THEN

	clear_record;
	clear_block;
                                                                                  	
	ENTER_QUERY;


END IF;
else
	IF :SYSTEM.mode <> 'NORMAL' THEN
			Exit_form;
			end if;
end if;

		end;

Open in new window

error message : this function can't be perform here
Untitled.png
0
flow01Commented:
Debug what function can't be performed.
check when evaluating the :system.mode if you are in the block you expect to be
(:system.current_block).
0
NiceMan331Author Commented:
ok i will
let me understand something about : "enter_query" mode of the form :
1- if form mode is : "enter_query" , it has database & non_database blocks , it is understood that the form will query based value of any database item , but what about if there is any default value for a non_database items ? is will affect any thing to query the form ?
2- also , if form mode is : "enter_query" , it has database & non_database blocks , if you type any value in non_database item , is will not allowe to change the mode to "normal" ?
0
NiceMan331Author Commented:
also :
is list_item having elements , or radion group having default value  , both are non_database items but part of database block  , may affect query block
0
flow01Commented:
I don't know what happens if you do an enter_query in a non_database block, that is the reason I asked to include (:system.current_block).   in your debugging.
I know that if you push a button on a non_database block forms try to navigate to that block unless  the 'navigable' properties of that button are set to no.
But I don't know how that works with a radio-button.
The enter-query will just have an effect on the current-block , other blocks wil not be effected.
But be aware that some actions (like pushing a button as mentioned before) can change the current block .
0
NiceMan331Author Commented:
ok now every thing is ok , but i still have 1 issue
when_mouse_click on list_ent , i have this code

declare
	tr_c number;
begin
	go_block('master_block');   ( the database block )
	enter_query	;
		go_item('tran_no');
	:master_block.tran_no := :norm.list_ent;
	
		SELECT sUBSTR(:norm.list_code,1,
                 INSTR(:norm.list_code,'-',-1)-1) into tr_c from dual;
             
     Get_Mast(:norm.list_ent,tr_c);   ( database proceduer to insert values into a both master & details tables as per your previous advice)
    


	 execute_query;
	 end;

Open in new window


here the trigger works well , the procedure executed successfully , but :
1- it query the master block record only and didn't for the details block "det_block"
( the procedure inserted for both master & details block )
2- it immateriality sent me system message " do you want to save changes "
when i select cancel , both master & details block be displayed
when no or yes it will save or abort , but will query the very 1st record in the table
i want the record be on the screen for both master & details then i will click my save push button to save or to abort
0
NiceMan331Author Commented:
and more thing : when mouse click , it didn't perform from the first click on the mouse , I have to click the mouse 2 , 3 times to perform the trigger
0
flow01Commented:
0  what is the reason for enter_query  on the masterblock
    If you execute the procedure with :norm.list_ent , wil there be  records inserted with other tran_no then :norm.list_ent ?
   And what must happen with records you inserted on a previous call to  the procedure ?
1  I remember something about not creating a master/detail form using the form wizard.
    Didn't you have to do something extra to populate the detail-blocks?
   Try adding the same action here (if the original action was build in a trigger , make it a procedure and call that procedure from both the trigger or here.
   Else : you fetch the data by executing the detailblock by your self
       execute_query;  -- master query
       go_block('det1');
       execute_query;;  -- det1 query
       go_block('det2');
       execute_query;;  -- det2 query
       go_block('master');
2)  are there changes  and in what block ?
      If yes , but you don't want to commit them yet,  use the  POST build-in to make the changes in the database without committing them.  
      "when i select cancel , both master & details block be displayed" >  the old data before the click or the data the procedure inserted : and is that inclusief the the detail data ?  
"when no or yes it will save or abort , but will query the very 1st record in the table"  > and that is the first record in the master table overal or the one with the tran_no you entered in enter_query-mode?
3) an you are not using a battery operated mouse ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
0  what is the reason for enter_query  on the masterblock
    If you execute the procedure with :norm.list_ent , wil there be  records inserted with other tran_no then :norm.list_ent ?
 
no , actually the procedure will insert records based on a selected value on the list box
then i want same this record to be display in the master/detail block for review it before saving
in my previous question , i make the form open on enetr_query mode , then when typing a value on the first field , next_item trigger will call the procedure to insert records in database then execute
because of that i put : enter_query here in this example

2)  are there changes  and in what block ?

no , no changes happened at all , just displaying the records inserted by the procedure

      "when i select cancel , both master & details block be displayed" > 
i mean the same master/details records inserted in procedure without any changes

"when no or yes it will save or abort , but will query the very 1st record in the table"  > and that is the first record in the master table overal or the one with the tran_no you entered in enter_query-mode?

yes , the first record in the master table overal

3) an you are not using a battery operated mouse ? no , im using wire mouse (usb port)
0
NiceMan331Author Commented:
yes , you are correct
i adjusted the code in : when_mouse_click like this :

	SELECT sUBSTR(:norm.list_code,1,
                 INSTR(:norm.list_code,'-',-1)-1) into tr_c from dual;
     Get_Mast(:norm.list_ent,tr_c); 
  
go_block('master_block');

	 execute_query;

Open in new window


and all issues is ok now
thanx alot
0
NiceMan331Author Commented:
by the way , the problem of pressing many times on mouse been not from the trigger , the procedure consumed more than 12 seconds to compiled , because of that when I didn't see the transaction at once , I clicked many times till complete and then appear , I will post new topic about describing my procedure in purpose to reduce the many calculation ,    please respond to it later                                              and regarding why it display the very old record ? because I didnt put creteria in pre wuery , now is ok
0
NiceMan331Author Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.