Solved

Changing a CLOB to a Varchar2

Posted on 2014-10-13
3
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

623 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