Error ora 00001 unique constraint violated - Исправление ошибок и поиск оптимальных решений проблем

Have you gotten an “ORA-00001 unique constraint violated” error? Learn what has caused it and how to resolve it in this article.

ORA-00001 Cause

If you’ve tried to run an INSERT or UPDATE statement, you might have gotten this error:

ORA-00001 unique constraint violated

This has happened because the INSERT or UPDATE statement has created a duplicate value in a field that has either a PRIMARY KEY constraint or a UNIQUE constraint.

There are a few solutions to the “ORA-00001 unique constraint violated” error:

Change your SQL so that the unique constraint is not violated.
Change the constraint to allow for duplicate values
Drop the constraint from the column.
Disable the unique constraint.

Solution 1: Modify your SQL

You can modify your SQL to ensure you’re not inserting a duplicate value.

If you’re using ID values for a primary key, it’s a good idea to use a sequence to generate these values. This way they are always unique.

You can use the sequence.nextval command to get the next value of the sequence.

So, instead of a query like this, which may not work if the employee_id value is already used:

INSERT INTO employee (employee_id, first_name, last_name)
VALUES (231, 'John', 'Smith');

You can use this:

INSERT INTO employee (employee_id, first_name, last_name)
VALUES (seq_emp_id.nextval, 'John', 'Smith');

Assuming the sequence is set up correctly, this should ensure that a unique value is used.

Find the constraint that was violated

The “ORA-00001 unique constraint violated” error usually shows a name of a constraint. This could be a descriptive name (if you’ve named your constraints when you create them) or a random-looking name for a constraint.

You can query the all_indexes view to find the name of the table and other information about the constraint:

SELECT *
FROM all_indexes
WHERE index_name = <constraint_name>;

This will give you more information about the specific fields and the table.

Solution 2: Change the constraint to allow for duplicates

If you have a unique constraint or primary key set up on your table, you could change the constraint to allow for duplicate values, to get around the ORA-00001 error.

Let’s say the unique constraint applies to first_name and last_name, which means the combination of those fields must be unique.

If you find that that rule is incorrect, you can change the constraint to say that the combination of first_name, last_name, and date_of_birth must be unique.

To do this, you need to drop and recreate the constraint.

To drop the constraint:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Then, recreate the constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (col1, col2....);

Now your constraint will reflect your rules.

Solution 3: Remove the unique constraint

The third solution would be to drop the unique constraint altogether.

This should only be done if it is not required.

To do this, run the ALTER TABLE command:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

The constraint will be removed and you should be able to UPDATE or INSERT the data successfully.

Solution 4: Disable the unique constraint

The final solution could be useful if you’re doing a lot of data manipulation and you need to temporarily disable the constraint, with the aim of enabling it later.

Disabling the constraint will leave it in the data dictionary and on the table, with the same name, it just won’t be checked when data is inserted or updated.

To disable the constraint:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

If you need to enable the constraint in the future:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

So, that’s how you can resolve the “ORA-00001 unique constraint violated” error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Источник

Problem

Error message running a stream exporting data to Oracle:
23000[1][520][ODBC Oracle Wire Protocol Driver][Oracle]ORA-00001: unique constraint (xxx) violated
(where xxx stands for the constraint name)

Cause

You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

Resolving The Problem

The option(s) to resolve this Oracle error are:
1) Drop the unique constraint.
2) Change the constraint to allow duplicate values.
3) Modify your SQL so that a duplicate value is not created.
If you are not sure which unique constraint was violated, you can run the following SQL:

SELECT DISTINCT table_name
FROM all_indexes
WHERE index_name = ‘xxx’; (where xxx stands for the constraint name from the error message)
Ref below url’s for more info:
https://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm
http://www.techonthenet.com/oracle/errors/ora00001.php

So basically this issue happens if you attempt to insert an already existing value into a column defined as requiring unique data. You may have to check with your Oracle DBA and see if that constraint has been set and if so if it can be removed, as mentioned above, so the export can be completed. Either
way the issue is with the Oracle Database you are trying to write to and will have to be taken care of on the Oracle side

Related Information

