| Post change trigger in oracle forms [message #557413] |
Tue, 12 June 2012 09:53  |
 |
megha2525
Messages: 62
Registered: June 2012
Location: columbus
|
Member |
|
|
Hi All ,
I have an issue with the oracle forms trigger. This is the scenario. The form is a query only form . A user can only query on this form . No update or insert is allowed.
The base table say TABLE1 consists of these fields. 1) pro id, pro item, empno , fname, lname, deptno .
The user can query on all items except the fname and lname.
The issue is few records in the TABLE1 have null values for the empno.
When the user queries on proid, pro item , empno or deptno - when the empno on TABLE1 is not NULL , the fname and lname should be fetched from a different table say TABLE2.
otherwise, if the empno on TABLE1 is null , the fname and lname from the same table i.e, TABLE1 are retrieved.
I have tried with the post change trigger on empno . Also tried the When validate item trigger . But it doesnt work the way it is expected to.
Please let me know what is the appropriate trigger that must be used.
Thanks in advance
Megha.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Post change trigger in oracle forms [message #557446 is a reply to message #557442] |
Tue, 12 June 2012 14:27  |
|
megha2525
Messages: 62
Registered: June 2012
Location: columbus
|
Member |
|
|
I have the below code in the post query trigger at the datablock level . The name of the data block is pro.
declare
v_enum varchar2(30);
vfname VARCHAR2 (20);
vlname VARCHAR2 (20);
begin
select tab1.empno into v_empno from table1 tab1 where tab1.empno = :pro.empno;
if v_empno is null then
select first_name into vfname from table2 tab2 where
tab2.empno=v_empno;
select last_name into vlname from table2 tab2 where
tab2.empno=v_empno;
else
select first_name into vfname from table1 tab1 where
tab1.empno=v_empno;
select last_name into vlname from table1 tab1 where
tab1.empno=v_empno;
:pro.fname := vfname;
:pro.lname := vlname;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
vfname := Null;
vlname := Null;
END;
[Updated on: Tue, 12 June 2012 14:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Post change trigger in oracle forms [message #557450 is a reply to message #557449] |
Tue, 12 June 2012 15:09 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If that exception handler didn't catch it then that would be a bug in forms that you would need oracle to fix.
So the no_data_found error must be coming from a different trigger.
That said the posted code is obviously wrong. How do you think this bit could ever work:
if v_empno is null then
select first_name into vfname from table2 tab2 where
tab2.empno=v_empno;
select last_name into vlname from table2 tab2 where
tab2.empno=v_empno;
If v_empno is null then the first select will raise a no_data_found and the 2nd will never be executed.
|
|
|
|
|
|
| Re: Post change trigger in oracle forms [message #557507 is a reply to message #557451] |
Wed, 13 June 2012 07:04 |
sandeepgujje
Messages: 28
Registered: January 2012
Location: India
|
Junior Member |
|
|
Hi
you said -
"When the user queries on proid, pro item , empno or deptno - when the empno on TABLE1 is not NULL , the fname and lname should be fetched from a different table say TABLE2.
otherwise, if the empno on TABLE1 is null , the fname and lname from the same table i.e, TABLE1 are retrieved."
as you are writing the code in POST_QUERY
you can directly make use of the fields to get the result
BEGIN
IF :PRO.EMPNO IS NOT NULL THEN
SELECT first_name, last_name
INTO :PRO.FNAME, :PRO.LNAME
FROM TABLE2
WHERE EMPNO = :PRO.EMPNO;
ELSE
SELECT first_name, last_name
INTO :PRO.FNAME, :PRO.LNAME
FROM TABLE1
WHERE ROWID = :ROWID;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:PRO_FNAME := NULL;
:PRO_LNAME := NULL;
END;
|
|
|
|
|
|
|
|
| Re: Post change trigger in oracle forms [message #557516 is a reply to message #557511] |
Wed, 13 June 2012 07:37 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Supply the block name for rowid - you should always put block names when refering to items in forms.
However I'd skip that select entirely.
Have 2 items in the block based on fname and lname. Don't put them on the canvas.
Have 2 non-database items that will display fname and lname. Put them on the canvas.
In post-query if empno is not null use a select to populate the 2 non-database items as above.
If empno is null assign the 2 non-database items to the values of the fname and lname database items directly:
BEGIN
IF :PRO.EMPNO IS NOT NULL THEN
SELECT first_name, last_name
INTO :PRO.l_FNAME, :PRO.l_LNAME
FROM TABLE2
WHERE EMPNO = :PRO.EMPNO;
ELSE
:pro.l_fname := :PRO.FNAME;
:pro.l_fname := :PRO.LNAME;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
