Home » Developer & Programmer » Forms » Post change trigger in oracle forms (Oracle forms 10g )
Post change trigger in oracle forms [message #557413] Tue, 12 June 2012 09:53 Go to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior 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 #557422 is a reply to message #557413] Tue, 12 June 2012 12:33 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
I'd create a view of the two tables and put the logic on which table the column would appear from then base the form on the view.
Re: Post change trigger in oracle forms [message #557423 is a reply to message #557422] Tue, 12 June 2012 12:40 Go to previous messageGo to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior Member
Hi joy_division

Thank you for the reply. But I am not allowed to create a view.

Is there any other way of doing this ?

Thanks
Megha
Re: Post change trigger in oracle forms [message #557426 is a reply to message #557423] Tue, 12 June 2012 12:58 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sounds like a job for post-query.
Re: Post change trigger in oracle forms [message #557430 is a reply to message #557426] Tue, 12 June 2012 13:29 Go to previous messageGo to next message
Littlefoot
Messages: 18828
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I agree, POST-QUERY.

POST-CHANGE shouldn't be used; it is here for backward compatibility (back to Forms 3.0 ancient times). WHEN-VALIDATE-ITEM is OK, but only if you'd be allowed to insert or update records. In query only mode, it is useless.
Re: Post change trigger in oracle forms [message #557434 is a reply to message #557430] Tue, 12 June 2012 13:42 Go to previous messageGo to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior Member
Hi All ,

I tried the post query trigger at the block level . Even before I could enter the query mode, I get this screen error FRM:40735 POST-QUERY trigger raised unhandled exception ORA-01403.
I also have an exception block to handle this in the trigger.
Not sure why I get this error .

Thanks
Megha
Re: Post change trigger in oracle forms [message #557435 is a reply to message #557434] Tue, 12 June 2012 13:45 Go to previous messageGo to next message
Littlefoot
Messages: 18828
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you don't know it (having the code), how do you expect anyone of us to know it (without even seeing what you wrote)?
Re: Post change trigger in oracle forms [message #557436 is a reply to message #557434] Tue, 12 June 2012 13:45 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
1403 is no data found.
You've got a select that finds no records.
Re: Post change trigger in oracle forms [message #557438 is a reply to message #557435] Tue, 12 June 2012 13:59 Go to previous messageGo to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior Member
I meant I had an exception ( WHEN NO DATA FOUND ) and I still get this error .
The code first fetches the empno from TABLE1 and checks to see if it is null .
if not null , fetches the data from table2.
if null , from table1.
Even before I can enter the query mode , I get this post query trigger . So wasnt sure of that part.
I also have a package at the database level that does the same logic of fetching the fname and lname from the relevant tables based on the empno.
I also tried to use the package in the post query trigger but no luck .

Thanks
Megha.
Re: Post change trigger in oracle forms [message #557442 is a reply to message #557438] Tue, 12 June 2012 14:06 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you've coded it wrong.
If you want us to tell you how it's wrong then you'll have to post the code here.
Re: Post change trigger in oracle forms [message #557446 is a reply to message #557442] Tue, 12 June 2012 14:27 Go to previous messageGo to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior 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 #557447 is a reply to message #557446] Tue, 12 June 2012 14:31 Go to previous messageGo to next message
Littlefoot
Messages: 18828
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Even before I can enter the query mode , I get this post query trigger

Is there, perhaps, EXECUTE_QUERY in WHEN-NEW-FORM-INSTANCE trigger?

Is this POST-QUERY trigger the only POST-QUERY trigger in your form?

Maybe you should run the form in debug mode. Step by step, you'd relatively quickly & easily spot the error cause.
Re: Post change trigger in oracle forms [message #557448 is a reply to message #557447] Tue, 12 June 2012 14:42 Go to previous messageGo to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior Member
yes , there is an execute_query in the WHEN-NEW-FORM-INSTANCE at the form level .
There is no other post query trigger other than the one I mentioned.

Thanks
Megha

[Updated on: Tue, 12 June 2012 14:42]

Report message to a moderator

Re: Post change trigger in oracle forms [message #557449 is a reply to message #557448] Tue, 12 June 2012 14:52 Go to previous messageGo to next message
Littlefoot
Messages: 18828
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, if you don't need it, remove EXECUTE_QUERY from WHEN-NEW-FORM-INSTANCE trigger (as you said that it fired although you didn't even enter query mode nor executed query manually).

As of the error ... I don't know. EXCEPTION section *should have* captured it ...

Did you run the form in debug mode? I believe you should.
Re: Post change trigger in oracle forms [message #557450 is a reply to message #557449] Tue, 12 June 2012 15:09 Go to previous messageGo to next message
cookiemonster
Messages: 10573
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 #557451 is a reply to message #557450] Tue, 12 June 2012 15:11 Go to previous messageGo to next message
Littlefoot
Messages: 18828
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, make it shorter:
  select first_name,
         last_name
    into vfname,
         vlname
    from table2
    where empno = v_empno;
Re: Post change trigger in oracle forms [message #557507 is a reply to message #557451] Wed, 13 June 2012 07:04 Go to previous messageGo to next message
sandeepgujje
Messages: 23
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 #557511 is a reply to message #557507] Wed, 13 June 2012 07:28 Go to previous messageGo to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior Member
Hi Sandeep ,
Thank you for the reply.
I tried your code in the post query trigger and get this error .

FRM-32087: Bind Variable :ROWID binds to more than one object.
FRM-30085: Unable to adjust form for output.

Thanks
Megha.
Re: Post change trigger in oracle forms [message #557515 is a reply to message #557511] Wed, 13 June 2012 07:37 Go to previous messageGo to next message
sandeepgujje
Messages: 23
Registered: January 2012
Location: India
Junior Member
can you please use the :PRO.ROWID instead of just :ROWID

hope this helps..
Re: Post change trigger in oracle forms [message #557516 is a reply to message #557511] Wed, 13 June 2012 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 10573
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;
Re: Post change trigger in oracle forms [message #557519 is a reply to message #557516] Wed, 13 June 2012 07:40 Go to previous messageGo to next message
sandeepgujje
Messages: 23
Registered: January 2012
Location: India
Junior Member
yes exactly... as cookiemonster suggested, we can reduce one select query, which improves performance...
Re: Post change trigger in oracle forms [message #557760 is a reply to message #557519] Fri, 15 June 2012 09:43 Go to previous messageGo to next message
megha2525
Messages: 22
Registered: June 2012
Location: columbus
Junior Member
Hi All ,

I finally got this to work using the FROM clause query for the Query Data Source Type that on the Data block properties .

Thanks
Megha
Re: Post change trigger in oracle forms [message #570997 is a reply to message #557760] Mon, 19 November 2012 21:46 Go to previous message
D_ORA
Messages: 32
Registered: November 2012
Location: UK
Member
Hi,It will be helpful for other as well if you describe your solution in detail.
Thanks
Previous Topic: show_view is not working
Next Topic: vista 64bit and 6i forms & forms 6i on vista64 (merged)
Goto Forum:
  


Current Time: Sat Apr 19 05:57:23 CDT 2014

Total time taken to generate the page: 0.08431 seconds