[{«Product»:{«code»:»SS3RA7″,»label»:»IBM SPSS Modeler»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Modeler Server»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Not Applicable»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Источник

There’re only two types of DML statement, INSERT and UPDATE, may throw the error, ORA-00001: unique constraint violated. ORA-00001 means that there’s a constraint preventing you to have the duplicate value combination. Most likely, it’s an unique constraint. That’s why your INSERT or UPDATE statement failed to work.

Let’s see some cases.

1. INSERT

We inserted into a row that violate the primary key.

SQL> insert into employees (employee_id, last_name, email, hire_date, job_id) values (100, 'Chen', 'EDCHEN', to_date('17-JAN-22', 'DD-MON-RR'), 'AC_MGR'); insert into employees (employee_id, last_name, email, hire_date, job_id) values (100, 'Chen', 'EDCHEN', to_date('17-JAN-22', 'DD-MON-RR'), 'AC_MGR') * ERROR at line 1: ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

In the error message, it told us that we specifically violate HR.EMP_EMP_ID_PK.

Please note that, not all primary keys are unique, it’s allowable to have non-unique primary keys.

2. UPDATE

We updated a row that violate an unique index.

SQL> update employees set email = 'JCHEN' where employee_id = 100; update employees set email = 'JCHEN' where employee_id = 100 * ERROR at line 1: ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

In the error message, it told us that we specifically violate HR.EMP_EMAIL_UK.

Solution

To solve ORA-00001, we should use a different value to perform INSERT INTO or UPDATE SET statement. The solution may sound easy to say, but hard to do, because we may not know what columns we violated.

Check Constraint Columns

So let’s see how we check unique columns.

SQL> column table_name format a20; SQL> column column_name format a20; SQL> select table_name, column_name, position from all_cons_columns where owner = 'HR' and constraint_name = 'EMP_EMAIL_UK';


  TABLE_NAME           COLUMN_NAME            POSITION
 -------------------- -------------------- ----------
 EMPLOYEES            EMAIL                         1

The above query tells us that the column combination in the output is violated. To comply with the unique constraint, you can almost do nothing except for checking the existing row.

Drop Unique Constraint to Prevent ORA-00001

An alternative solution is to drop the unique index if it’s not necessary anymore. Dropping a primary or unique index needs more skills, otherwise you might see ORA-02429.

In a multithread environment, you may check whether the row is existing or not, then do your INSERT in order to prevent ORA-00001.

declare v_row_counts number; begin select count(*) into v_row_counts from employees where employee_id = 100; if v_row_counts = 0 then -- Insert the row else -- Do not insert the row end if; end; /

In the above block of code, if the row count is 0, then we can do INSERT right after counting, elsewhere don’t do it.

Источник

ORA-00001 unique constraint violated is one of the common messages we often get while loading data.

This ORA-00001 unique constraint violated error occurs when You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

We can perform the following action items for this ORA-00001

(1) You can look at the error and find the constraint information with the below sql

SELECT column_name, position
FROM all_cons_columns
WHERE constraint_name = '<name of the constraint in error>'
AND owner = '<table owner>'
AND table_name = '<table name>'

Now we can check the existing data with the data we are inserting and then take action accordingly. You can change the keys so that they can be inserted

CREATE TABLE EXAMPLE_UNIQ(
EXAM_ID NUMBER NOT NULL ENABLE,
EXAM_NAME VARCHAR2(250) NOT NULL ENABLE
CONSTRAINT UK1_EXAMPLE UNIQUE (EXAM_ID, EXAM_NAME) ENABLE
);
table created.

INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, 'XYZ');
1 rows inserted.
Commit;

INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, 'XYZ');
SQL Error: ORA-00001: unique constraint (UK1_EXAMPLE) violated
Solution
INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1001, 'XYZ');
1 rows inserted.

(2) We can drop the constraint if duplicates are allowed in the table

Example

alter table <table name> drop constraint <constraint name>;

(3) The 11gr2 hint ignore_row_on_dupkey_index allows the statement to silently ignore ORA-00001 errors.

The IGNORE_ROW_ON_DUPKEY_INDEX hint are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.

The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.

The semantic effect of this hint results in error messages if specific rules are violated:

If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.
You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.
You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.
As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.

insert /*+ ignore_row_on_dupkey_index(unique_table, unique_table_idx) */
into
unique_table
(select * from non_unique_table);

Related articles

ORA-00911: invalid character
ORA-03113: end-of-file on communication channel
ORA-00257
ORA-29285: file write error
ORA-29913 with external tables
delete query in oracle
https://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm

Reader Interactions

Источник

ORA-00001 Error Message

Error messages in Oracle can seem like nagging roadblocks, but some are truly designed and signaled to aid in the efficiency of the database. While this may seem counterintuitive, if the program simply allowed the user to have free reign in making mistakes, Oracle would not be the dynamic and streamlined database software that it is known as. The ORA-00001 error message is indicative of this pattern of thinking.

