This content has been marked as final. Show 5 replies
When are you seeing this problem ?
Have you now migrated the data into Oracle but are getting duplicate values or errors from Oracle inserts ? The 'ignore_dup_key' prevents duplicate data from being inserted on the Sybase side, so what is the "create index" statement for the table in Sybase and Oracle for the table giving you problems ?
This table gets populated through a procedure in oracle, so when the procedure runs in oracle there are additional rows in the table, which are not inserted in Sybase due to the ignore_dup_key.
So i need to a remove those records from oracle table.
I wanted to know if there is way through which we can replicate the behaviour of ignore_dup_key in oracle.
That is why I asked if you had an index on the Oracle table. A unique constraint or similar would prevent duplicate rows. However, I am not sure if there is an equivalent to ignore_dup_key where only the duplicate rows are rejected if it is set to yes.
I think your best option would be ask in the SQL and PL/SQL forum -
PL/SQL and SQL
They will be better able to help with a question like that.
I think there is some confusion around ignore_dup_key functionality. Ignore_dup_key in Sybase essentially allows an application to continue without aborting a on-going transaction even if there was an attempt to insert duplicate values for columns that have a Unique index on them with ignore_dup_key clause. But (I emphasize) Sybase does not allow duplicate values in a column on which a unique index exists.1 person found this helpful
Typically when you try to insert duplicate values for columns with unique/primary indexes, you get a "unique constraint violation error". This error under normal conditions will result in the failure of the complete transaction. With IGNORE_DUP_KEY clause, the sybase database simply ignores that "INSERT/UPDATE" statement and continues with the rest of the transaction.
What that means is if you have a index on a table in Sybase with Ignore_dup_key clause then most likely that index is a UNIQUE index. So that means you shouldn't see duplicate values in the affected column(s). If you do see duplicate values then the implication is that the index is not a UNIQUE index and hence IGNORE_DUP_KEY clause does not really add value. Customer may have changed their schema design later realizing that those columns will have duplicate values but forgot to take out the IGNORE_DUP_KEY clause. Also if you already have duplicate values for a column, you cannot create a UNIQUE index even in Sybase with this clause.
Since you are seeing duplicate values in the table so I am guessing that it is a regular B*tree index so you can do the same in Oracle and ignore the IGNORE_DUP_KEY clause as Oracle does not offer such functionality.
If you really want to create a unique index in Oracle and not have the duplicate values then you will have to try couple of different approaches. Create an External table and use PL/SQL procedure/function to load the data using MERGE statements or INSERT INTO... SELECT FROM EXTERNAL_TABLE with the LOG ERROR Clause to reject the duplicate values without failing the overall data load.
Hope this helps..
Oracle 22.214.171.124 has IGNORE_ROW_ON_DUPKEY_INDEX hint which can be used for this functionality.