Re: Problem: converting constrained columns

Hi Jed,

try:

Set TERMOUT ON
SET ECHO ON spool c:\cons.sql

SELECT ' ALTER TABLE FOO DISABLE CONSTRAINT '         || CONSTRAINT_NAME || ' ;'
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME='FOO'
AND COLUMN_NAME='BAR';
spool off
@c:\cons.sql

This will generate a script in file 'c:\cons.sql', that contains the command(s) to disable your constraint(s).

Jed Deaver wrote:
>
> 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
 

-- 
Regards

Matthias Gresz    :-)