The ORA-00001 message is triggered when a unique constraint has been violated. Essentially the user causes the error when trying to execute an INSERT or UPDATE statement that has generated a duplicate value in a restricted field. The error can commonly be found when a program attempts to insert a duplicate row in a table.

Before we progress to resolving the issue, it would be ideal to have some more information about the error. If you are unsure as to which constraint was violated to trigger the ORA-00001, there is an SQL statement that you can run to find out more about it. You can type the following command in:
SELECT DISTINCT table_name FROM all_indexes WHERE index_name = 'CONSTRAINT_NAME' ;
The constraint name can be found by looking at the error message itself. In parenthesis following the ORA-00001 notice, the constraint should be listed. This process will then return the name of the table that features the violated constraint.

Now that we know the constraint and table in question, we can move forward with fixing the problems itself. There are a few basic options. You can modify the SQL so that no duplicate values are created, thus no errors are triggered. If you do not wish to do this, you can also simply drop the table constraint altogether. This would only be recommended if the constraint is unnecessary for the foundation of your table.

Another option would be to modify the constraint so that it can allow duplicate values in your table. Depending on your version of Oracle, this can be done multiple ways. The first and universal method would be to manually adjust the constraint. However, if you’re using Oracle 11g or newer, you can use the ignore_row_on_dupkey_index hint. This feature will allow for insert SQL’s to enter as duplicates and be effectively ignored so that an ORA-00001 message will not be triggered.

By employing hints such as this, the ORA-00001 error can be sidestepped in many circumstances. A trigger method can also be a preventative approach to minimizing the frequency of ORA-00001 errors. These types of automatic increment columns can overwrite the value from an ID by inserting a value from the sequence in its place. On a more broad scale, remaining knowledgeable of the types of constraints that you are working with will help not only in preventing an error, but by also allowing you to respond to it quickly when it does occur. If you are unsure about some of the constraints on your tables or would like to know more about the most up-to-date versions of Oracle that include favorable hints the ignore_row_on_dupkey_index, it may be a good idea to speak with your licensed Oracle consultant for more information.

Источник

Содержание

Error ‘ORA-00001: UNIQUE CONSTRAINT (AP.AP_INVOICE_DISTRIBUTIONS_U1) VIOLATED’ During Invoice Validation (Doc ID 2443983.1)
Applies to:
Symptoms
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
MERGE STATEMENT FAILS WITH ORA-00001 UNIQUE CONSTRAINT VIOLATION (Doc ID 1081283.1)
Applies to:
Symptoms
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
ORA-00001: unique constraint violated error
Answers
ORA-00001: unique constraint error..
Answers
ORA-00001: unique constraint violated

Error ‘ORA-00001: UNIQUE CONSTRAINT (AP.AP_INVOICE_DISTRIBUTIONS_U1) VIOLATED’ During Invoice Validation (Doc ID 2443983.1)

Last updated on JULY 15, 2022

Applies to:

Symptoms

On : 12.1.X/12.2.X version, GST Payables

When User is validating invoice, he is getting following error:-

Unexpected error occurred during Tax Calculation.
Exception: Encountered the error in
JAI_TAX_PROCESSING_PKG.CALCULATE_TAXORA-20001: —1:ORA-00001: unique
constraint
(AP.AP_INVOICE_DISTRIBUTIONS_U1) violated.
Please correct the problem or contact your System Administrator.

In FND Debug log message, following error message has been captured.

JAI.PLSQL.JAI_TAX_DETERMINATION_PKG.jai_insert_ap_inv_dist() ORA-00001:
unique constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated
fnd.plsql.APP_EXCEPTION.RAISE_EXCEPTION.dict_auto_log —1: ORA-00001: unique
constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated
JAI.PLSQL.JAI_TAX_PROCESSING_PKG.CALCULATE_TAX ORA-20001: —1: ORA-00001:
unique constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated

STEPS
————————
The issue can be reproduced at will with the following steps:
1. Create Invoice.
2. Validate the invoice

BUSINESS IMPACT
————————
The issue has the following business impact:
Due to this issue, users cannot make vendor payment on time.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Источник

MERGE STATEMENT FAILS WITH ORA-00001 UNIQUE CONSTRAINT VIOLATION (Doc ID 1081283.1)

Last updated on FEBRUARY 02, 2022

Applies to:

Oracle Database — Enterprise Edition — Version 10.2.0.4 to 11.2.0.1.0 [Release 10.2 to 11.2]
Oracle Database — Enterprise Edition — Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

MERGE STATEMENT AFTER UNCOMMITTED UPDATE FAILS WITH ORA-1

