FRM-40733: PL/SQL built-in DBMS_ERROR_CODE failed [message #509472] |
Sat, 28 May 2011 03:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c83479f767407b5111c13147bb210d82?s=64&d=mm&r=g) |
mbzhackers
Messages: 4 Registered: May 2011 Location: Tunisia
|
Junior Member |
|
|
Hello,
I have a client who is facing the above error. Everything used to work perfectly but since a week, he gets this error message on a specific FORM.
No updates have been made to the FORM.
The FMXs are stored on the database server(10G R3, Windows 2003 R2) and the Client terminals access them through a network drive.
When executing the conflicting FORM on the server, no error.
I call the DBMS_ERROR_CODE routine from a FORM-LEVEL ON-ERROR trigger :
DECLARE
ERR NUMBER;
BEGIN
IF DBMS_ERROR_CODE=-1 THEN
MSG('Client existant',ERR,'E');
ELSE
message(error_type||'-'||error_code||' : '||error_text);
END IF;
RAISE FORM_TRIGGER_FAILURE;
END;
Code for procedure MSG :
PROCEDURE MSG(TXT_MSG IN VARCHAR2 , RET_REP OUT NUMBER , TYPE_MSG IN CHAR DEFAULT 'I' ,
LABEL_BUT1 IN VARCHAR2 DEFAULT 'OK' , LABEL_BUT2 IN VARCHAR2 DEFAULT NULL ,
LABEL_BUT3 IN VARCHAR2 DEFAULT NULL) IS
VL_ALERT ALERT;
BEGIN
IF upper(TYPE_MSG) = 'E' THEN
VL_ALERT:=FIND_ALERT('ERROR');
ELSIF upper(TYPE_MSG) = 'W' THEN
VL_ALERT:=FIND_ALERT('WARNING');
ELSIF upper(TYPE_MSG) = 'I' THEN
VL_ALERT:=FIND_ALERT('INFO');
END IF;
SET_ALERT_BUTTON_PROPERTY(VL_ALERT,ALERT_BUTTON1,LABEL,LABEL_BUT1);
SET_ALERT_BUTTON_PROPERTY(VL_ALERT,ALERT_BUTTON2,LABEL,LABEL_BUT2);
SET_ALERT_BUTTON_PROPERTY(VL_ALERT,ALERT_BUTTON3,LABEL,LABEL_BUT3);
SET_ALERT_PROPERTY(VL_ALERT,ALERT_MESSAGE_TEXT,TXT_MSG);
RET_REP:=SHOW_ALERT(VL_ALERT);
END;
Could you help me please ?
|
|
|
|
|
Re: FRM-40733: PL/SQL built-in DBMS_ERROR_CODE failed [message #509482 is a reply to message #509478] |
Sat, 28 May 2011 05:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Only reason I can think of for that builtin to fail is if the error that caused the on-error trigger to fire also resulted in the form losing it's DB session.
So if a recompile doesn't work you may have to debug the form and work out exactly what is causing the original error.
|
|
|
|
Re: FRM-40733: PL/SQL built-in DBMS_ERROR_CODE failed [message #510479 is a reply to message #509472] |
Mon, 06 June 2011 03:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c83479f767407b5111c13147bb210d82?s=64&d=mm&r=g) |
mbzhackers
Messages: 4 Registered: May 2011 Location: Tunisia
|
Junior Member |
|
|
Hello,
After the recompile, nothing changed.
As mentioned, I removed the ON-ERROR trigger.
When commiting, he gets FRM-40508: ORACLE error: unable ton INSERT record. When clicking Help ==> Display error, he gets FRM-42100: No errors encountred recently.
Furthermore, I made the DATA BLOCK not linked the DB and added this Key-Commit trigger :
DECLARE
ERR NUMBER;
BEGIN
INSERT INTO CLIENTS(COD_DOUANE,NOM_PRE_CLT,ADR1)
VALUES (:CLIENTS.COD_DOUANE,:CLIENTS.NOM_PRE_CLT,:CLIENTS.ADR1);
STANDARD.COMMIT;
MSG('Insertion effectuée avec succès',ERR,'I');
EXCEPTION
WHEN OTHERS THEN
MSG(SQLERRM,ERR,'E');
END;
Now, when commiting, he gets ORA-01009: missing mandatory parameter.
This is structure of table CLIENTS :
TABLE clients
Name Null? Type
----------------------------------------- -------- ----------------------------
COD_DOUANE NOT NULL VARCHAR2(8)
NOM_PRE_CLT VARCHAR2(175)
RAI_SOC_CLT VARCHAR2(175)
ADR1 VARCHAR2(70)
ADR2 VARCHAR2(70)
ADR3 VARCHAR2(70)
COMMISSION NUMBER(15,3)
TAUX_TVA NUMBER(22,2)
PDL NUMBER(22,3)
I forgot to mention in my first post that this problem occurs only with my client. For me, everything works OK and I work on a dump of his database.
|
|
|
|