CODEWITHSUNDEEP
oracle-database
milheiros
milheiros

Reputation: 619

How to drop columns in Oracle7

How to drop a table column in Oracle7? Yes, yes... My client still uses this version: Oracle7 Server Release 7.3.4.5.0

Or... how to change column datatype from Varchar2 to Long... in Oracle7 Server Release 7.3.4.5.0

Upvotes: 1

Views: 67

Answers (1)

milheiros
milheiros

Reputation: 619

My solution:

--1. Create a clone table.
CREATE TABLE tableA_aux
AS
   SELECT *
   FROM tableA;

/

-- 2. Alter table to accept null values (if is not nullable)
ALTER TABLE tableA MODIFY text varchar2(2000);

-- 3. Clear table column
UPDATE tableA
SET text = NULL;

COMMIT;

-- 4. Alter datatype to VARCHAR2 -> LONG
ALTER TABLE tableA MODIFY text LONG;


--5. Update the column with original values
UPDATE tableA s
SET s.text =
       (SELECT a.text
        FROM tableA_aux a
        WHERE a.PK_ID = s.PK_ID);

COMMIT;

Upvotes: 1

Related Questions