The problem can be reproduced as follows:
1.
Session #1

SQL*Plus: Release 10.2.0.4.0 — Production on Thu Jan 21 16:40:52 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 — Production With
the Partitioning, OLAP, Data Mining and Real Application Testing options

2.
SQL> CREATE TABLE table1
(
ORDER_LINE_ID NUMBER(19) NOT NULL,
click NUMBER NOT NULL,
CONSTRAINT PK3
PRIMARY KEY
( ORDER_LINE_ID)
)
ORGANIZATION INDEX;

3.
SQL> CREATE TABLE TABLE2
(
ORDER_LINE_ID NUMBER,
CLICK NUMBER
);
Table created.

4.
SQL> Insert into TABLE1 (ORDER_LINE_ID, CLICK) Values (10430001, 20);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 10);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 20);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10430001, 50);
1 row created.

SQL> commit;
Commit complete.

5.
SQL> UPDATE table1 set click=click+5 WHERE order_line_id=10430001;
1 row updated.

SQL> connect login/password
Connected.
SQL> set autotrace on explain
SQL> MERGE INTO table1 t1
USING(
SELECT
fact.ORDER_LINE_ID,
sum (fact.click
) AS click
FROM table2 fact
GROUP by fact.ORDER_LINE_ID
ORDER BY fact.ORDER_LINE_ID
) t2 ON (
t1.ORDER_LINE_ID = t2.ORDER_LINE_ID
)
WHEN MATCHED THEN
UPDATE SET
t1.click = t2.click+10
WHEN NOT MATCHED THEN
INSERT (
ORDER_LINE_ID,
click
) VALUES (
t2.ORDER_LINE_ID,
t2.click) ;

MERGE INTO table1 t1
(the session is waiting)

7.
Go back to Session #1 and enter commit
SQL> commit;
Commit complete.

Now i see in session 1 the following about the waiting MERGE statement:
*
ERROR at line 1:
ORA-00001: unique constraint (OWNER.PK3) violated

8.
Go to session #2 and re-run the merge sql
SQL> /
2 rows merged.

| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|

| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|

| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|

| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|

| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|

|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|

Note
——
— dynamic sampling used for this statement

| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|

| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|

| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|

| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|

| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|

|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|

Note
——
dynamic sampling used for this statement

WORKAROUND:
————
COMMIT update before running MERGE command.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Источник

ORA-00001: unique constraint violated error

Table T has four columns A,B,C,D all are not null. Also, unique constraint is defined on column A & B

but, one of the insert statement got failed with the below error.

Getting DB Error: ORA-00001: unique constraint violated error on inserting the value.

It means already record is existing in the table.

As, thousands of records are getting inserted online in the table at a time, so could not able to trace which record is being inserted.

So, how to find that record in the table which is already existing due to which above unique constraint violation error is raised ?

Answers

Error: ORA 1
Text: unique constraint violated
——————————————————————————-
Cause: An update or insert statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.
For Trusted Oracle7 configured in DBMS MAC mode, you may see this
message if a duplicate entry exists at a different level.

check in the insert statement which was already in the table.. and also constraint assigned on that column.. if unique constarint then give another value
Thanks

but, I want to find that record in the table and want to delete, so how to find that record ?

Edited by: user640001 on Sep 27, 2010 12:00 AM

can you please post the transaction which you are inserting.

As all transactions are online, so neither can trace the transactions nor have any record details which is creating the problem.

From the db, I came to know that a composite primary key is defined including two columns A,B which is restricting that record.

ALTER TABLE T ADD (
CONSTRAINT T_PK
PRIMARY KEY
(A,B));

Just having the error details and knowing that insert statement is failing due to above constraint

And why do you need to identify the statement which is trying to violate this constraint?

Perhaps starting there will give us a little background information we’re presently missing.

I want to delete that record.

so, I need sql statement to find out that record.

user640001 wrote:
Hi,

I want to delete that record.

so, I need sql statement to find out that record.

The question of «why» still hasn’t really been answered.

If the inserts are coming from an application, and the table has a unique constraint placed on it to protect the data, WHY do you think the application attempting to violate this constraint has «better» data?

If this is indeed the way the application needs to function, then you need to modify the application to account for this.
Instead of the current Which doesn’t attempt to catch the unique constraint error.

Since you do not at present know the data trying to violate the constraint (that’s your entire question) i find it difficult to understand how you could presume to know that it is correct . you know nothing about it so the course you are on seems to be the incorrect one (my opinion anyway).

Источник

