Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Changing a CLOB to a Varchar2

Posted on 2014-10-13
3
Medium Priority
?
280 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 35

Accepted Solution

by:
johnsone earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

916 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