Solved

Changing a CLOB to a Varchar2

Posted on 2014-10-13
3
257 Views
Last Modified: 2014-10-13
Hi Experts,

I have a situation where we have a CLOB when it should have been a varchar2(4000). We know that no data exceeds 4000 characters and want to convert the datatype.

I have created a new column of type Varchar2(4000 CHAR) successfully.
When I try and update the table to set the new column I get a constraint error and it is talking about deleting data

My code is

      ALTER TABLE XXX ADD TITLE_VAR VARCHAR2(4000 CHAR);
     
      UPDATEXXX SET TITLE_VAR = SUBSTR (TITLE, 1, 4000);
     
      ALTER TABLE XXX DROP COLUMN TITLE;
     
      ALTER TABLE XXX RENAME COLUMN TITLE_VAR TO TITLE;

The 1st line executes correctly, the second line however gives the following

SQL Error: ORA-02292: integrity constraint (AQD.FK_QACKLISTRES_QACKLISTITM) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

The foreign key being referred to is the primary key for this table being referenced by another table. The old and new columns being changed are not part of any relationship.

This error makes me think that Oracle is deleting the table and recreating - I can't think of any reason for this error.

It just doesn't make sense. The version of Oracle is 11.2.0.4 Enterprise with the code being run through SQL Developer.

I can't find any reference to this in any material anywhere. The solution I am using is widely quoted.

Does anyone have any ideas?

Kelvin
0
Comment
Question by:Kelvin Sparks
3 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40376563
Cannot recreate your scenario, but have you given a try using dbms_redefinition package and sync the tables?, this should work without any errors, use primary key as it seems you already have one on your table..

For Sample illustrations of how to use dbms_redefinition, check burleson site..

http://www.dba-oracle.com/t_dbms_redefinition_example.htm
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 40377422
My guess would be a trigger, or a constraint that was created with ENABLE NO VALIDATE.

I would check for triggers first.
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 40377653
Thanks Johnsone

All it took was a fresh approach when the problem  caused me to become tunnel visioned. A trigger was the culprit - you just don't fire a trigger for a sequence on update! Not sure how this got in - and is a rarely updated table.

Thanks for the fresh view!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now