Problem: converting constrained columns

From: Jed Deaver <jedster_at_richmond.infi.net>
Date: 1998/01/21
Message-ID: <01bd26a6$d7fc3a60$6902a8c0@jed>#1/1

I am trying to write a script that will change the data type of a LONG column to VARCHAR2(2000). The LONG column was originally defined with a NOT NULL constraint. I can determine the constraint name by querying the USER_CONS_COLUMNS table, but I need to somehow transfer this value into my ALTER TABLE command programmatically. Here is a theoretical example of what I need to do:

SQL> DECLARE
  2 CURSOR curCONSID IS SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS   3 WHERE TABLE_NAME='FOO'
  4 AND COLUMN_NAME='BAR';
  5 rowCONSID curCONSID%ROWTYPE;
  6 BEGIN
  7 ALTER TABLE FOO DISABLE CONSTRAINT rowCONSID.CONSTRAINT_NAME;   8 END;/ Essentially, I need to determine the name of the constraint and disable that constraint from a script. Is this possible using just SQL? I already have an application written if it is not.

Jed Deaver
Gyrus Software
jed_at_gyrus.com Received on Wed Jan 21 1998 - 00:00:00 CST