Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-00001

SETH YOMBA6 days ago

DECLARE
v_countryid locations.country_id%TYPE := 'CA';
v_loc_id locations.location_id%TYPE;
v_counter NUMBER(2) := 1;
v_new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO v_loc_id FROM locations
WHERE country_id = v_countryid;
LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES (v_loc_id + v_counter, v_new_city, v_countryid);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
END LOOP;
END;

i get an ora00001 at line when running the code. The table is called "locations," in SQL*Plus.

Comments

L. Fernigrini 6 days ago

ORA-00001

unique constraint (constraint_schema.constraint_name) violated on table table_schema.table_name columns (column_names)

That means that you are trying to insert values that are repatead and a Unique key exits that prevents that. Post the DDL of the Locations table, probably the unique is on the City column since you are trying to insert Montreal three times.

James Su 6 days ago

The MAX(location_id) is for v_countryid only, it's very likely MAX(location_id)+1 is already used by another country.

Solomon Yakobson 6 days ago

Using MAX(location_id) is bad design. Column location_id should be either sequence populated or defined as identity column. You MUST use sequence.nextval if there is a dedicated sequence. If there is no sequence you should at the minimum change:

SELECT MAX(location_id) INTO v_loc_id FROM locations
WHERE country_id = v_countryid;

to

SELECT MAX(location_id) INTO v_loc_id FROM locations;

But you still can get unique constraint violation if multiple sessions will be executing that code.

SY.

Solomon Yakobson 6 days ago

Also, why would you insert same country/city 3 times?

1 - 4

Post Details

Added 6 days ago
4 comments
57 views