OraFAQ Forum: Forms » Post-INSERT TRIGGER raised unhandled error

Home » Developer & Programmer » Forms » Post-INSERT TRIGGER raised unhandled error (Oracle 9i, xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Post-INSERT TRIGGER raised unhandled error [message #493619]

Wed, 09 February 2011 01:56

naveed35
Messages: 5
Registered: March 2010

Junior Member

I am facing a post trigger problem.
No error while compiling.
please check coding and help

DECLARE
mYPACCT VARCHAR2(10);
mTRNNO NUMBER; mVNO NUMBER; mSR NUMBER; mNARRATION VARCHAR2(200); mCHCODE VARCHAR2(10); mPARTY VARCHAR2(200);
mBRAND VARCHAR2(200); mYARN VARCHAR2(200);
BEGIN

BEGIN
SELECT YPACCT INTO mYPACCT FROM CONFIG;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;

IF nvl(:YPURCH.JTRNNO,0) = 0 THEN

BEGIN
SELECT NVL(MAX(NVL(TRNNO,0)),0) + 1 INTO mTRNNO FROM JMST ;
EXCEPTION WHEN NO_DATA_FOUND THEN mTRNNO := 1;
END;

BEGIN
SELECT NVL(MAX(NVL(VOUCHNO,0)),0) + 1 INTO mVNO FROM JMST
WHERE VTYPE = 'JY';
EXCEPTION WHEN NO_DATA_FOUND THEN mVNO := 1;
END;

UPDATE YPURCH SET JTRNNO = mTRNNO WHERE TRNNO = :YPURCH.TRNNO;

ELSE

mTRNNO := :YPURCH.JTRNNO;

BEGIN
SELECT VOUCHNO INTO mVNO FROM JMST WHERE TRNNO = mTRNNO;
EXCEPTION WHEN NO_DATA_FOUND THEN
BEGIN
SELECT NVL(MAX(NVL(VOUCHNO,0)),0) + 1 INTO mVNO FROM JMST
WHERE TRNDATE = :PDATE AND VTYPE = 'JY';
EXCEPTION WHEN NO_DATA_FOUND THEN mVNO := 1;
END;
END;
DELETE JDTL WHERE TRNNO = :YPURCH.JTRNNO;
DELETE JMST WHERE TRNNO = :YPURCH.JTRNNO;
END IF; -- JTRNNO IS NULL

INSERT INTO JMST (TRNNO,TRNDATE, VTYPE, VOUCHNO)
VALUES (mTRNNO,:PDATE,'JY',mVNO);

BEGIN
SELECT B.YBDESC,YCOUNT || ' ' ||
BLEND || ' ' || RATIO || ':'|| YSHADE
INTO mBRAND ,mYARN
FROM YPCONT,YBRAND B, YARN
WHERE ypcont.YPCONTNO = :YPURCH.CONTNO AND YARN.CODE = YPCONT.YCODE
AND b.YBCODE = YPCONT.Brand;
EXCEPTION WHEN NO_DATA_FOUND THEN mBRAND := 'NIL';
END;
begin
select chart.chcode,chart.chdesc into mchcode,mparty from chart,ypcont
where ypcont.ypcontno = :ypurch.contno and chart.chcode = ypcont.ycode;
exception when no_data_found then null;
end;
mNARRATION := :PBAG || ' BAGS @ ' || :RATE || ' ' || mBRAND || ' ' || mYARN ;
BEGIN
SELECT NVL(MAX(NVL(SR,0)),0) + 1 INTO mSR FROM JDTL WHERE TRNNO = mTRNNO;
EXCEPTION WHEN NO_DATA_FOUND THEN mSR := 1;
END;
--DR TO YP ACCT
INSERT INTO JDTL (TRNNO,SR,CHCODE,NARRATION,DR,CR)
VALUES (mTRNNO, mSR, mYPACCT, mPARTY || ' ' || mNARRATION, :PVALUE,0);
BEGIN
SELECT NVL(MAX(NVL(SR,0)),0) + 1 INTO mSR FROM JDTL WHERE TRNNO = mTRNNO;
EXCEPTION WHEN NO_DATA_FOUND THEN mSR := 1;
END;
--CR TO PARTY
INSERT INTO JDTL (TRNNO,SR,CHCODE,NARRATION,DR,CR)
VALUES (mTRNNO, mSR, mCHCODE, mNARRATION, 0,:PVALUE);
END;

Thanks!

Report message to a moderator

Re: Post-INSERT TRIGGER raised unhandled error [message #493622 is a reply to message #493619]

Wed, 09 February 2011 02:12

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Good luck with debugging! If you only told us what error it was, it would be easier for all of us.

With the information you posted, well, if it is unhandled, then handle it, either by fixing the code (if it is wrong), or - if such an error is expected - by creating the EXCEPTION handler.

Report message to a moderator

Re: Post-INSERT TRIGGER raised unhandled error [message #493623 is a reply to message #493619]

Wed, 09 February 2011 02:18

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) Can you please read and follow the orafaq forum guide especially the part about formatting your post.
2) There are tens of thousands of possible unhandled errors. Unless you tell us the actual error number there is nothing we can do.

Report message to a moderator

Re: Post-INSERT TRIGGER raised unhandled error [message #493634 is a reply to message #493623]

Wed, 09 February 2011 03:00

naveed35
Messages: 5
Registered: March 2010

Junior Member

Ok thanks for your reply

it is fetching multiple rows
i m trying to fix it Very Happy

Report message to a moderator

Re: Post-INSERT TRIGGER raised unhandled error [message #493643 is a reply to message #493634]

Wed, 09 February 2011 03:49

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You supposed that there'll be NO-DATA-FOUND. At least one of SELECTs returns more than a single record.

What is your Forms version? If 10g, run it through debugger. If 6i, include MESSAGE built-ins so that you could trace code execution. If other, which one?

Report message to a moderator

Re: Post-INSERT TRIGGER raised unhandled error [message #493654 is a reply to message #493643]

Wed, 09 February 2011 04:28

naveed35
Messages: 5
Registered: March 2010

Junior Member

i m using 6i

code was mentioned above
if there is way to find problem then please tell me

Thanks!

Report message to a moderator

Re: Post-INSERT TRIGGER raised unhandled error [message #493655 is a reply to message #493654]

Wed, 09 February 2011 04:30

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Use message - place a messgae with a different number at various points in the code. Run the form. See which message you got last before the error. Use that to work out which statement raised the error.

Report message to a moderator

Re: Post-INSERT TRIGGER raised unhandled error [message #493677 is a reply to message #493655]

Wed, 09 February 2011 05:57

naveed35
Messages: 5
Registered: March 2010

Junior Member

Thanks
specially to littlefoot and cookiemonster
My problem has been solved Smile

[Updated on: Wed, 09 February 2011 05:58]

Report message to a moderator

Previous Topic:	ON-insert vs Commit
Next Topic:	Installed Forms 10g, not able to run form

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 16:28:18 CDT 2024