CODEWITHSUNDEEP
sqloracle-databaseoracle-apexoracle-apex-5.1
HSN
HSN

Reputation: 61

Unique constraint validation APEX

I have created a validation for a text field in my application. The text field is "Location." It has a unique constraint and when you enter an existing location it will give you an ORA- error message. Instead of this, I want to display a field inline error message. I did the following.

  1. Create validation
  2. Type: Item=Value
  3. Item = :P3_LOCATION, Value = #LOCATION#
  4. Error message: Location already exists. Inline with field. Associated item is :P3_LOCATION
  5. Server-side Condition
  6. When Button Pressed: CREATE, Type = Item is NOT NULL, Item = P3_LOCATION.

This does exactly what I want but when I try to create a new location, it also gives me this error message. What can I do to have it only affect the locations that already exist?

Upvotes: 2

Views: 3439

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

Well, you never actually control whether location you're entering right now already exists in the table.

I'd suggest the following:

  • create validation on the item
  • its type is "PL/SQL Function (returning Error Text)"

  • PL/SQL Function Body:

    declare
  l_loc your_table.location%type;
begin
  select location
    into l_loc
    from your_table
    where location = :P3_LOCATION;

  return ('Location already exists');
exception
  when no_data_found then null;
end;

  • Display position: Inline with field

  • Associated item: P3_LOCATION
  • When button pressed: CREATE

Upvotes: 1

Related Questions