| ORA-01001 in WHEN-VALIDATE-ITEM PLEASE HELP [message #80590] |
Thu, 17 October 2002 12:13  |
Nina
Messages: 113
Registered: March 2000
|
Senior Member |
|
|
I have a WHEN-VALIDATE-ITEM trigger on one of my items. I need to have 3 conditions in the code of this trigger:
a) The field can be NULL
b) Since I have LOV on this item, I need to validate
what the user eneters and if it does not correlate
to the list, raise error.
c) If the value is validated - move on.
Here is the code of my trigger, I have no idea why I keep getting ORA-01001 on it:
BEGIN
DECLARE
ISO_DESC VARCHAR2(1);
CURSOR PTI_CURSOR IS
SELECT 'Y'
FROM GTVSCOD
WHERE GTVSCOD_CODE = :STVNATN_SCOD_CODE_ISO ;
BEGIN
OPEN PTI_CURSOR ;
FETCH PTI_CURSOR INTO ISO_DESC ;
IF :STVNATN_SCOD_CODE_ISO IS NULL THEN
CLOSE PTI_CURSOR;
ELSE
IF PTI_CURSOR%NOTFOUND THEN
MESSAGE('*ERROR* ISO Standard Code is Invalid; LIST is Available.');
CLOSE PTI_CURSOR;
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
CLOSE PTI_CURSOR;
END ;
--
:GLOBAL.QUERY_MODE := '0' ;
EXCEPTION
WHEN FORM_TRIGGER_FAILURE THEN
:GLOBAL.QUERY_MODE := '0' ;
RAISE FORM_TRIGGER_FAILURE ;
END ;
|
|
|
|
| Re: ORA-01001 in WHEN-VALIDATE-ITEM PLEASE HELP [message #80591 is a reply to message #80590] |
Thu, 17 October 2002 16:33  |
Susane
Messages: 27
Registered: September 2002
|
Junior Member |
|
|
hi!
I understand your problem. I have a suggestion in problems in your trigger:
1. Change your trigger to KEY-NEXT-ITEM
2. In your trigger try this validation
BEGIN
IF :STVNATN_SCOD_CODE_ISO IS NOT NULL THEN
SELECT 'Y'
FROM GTVSCOD
WHERE GTVSCOD_CODE = :STVNATN_SCOD_CODE_ISO;
IF SQL%FOUND THEN
NEXT_ITEM; (TO MOVE ON THE NEXT ITEM)
END IF;
ELSE
NEXT_ITEM;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF NOT SHOW_LOV('name of lov') THEN --(THIS RAISE YOUR LOV
MESSAGE('*ERROR*ISO STANDARD CODE IS INVALID, LIST IS AVAILABLE')
RAISE FORM_TRIGGER_FAILURE;
ELSE
NEXT_ITEM;
END IF;
END;
TRY THIS CODE AND I SURE IT CAN WILL HELPS YOU. YOU DONT NEED TO USE CURSOR... I HOPE THIS WILL HELP YOU...
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01001 in WHEN-VALIDATE-ITEM PLEASE HELP [message #238884 is a reply to message #238697] |
Sun, 20 May 2007 16:20 |
mokhtar
Messages: 2
Registered: May 2007
|
Junior Member |
|
|
j'en suis sur, cette erreur peut être générer qd on ferme le curseur avant une instruction de boucle. g vécu ça. mais peut être ce code 'ORA-01001' est relatif aux deux types d'erreurs.
djmartin (rough trans.) - I think this error maybe generated when closing the curser before a command to loop. ??? [some IM speak in French] maybe the code 'ORA-01001' covers two types of error.
[Updated on: Tue, 22 May 2007 23:46] by Moderator Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
