Avatar of chalie001
chalie001
 asked on

Global variable in form

hi how can i reset global variable in oracle form 11g i have the following block IN MY when-new-form-instance


:block_home.enter_employee_id := substr(:global.ent_empid,1,4)||' - '||substr(:global.ent_ent_empid,5,2)||' - '||substr(:global.ent_empid,7,3)||' - '||substr(:global.ent_empid,10,4);	
:BLOCK_HOME.CURR_employee_id    :=substr(:global.uni_empid,1,4)||' - '||substr(:global.uni_empid,1,2)||' - '||substr(:global.uni_empid,3,3)||' - '||substr(:global.uni_empid,6,4);

Declare
	Cursor C_jobs 
	  is
	  Select employee_id, start_date, end_date,
	         job_id, department_id 
	         from hr.job_history
	         where employee_id = substr(:global.uni_empid,5,9); --am geting value as -18-047-6 instead of 5320180476694 how can i resert :BLOCK_HOME.CURR_employee_id
begin
	go_block('job_history');
	-- first clear the block if it contains any records
	clear_block(no_validate);
	-- move control to first record;
	first_record;
	for cur in C_jobs loop
		 :job_history.employee_id := cur.employee_id;
		 :job_history.start_date := cur.start_date;
		 :job_history.end_date := cur.end_date;
		 :job_history.job_id := cur.job_id;
		 :job_history.department_id := cur.department_id;
		 -- move control to next record;
		 next_record;
	end loop;
	-- again after completion move control to first record
	first_record;
	
end;

Open in new window

DatabasesOracle DatabaseSQLMongoDB

Avatar of undefined
Last Comment
Mark Geerlings

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mark Geerlings

I'm looking over you trigger some more now, and I'm wondering why you need this trigger?  Why not base this form block on the hr.job_history table?  Then your trigger could simply be:
begin
      go_block('job_history');
        execute_query;
end;
Mark Geerlings

If you want that query limited to a particular value from a :global variable, that is possible.  One way to do this is to add a non-database, control record to your form.   This block does not need any items that the user can see or interact with directly.  Your when-new-form-instance trigger should extract the value that you want from the global variable, and store that in a an item in your control block.  You can add a pre-query trigger in your job_history block to copy this value into  the appropriate item in the job_history block.  And, use a post-query trigger to clear the value from the control block item, it that is non-blank.  This will allow users of the screen to query other records after the initial query , if they want to.
chalie001

ASKER
Your help has saved me hundreds of hours of internet surfing.
fblack61
chalie001

ASKER
this what i need the global value to be
when i press the button
declare
      cursor upd_global is
             select empid from employee
              where cii.sin = bii.sin
              where  emcode||serial||empnumber = regexp_replace(:block.item.assoc_nsn,'-',null);
              
begin
      
open upd_global;
fetch upd_global into   :global.use_empid;
close upd_global;       
            
      end;
SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
chalie001

ASKER
Sorry my mistake it suppose to be and
SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.