raise_application_error's Question in Trigger ? 1

[saustin]

Hi,
Have read the Oracle oracle8/server.815/a67842/06_errs.htm documentation on raising exceptions but am curious. Can you put in ANY text you want when raising your error ?

In a trigger for example is ...raise_application_error(-20999,'VMFG-32092 Check with Purchasing Exceeds Early Need Date');
just as valid as .... raise_application_error(-20999,'VMFG-32092 Check with purchasing');

Am not sure what table these error's are stored in(or if they need to be). Think they don't.

Am getting an SQL Error 25530, not found in ERROR.SQL file.
error in my application and was wondering if the exact raise_application_error message needed to be the ERROR.SQL file.

Thanks, Steve.

 

[SantaMufasa]

Steve,

Yes, you can even code profanity in your user-defined error messages. No, there is no data-dictionary table or file in which you store error messages. (I personally reuse error number "-20000".)

Post your exact code that is throwing the error (from "DECLARE" to "END;") so we can help troubleshoot your issue.

santa]Mufasa
aka Dave of Sandy, Utah, USA @ 19:14 (30Aug04) UTC (aka "GMT" and "Zulu"), 12:14 (30Aug04) Mountain Time)

 

[saustin]

Hi Dave,
Many thanks for the reply. Here is the code.
CREATE OR REPLACE TRIGGER "SYSADM"."TBI_EARLY_RECEIPT_WARNING"
BEFORE INSERT
ON "RECEIVER_LINE" REFERENCING OLD AS OLDDATA NEW AS NEWDATA
FOR EACH ROW
DECLARE mDate SYSADM.PURC_ORDER_LINE.PROMISE_DATE%type;

BEGIN

SELECT
SYSADM.PURC_ORDER_LINE.PROMISE_DATE
INTO mDate
FROM SYSADM.PURC_ORDER_LINE
WHERE :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID;
IF mDate > (SYSDATE + 30) THEN

INSERT INTO BARCODERECEIVER_LINE
(RECEIVER_ID, LINE_NO, PRINTED, QTY, PO, VENDOR, DESCRIPTION, PART, RECEIVED_DATE, PRINTER,EARLY)
SELECT
:NEWDATA.RECEIVER_ID,
:NEWDATA.LINE_NO,
'N',
:NEWDATA.RECEIVED_QTY,
:NEWDATA.PURC_ORDER_ID,
SYSADM.VENDOR.NAME,
SYSADM.PART.DESCRIPTION,
NVL(SYSADM.PURC_ORDER_LINE.PART_ID, SYSADM.PURC_ORDER_LINE.VENDOR_PART_ID),
SYSADM.RECEIVER.RECEIVED_DATE,
DECODE(SYSADM.RECEIVER.USER_ID, 'CARL', 1, 'ORLANDO', 2, 'TONYA', 3, 'MATT', 'TOM', 3, 'SYSADM', 3),
'Y'
FROM SYSADM.RECEIVER, SYSADM.PURC_ORDER_LINE, SYSADM.PART, SYSADM.PURCHASE_ORDER, SYSADM.VENDOR
WHERE :NEWDATA.RECEIVER_ID = SYSADM.RECEIVER.ID
AND :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID
AND SYSADM.PURC_ORDER_LINE.PART_ID = SYSADM.PART.ID (+)
AND SYSADM.RECEIVER.PURC_ORDER_ID = SYSADM.PURCHASE_ORDER.ID (+)
AND SYSADM.PURCHASE_ORDER.VENDOR_ID = SYSADM.VENDOR.ID (+)
AND SYSADM.RECEIVER.USER_ID IN ('ORLANDO', 'TONYA', 'MATT', 'TOM', 'SYSADM');
raise_application_error(-20999, 'Check with Purchasing please Exceeds Early Need Date');
END IF;

END;

All of it is working great until the raise error section. From the Oracle documentation...

An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error

Thought that all processing would be halted and rolled back after the raise error was called. What I am attempting to do with this trigger is stop all processing (prevent an insert). This trigger is called by a 3rd party app (that uses a centura runtime). That app now pop's up a SQL Error 24537, not found in ERROR.SQL file.

 

[SantaMufasa]

Steve,

Your code looks fine (although I have not tested it by running it). Before I do any in-depth trouble shooting, let's eliminate "centura" as an issue. Could you please try this test:
1) Connect to SQL*Plus using the username/password appropriate for this trigger.
2) Re-execute the "CREATE TRIGGER..." code, confirming no errors.
3) Attempt an "INSERT INTO RECEIVER_LINE..." that would exercise the logic in your trigger, causing the "RAISE...".

