hello every one !
im using form builder version 10.1.2.0.2 , oracle 10g .
im making aproject for iron auto selling for subject at university , i have created the tables
and now during making the forms -which take along time till i feel crazy-
the problem is that i made afor for employees information to give the user the ability to check for employees through entering substring of there names and oracle gonna show the employees got the match .
i make text box and button beside the button has when button pressed trigger i wrote the following :

DECLARE
len number := length(:TEXT_ITEM15);
CURSOR SEARCH IS
select worker_id,WNAME
from workers where upper(substr(wname,0,len-1)) =upper(:TEXT_ITEM15);
begin if ( ( :TEXT_ITEM15 is not null ) ) then
open SEARCH;

loop
fetch Search into :worker_id ,:wname ;
exit when SEARCH%notfound;

END LOOP;
END IF;
End;

and its compiled without error but no execution
the form is connected to workers in database but i added departments , contact_info
as none data base items and make trigger when validate item on name
so that it will return these info when name validated .-for now this form is for checking- .
as the following :

begin select descreption
into :TEXT_ITEM11 from departments,workers
where :worker_id =worker_id and workers.dept_id =departments.dept_id;

select fax ,phone,email,address
into :TEXT_ITEM9,:TEXT_ITEM10,:TEXT_ITEM12,:TEXT_ITEM13 from contact_info
where :worker_id=woker_id ;
end;

its not working i enter name in the text box , but nothing work
do help plz ? quick
many thanks

SALAM BROTHER

WHY DO U WANT TO MAKE SEARCHING USIN THE NAME ?
SO...!!! WHATS THE RESULT IF THE USER WRITE IN THE TEXT BOX A WRONG SPELLING 4 THE EMPLOYEE NAME ?
DID U THINK ABOUT THAT ?
SO...I ADVICE U TO MAKE A SEARCH USIN THE EMPLOYEE NUMBER
OR
IF U WANNA MAKE IT VIA NAME U CAN USE DEFAULT_WHERE OR U CAN CREATE A LOV
TRY TO DO THAT AND TELL ME

الاخ الكريم

DECLARE
len number ;--:= length(:TEXT_ITEM15);
CURSOR SEARCH IS
select worker_id,WNAME
from workers where UPPER(wname)=upper(:TEXT_ITEM15);--upper(substr(wname,0,len-1)) =upper(:TEXT_ITEM15);
begin if ( ( :TEXT_ITEM15 is not null ) ) then
go_block('workers');--please take the correct block that has the result search i write this block as i mean it
open SEARCH;
loop
fetch Search into :worker_id ,:wname ;
NEXT_RECORD;
exit when SEARCH%notfound;
END LOOP;
END IF;
End; 
first_record;

the second I think This is the true

begin select descreption -- but where this column I guess this in the workers table so try
into :TEXT_ITEM11 from departments,workers
where worker_id =:worker_id and workers.dept_id =:departments.dept_id;

select fax ,phone,email,address
into :TEXT_ITEM9,:TEXT_ITEM10,:TEXT_ITEM12,:TEXT_ITEM13 from contact_info
where worker_id=:woker_id ;
end;

SALAM BROTHER

WHY DO U WANT TO MAKE SEARCHING USIN THE NAME ?
SO...!!! WHATS THE RESULT IF THE USER WRITE IN THE TEXT BOX A WRONG SPELLING 4 THE EMPLOYEE NAME ?
DID U THINK ABOUT THAT ?
SO...I ADVICE U TO MAKE A SEARCH USIN THE EMPLOYEE NUMBER
OR
IF U WANNA MAKE IT VIA NAME U CAN USE DEFAULT_WHERE OR U CAN CREATE A LOV
TRY TO DO THAT AND TELL ME

to make it user friendly because the user may remember part of name , using LOV to search its a good point but the user has to look all names which is hard when we have big number . if the user enter wrong name then ur right the system will tell him that no match .
thanks

الاخ الكريم

DECLARE
len number ;--:= length(:TEXT_ITEM15);
CURSOR SEARCH IS
select worker_id,WNAME
from workers where UPPER(wname)=upper(:TEXT_ITEM15);--upper(substr(wname,0,len-1)) =upper(:TEXT_ITEM15);
begin if ( ( :TEXT_ITEM15 is not null ) ) then
go_block('workers');--please take the correct block that has the result search i write this block as i mean it
open SEARCH;
loop
fetch Search into :worker_id ,:wname ;
NEXT_RECORD;
exit when SEARCH%notfound;
END LOOP;
END IF;
End; 
first_record;

the second I think This is the true

begin select descreption -- but where this column I guess this in the workers table so try
into :TEXT_ITEM11 from departments,workers
where worker_id =:worker_id and workers.dept_id =:departments.dept_id;

select fax ,phone,email,address
into :TEXT_ITEM9,:TEXT_ITEM10,:TEXT_ITEM12,:TEXT_ITEM13 from contact_info
where worker_id=:woker_id ;
end;

[يسار][/يسار]thanks ,ill try it . but i have Q why did u remove substr i suppose the user will not know the whole name he is just remember that worker's name begin with moh or ...etc . i think my code is wrong but thats my idea i want the user to enter any sub string and the system will return all workers names begin with or include that substring but my code can just return the worker's names begin with the entered substr .beside in the next code descreption belongs to departments . the data block connected with workers table only but i added other text items from other tables so when the user enter sub string the id and name will be returned by the first code and then i put trigger to the name when validate item that execute the next one .
many thanks

Dear Friend;

try to use the following code:

GO_BLOCK('ITEMS');
set_block_property('ITEMS',default_where,'upper(CODE_name)  like '''||'%'|| upper(:control.ITEM_NAME_LIST)|| '%''');
EXECUTE_QUERY(NO_COMMIT);
go_item('CONTROL.ITEM_NAME_LIST');

Forget to tell u to add the previous code to list item and use the trigger(when-list-changed)...

DEAR BROTHER
IN MY OPENION...USE A DEFAULT_WHERE ITS AN EASY WAY AND GIVES U A GOOD RESULT

AND ITS NOT TAKE A HEAVY LOAD LIKE WHEN U WRITE CODE WITH LOOPS