Home » Developer & Programmer » Forms » Exceptional Error in Post Insert Trigger (Form6i and Oracle8i)
Exceptional Error in Post Insert Trigger [message #436820] Mon, 28 December 2009 12:27 Go to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Dear fellows,

I have a master detail form to enter purchases of raw material. I also need to insert some record into another master detail tables. I have written the following code in Pre-insert trigger of the master block to insert the primary key into another master table named vrh.
begin
select lpad(to_char(nvl(max(vhno),0)+1),4,0) into :invh.vhno from vrh
where vhtype = :invh.invtype
and to_char(vhdate,'MM-YYYY') = to_char(:invh.dated,'MM-YYYY');

--to insert voucher header
insert into vrh
values(:invh.invtype,:invh.dated,:invh.vhno);
end;

and have written the following code in Post-insert trigger of the detail block to insert some record into another detail table alongwith foreign key of the above primary key.
begin
  --Dr Raw Material Stock a/c for inward of material into vrd table
	insert into vrd(vdtype,vddate,vdvhno,vdsno,vdlcode,vdmcode,vdwcode,vdparticular,vddr,vdcr,invhno,invdno)
	values(:invh.invtype,:invh.dated,:invh.vhno,vrd_snoseq.nextval,:invd.rmlcode,:invd.rmmcode,:invd.rmwcode,
   'Inward '||:invd.itemcode||'-'||:invd.grpicode||'-'||:invd.qtyin||' '||:invd.umid||
   ' @'||:invd.rate,:invd.amount,0,invd.invhno,:invd.invdno);
end;

After entering the data into the form when i press save button or say committing the same the form display POST-INSERT trigger raised unhandle exceptional ORA-00984.
I have only 3 days to install this inventory system on trial basis to the client but i stuck off by this error. Could any expert help me in this regard. I am verty thankfull for spenting your precious time to read and solve my problem.

Muhammad Khalil
[EDITED by DJM: please format your code]

[Updated on: Tue, 05 January 2010 23:00] by Moderator

Report message to a moderator

icon6.gif  Re: Exceptional Error in Post Insert Trigger [message #436823 is a reply to message #436820] Mon, 28 December 2009 12:46 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
You would need to post the trigger(s) so we can know where the :invh and :invd variables come from?
Confused

Re: Exceptional Error in Post Insert Trigger [message #436829 is a reply to message #436820] Mon, 28 December 2009 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you kept your lines in 80 characters instead of 250 I'd read your question.

Regards
Michel
Re: Exceptional Error in Post Insert Trigger [message #436849 is a reply to message #436820] Tue, 29 December 2009 00:05 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Ok I again try to adjust my question to 80 character as desired.
I have a master detail form to enter purchases of raw material.
I also need to insert some record into another master detail tables.
I have written the following code in Pre-insert trigger of the
master block to insert the primary key into another master table named vrh.
begin
select lpad(to_char(nvl(max(vhno),0)+1),4,0) into :invh.vhno from vrh
where vhtype = :invh.invtype
and to_char(vhdate,'MM-YYYY') = to_char(:invh.dated,'MM-YYYY');

--to insert voucher header
insert into vrh
values(:invh.invtype,:invh.dated,:invh.vhno);
end;

and have written the following code in Post-insert trigger of
the detail block to insert some record into another detail
table alongwith foreign key of the above primary key.
begin
  --Dr Raw Material Stock a/c for inward of material into vrd table
	insert into vrd(vdtype,vddate,vdvhno,vdsno,vdlcode,
vdmcode,vdwcode,vdparticular,vddr,vdcr,invhno,invdno)

values(:invh.invtype,:invh.dated,:invh.vhno,vrd_snoseq.nextval,
:invd.rmlcode,:invd.rmmcode,:invd.rmwcode,'Inward '||
:invd.itemcode||'-'||:invd.grpicode||'-'||:invd.qtyin||
' '||:invd.umid||' @'||:invd.rate,:invd.amount,
0,invd.invhno,:invd.invdno);
end;

After entering the data into the form when i press save button
or say committing the same the form display POST-INSERT trigger
raised unhandle exceptional ORA-00984.
I have only 3 days to install this inventory system on trial
basis to the client but i stuck off by this error. Could any
expert help me in this regard. I am verty thankfull for spenting
your precious time to read and solve my problem.

Muhammad Khalil
Re: Exceptional Error in Post Insert Trigger [message #436854 is a reply to message #436849] Tue, 29 December 2009 00:39 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should consider purchasing (or using one of free) code formatter. Your style reminds me of one of my former colleagues who didn't care about formatting at all. His programs are a complete nightmare (when debugging comes into question).

The same goes with you; if you'd written it in a nice manner, you'd probably see that colon sign (:) is missing at the bottom of the VALUES list:
values
(:invh.invtype,
 :invh.dated,
 :invh.vhno,
 vrd_snoseq.nextval,
 :invd.rmlcode,
 :invd.rmmcode,
 :invd.rmwcode,
 'Inward ' || :invd.itemcode ||'-'|| :invd.grpicode ||'-'
    || :invd.qtyin ||' '|| :invd.umid ||' @'|| :invd.rate,
 :invd.amount,
 0,
 invd.invhno,           --> here!
 :invd.invdno
);
Re: Exceptional Error in Post Insert Trigger [message #436856 is a reply to message #436823] Tue, 29 December 2009 00:42 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LKBrwn_DBA wrote on Mon, 28 December 2009 19:46
You would need to post the trigger(s) so we can know where the :invh and :invd variables come from?

Colon sign (in Forms) in front of a "name" (a variable, as you've put it) represents block name. For example, ":invh.dated" means that there is an item called "dated" that belongs to the "invh" data block.
icon10.gif  Re: Exceptional Error in Post Insert Trigger [message #436898 is a reply to message #436856] Tue, 29 December 2009 07:21 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Littlefoot wrote on Tue, 29 December 2009 01:42
Colon sign (in Forms) in front of a "name" (a variable, as you've put it) represents block name. For example, ":invh.dated" means that there is an item called "dated" that belongs to the "invh" data block.


Ooooh, thanks Littlefoot. It's been a very very long time since I dealt with "Forms".
Smile
Re: Exceptional Error in Post Insert Trigger [message #437017 is a reply to message #436898] Wed, 30 December 2009 05:43 Go to previous message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks Littlefoot for pointing out my mistake. Really i have checked several time my code but it skip all the time perhaps due to tension. Now my problem have been solved. Thanks a lot once again.

Muhammad Khalil
Previous Topic: :global variable in forms 10g
Next Topic: Implementation Restriction 'DBMS_LOB.READWRITE' Cannot directly access remote package variable or cu
Goto Forum:
  


Current Time: Sun Dec 04 10:31:24 CST 2016

Total time taken to generate the page: 0.10238 seconds