If there are any errors, please post them here.

Let us know the results of your test.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:53 (31Aug04) UTC (aka "GMT" and "Zulu"), 09:53 (31Aug04) Mountain Time)

 

[saustin]

Hi Dave,
Thank you for the debugging tip. Did this with SQL*Plus and here is the result....

SQL> insert into RECEIVER_LINE (PURC_ORDER_ID, RECEIVER_ID, LINE_NO, PURC_ORDER_LINE_NO, USER_RECEIV
ED_QTY) values ('53922', '48745', 1,1,1);
insert into RECEIVER_LINE (PURC_ORDER_ID, RECEIVER_ID, LINE_NO, PURC_ORDER_LINE_NO, USER_RECEIVED_QT
*
ERROR at line 1:
ORA-20000: Check with Purchasing please Exceeds Early Need Date
ORA-06512: at "SYSADM.TBI_EARLY_RECEIPT_WARNING", line 35
ORA-04088: error during execution of trigger 'SYSADM.TBI_EARLY_RECEIPT_WARNING'

The order id in this case 53922, is what I am hoping to have fail on the insert. Is the above error message what one would normally expect ?

Thanks, Steve.

 

[SantaMufasa]

Sam,

Yes, you are getting proper (expected) results from your trigger.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:17 (31Aug04) UTC (aka "GMT" and "Zulu"), 13:17 (31Aug04) Mountain Time)

 

[saustin]

Thanks Dave,
After more debugging am noticing that when the raise error is in the trigger the code above (specifically inserting a record into another table) does NOT work according to plan. The raise error also seems to roll back that save.
The point of this trigger was to prevent an insert into a table but save a record of the attempt in another table for bar code label printing purposes.
Even if the above points are satisfied still have the problem of the sql error in the 3rd party app.

Steve.

 

[saustin]

Hi Dave,
Also added in your suggestion from a prior post to no avail. Think the issue is one of adding data to ANOTHER table if the one the trigger is based on has a raise_error.
Here is the updated trigger. Steve.

DECLARE mDate SYSADM.PURC_ORDER_LINE.PROMISE_DATE%type;

BEGIN

SELECT
SYSADM.PURC_ORDER_LINE.PROMISE_DATE
INTO mDate
FROM SYSADM.PURC_ORDER_LINE
WHERE :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID;
IF mDate > (SYSDATE + 30) THEN

INSERT INTO BARCODERECEIVER_LINE
(RECEIVER_ID, LINE_NO, PRINTED, QTY, PO, VENDOR, DESCRIPTION, PART, RECEIVED_DATE, PRINTER, EARLY, TRANSTIME)
SELECT
:NEWDATA.RECEIVER_ID,
:NEWDATA.LINE_NO,
'N',
:NEWDATA.RECEIVED_QTY,
:NEWDATA.PURC_ORDER_ID,
SYSADM.VENDOR.NAME,
SYSADM.PART.DESCRIPTION,
NVL(SYSADM.PURC_ORDER_LINE.PART_ID, SYSADM.PURC_ORDER_LINE.VENDOR_PART_ID),
SYSADM.RECEIVER.RECEIVED_DATE,
DECODE(SYSADM.RECEIVER.USER_ID, 'CARL', 1, 'ORLANDO', 2, 'TONYA', 3, 'MATT', 'TOM', 3, 'SYSADM', 3),
'Y',
to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')
FROM SYSADM.RECEIVER, SYSADM.PURC_ORDER_LINE, SYSADM.PART, SYSADM.PURCHASE_ORDER, SYSADM.VENDOR
WHERE :NEWDATA.RECEIVER_ID = SYSADM.RECEIVER.ID
AND :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID
AND SYSADM.PURC_ORDER_LINE.PART_ID = SYSADM.PART.ID (+)
AND SYSADM.RECEIVER.PURC_ORDER_ID = SYSADM.PURCHASE_ORDER.ID (+)
AND SYSADM.PURCHASE_ORDER.VENDOR_ID = SYSADM.VENDOR.ID (+)
AND SYSADM.RECEIVER.USER_ID IN ('ORLANDO', 'TONYA', 'MATT', 'TOM', 'SYSADM');

if sql%rowcount > 0 then
raise_application_error(-20000, 'Check with Purchasing please Exceeds Early Need Date');
END IF;
END IF;

END;