ORA-00001: unique constraint error..

Hi There,
We were trying to do an insert when we started having ORA-00001: unique constraint error.. to speed our testing we decided to disable all the constraints on the table; however we still having the same issue.

How can we resolve this please.

Anything else we can do please?

Answers

but isn’t the unique index constraint part of the disabled constraints on the table as shown above?

I’m surprised that after we disabled all constraints on the table, this is still an issue??

We can certainly drop it for the time being.. will keep you posted.

rsar001 wrote:
but isn’t the unique index constraint part of the disabled constraints on the table as shown above?

I’m surprised that after we disabled all constraints on the table, this is still an issue??

We can certainly drop it for the time being.. will keep you posted.

Not if index used by PK was created separately prior to PK:
But by dropping index you are simply delaying the issue. Yes, you will be able to insert, but what’s then? You will not be able to recreate PK — same violation error will be raised.

One should keep an eye out for constraints forced through unique indexes and/or any other objects.

We completely missed out the fact that the constraints here are forced through the unique index created before creating the PK.

Источник

ORA-00001: unique constraint violated

I am getting very strange problem here , i don’t understand where is the issue and what is the solution.

Database version 11.2.0.4.0

I have a table let’s say

tb_legal_arb_execution

Composite primary key (comp_code,sec_requisition_no)

Insert statement for inserting data into the table giving error ORA-00001: unique constraint violated

The get_exe_requisition_no is function which is written inside the package legal_requisition_package.

This legal_requisition_package.get_exe_requisition_no is generating the sec_requisition_no for the table tb_legal_arb_execution

which is SE/0018/C/0715/00002 is correct.

The result of the above query(which is used in the insert statement) is giving the correct result as expected.

COMP_CODE	REQUISITION_NO	SEC_REQUISITION_NO	PROPOSAL_NO	TRUNC(SYSDATE)	ZONE_CODE	REGION_CODE	BRANCH_CODE	LOCATION_CODE	SENT_ARB_APRV	ARB_APPROVE_ID	ARB_APPROVE_DATE
000002	LG/0018/C/0714/00004	SE/0018/C/0715/00002	PG/0018/C/11/000102	22-Jun-2016	000002	000012	000018	0018	Y	USER	22-Jun-2016

If the insert statement written directly like this then record is inserted successfully.

Why the record is not inserting into the table while using the package and function ?

Источник

Oracle raises ORA-00001 error when an unique constraint is violated by a INSERT or UPDATE statement that attempts to insert
a duplicate key. SQL Server raises error 2627 in this case.

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

Unique Constraint Violation in Oracle

Assume there is a table with the primary key in Oracle:

Oracle:

   CREATE TABLE states
   (
      id CHAR(2) PRIMARY KEY,
      name VARCHAR2(90)
   );
 
   -- Let's insert a row
   INSERT INTO states VALUES ('MO', 'Missouri');
   # 1 row created.

Now let’s try to insert a row that violates the unique constraint enforced by the primary key:

Oracle:

   -- Assign MO instead of MT abbreviation to Montana by mistake 
   INSERT INTO states VALUES ('MO', 'Montana');
   # ERROR at line 1:
   # ORA-00001: unique constraint (ORA.SYS_C0014290) violated

You can see that ORA-00001 error is raised when an unique constraint is violated in Oracle.

Unique Constraint Violation in Microsoft SQL Server

Now let’s create the same table with the primary key in SQL Server:

SQL Server:

   CREATE TABLE states
   (
      id CHAR(2) PRIMARY KEY,
      name VARCHAR(90)
   );
 
   -- Let's insert a row
   INSERT INTO states VALUES ('MO', 'Missouri');
   # 1 row created.

Now we will try to insert a row that violates the unique constraint enforced by the primary key:

SQL Server:

   -- Assign MO instead of MT abbreviation to Montana by mistake 
   INSERT INTO states VALUES ('MO', 'Montana');
   # Msg 2627, Level 14, State 1, Line 1
   # Violation of PRIMARY KEY constraint 'PK__states__3213E83FFFE97CF5'. 
   # Cannot insert duplicate key in object 'dbo.states'.
   # The duplicate key value is (MO).

SQL Server raises error 2627 with the severity level 14 when a primary key is violated.

You can also call @@ERROR function to get the error code in SQL Server:

SQL Server:

  -- Get the error code of the last Transact-SQL statement
  SELECT @@ERROR
  # 2627

Resources

Oracle 11g Release 2 Documentation

Microsoft SQL Server 2012 — Books Online

SQLines Services

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.

Источник