APEX

Leh,

Where and when do you get the Unique Constraint error? Do you have form where users enter data, click "Create" button, and you use a process to insert the form data into a table?

Please let us know.

Ravi

I hope this is what your were looking for:

http://apex.oracle.com/pls/otn/f?p=31517:185

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://htmldb.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

Hello,

I have created a constraint on my "proj_resources" table so that a user cannot enter a duplication "person_id" and "project_id" combination. When I click the "Create" button and try to add a record set where the "person_id" / "project_id" constraint is violated, I should (and do) get an error but I would like to show a user-friendly inline message instead.

Here is the process I use to insert the form data:

BEGIN
INSERT INTO PROJ_RESOURCES proj
(proj.resource_id, proj.person_id,proj.project_id,proj.probability_percent,proj.project_bill_rate,proj.tenant_id )
VALUES (:P6_PROJ_RESOURCE_TIME_ID,:P6_PERSON_ID,:P6_PROJECT_ID,:P6_PROBABILITY_PERCENT,:P6_PROJECT_BILL_RATE,:P6_TENANT_ID);

INSERT INTO PROJ_RESOURCE_TIME projt
(projt.resource_id, projt.YEAR, projt.MONTH,projt.TOTAL_WORK_HOURS)
VALUES (:P6_PROJ_RESOURCE_TIME_ID, :P6_CURRENT_YEAR, :P6_CURRENT_MONTH,:P6_CURMOTOT);

INSERT INTO PROJ_RESOURCE_TIME projt
(projt.resource_id, projt.YEAR, projt.MONTH,projt.TOTAL_WORK_HOURS )
VALUES (:P6_PROJ_RESOURCE_TIME_ID, :P6_NEXT_YEAR, :P6_NEXT_MONTH, :P6_NXTMOTOT );
END;

If you could tell me what I need to do that would be great.
Thanks

LEH

Hello Denes,

I went to the page that you sent to. I found the following code:

1. Process on Submit:


DECLARE
number_exists EXCEPTION;
PRAGMA EXCEPTION_INIT (number_exists, -00001);
number_too_big EXCEPTION;
PRAGMA EXCEPTION_INIT (number_too_big, -02290);
BEGIN
INSERT INTO uq
VALUES (:p185_number);
EXCEPTION
WHEN INVALID_NUMBER
THEN
:p185_error :=
'Well, we said that '
|| 'only numbers '
|| 'are allowed!';
WHEN number_exists
THEN
:p185_error := 'Well, this number '
||'can not be inserted twice!';
WHEN number_too_big
THEN
:p185_error :=
'We will accept only one digit numbers. '
||'Unfortunatelly, they all exist already. '
||'Means, you will not be able to enter anything ;)!';
WHEN OTHERS
THEN
:p185_error := SQLERRM;
END;

Where do I use this code? In a page process? In a validation?

Also, I am trying to capture an ORA-00001 Unique Constraint error that occurs when I duplicate a "person_id"/"product_id" combination. I'm not sure this has anything to do with validating a number and it looks like the script you showed me deals with numbers.

Please tell me how to integrate the script into my APEX application.

Thanks

leh

Hi Leh,

Why dont you create a page validation that executes when you press the submit button (Or what ever button you use to save the data).

This validation would simply be a sql expression that checks for the existence of your unique values and errors if they already exist. You can also specify the error message you wish in the validation.

This isn't really capturing the error but checking for the error condition before it tries to do the insert. There is a small chance that the error condition will not be picked up by the validation in the unlikely case that another user manages to insert the same values in the small time between the validation firing and insert happenning, thus you still need the unique key, but on most systems this would be very rare.

Andre

One option is to add page validation alonge with Validation Method = SQL,
and Type of SQL = Not Exists
and code will be

select 'y'
from proj_resources
where person_id = :P6_PERSON_ID
and project_id = :P6_PROJECT_ID;

and error message can be 'User Friendly error message'.

Second option is to handle 'DUP_VAL_ON_INDEX' exception. you can handle this exception in your anonymos block which should be like as:
BEGIN
INSERT INTO PROJ_RESOURCES proj
(proj.resource_id, proj.person_id,proj.project_id,proj.probability_percent,proj.project_bill_rate,proj.tenant_id )
VALUES (:P6_PROJ_RESOURCE_TIME_ID,:P6_PERSON_ID,:P6_PROJECT_ID,:P6_PROBABILITY_PERCENT,:P6_PROJECT_BILL_RATE,:P6_TENANT_ID);

INSERT INTO PROJ_RESOURCE_TIME projt
(projt.resource_id, projt.YEAR, projt.MONTH,projt.TOTAL_WORK_HOURS)
VALUES (:P6_PROJ_RESOURCE_TIME_ID, :P6_CURRENT_YEAR, :P6_CURRENT_MONTH,:P6_CURMOTOT);

INSERT INTO PROJ_RESOURCE_TIME projt
(projt.resource_id, projt.YEAR, projt.MONTH,projt.TOTAL_WORK_HOURS )
VALUES (:P6_PROJ_RESOURCE_TIME_ID, :P6_NEXT_YEAR, :P6_NEXT_MONTH, :P6_NXTMOTOT );
Exception
When DUP_VAL_ON_INDEX then
:p185_error := 'User Friendly Error Message.........'
raise;
END;

I am sorry, I forgot to put semicolon at the end of third last line. The code will be like that in Excetion handling part.

:p185_error := 'User Friendly Error Message.........' ;

The process is a simple example of error handling in ApEx. It does inserting and at the
same time it handels exceptions. If an exception occurs, it will use a hidden item on the
page to store error messages. At the same time, the success message of that process
(which is what you get after pressing the "Save" button) is &P185_ERROR. item.

So, in this case I do not even use Validations, which are also there to handle exceptions
before your process happens and give your user a friendly messages. However, if you
already have constraints on your tables, this could be the way to go and do all at once,
without creating additional validations.

Hope this is clear.

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://htmldb.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

Bill21,

I have one thing to say to you about your validation solution and that is....

!!!!! YOU ROCK !!!!!

I've been looking for days to find a solution to this seemingly simple problem and you have listen carefully to my requirements and have provided a clear and concise answer.

I am using the first (and most simple) solution that you had provided. I substituted the 'y' with 'resource_id'. When the resource_id is null, the validation passes. When the resource_id is not null, the result is an inline user-friendly error.

Beautiful!

Thanks so much.
Have a great day!

leh

Hello Bill21,

Since you were so helpful with the validation problem, perhaps you would be able to help me with another question I have.

I've been trying to populate my form with a default value of "0" so that if a person forgats to fill in all of the fields, I would not get a misscalculation due to a null value.

Could you tell me how to set a default value for a form field?
Thanks

leh

You can set Default value 0 in property setting for a form field. It is under the 'Default' tag.

Thanks