1748164 Members
3657 Online
108758 Solutions
New Discussion юеВ

Re: Oracle 8i Question

 
Scott Buckingham
Regular Advisor

Oracle 8i Question

Yes, we are still in the dark ages! I can't find an answer in Metalink so I thought I'd try someone that really knows; all of you!

We have a column in a table called "comment". It's been there forever but now it has been requested to get changed since it is a reserved word. It seems simple enough to add the new column name, update it's contents with the existing data and then dropping the old name. However, the update fails becuse of the reserved word usage:

update tablename set new_commnet = comment;

ERROR at line 1:
ORA-00936: missing expression

update tablename set new_commnet = "comment"

ERROR at line 1:
ORA-00904: invalid column name

Is there another way around this? There is "comment" data in some of these rows so "create table tablename as" won't work either.
Long time dabbler, first time Admin / DBA
9 REPLIES 9
Steven E. Protter
Exalted Contributor

Re: Oracle 8i Question

Maybe export/import is the general course of action here.

If I remember I'll ask my oracle guy to read ths.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Scott Buckingham
Regular Advisor

Re: Oracle 8i Question

That is one thing Metalink did point out is that export/import can't handle this either so I didn't even try.
Long time dabbler, first time Admin / DBA
Sandman!
Honored Contributor

Re: Oracle 8i Question

How about...

update tablename t1
set new_commnet = (select comment from tablename t2 where t1.p_key = t2.p_key);
Scott Buckingham
Regular Advisor

Re: Oracle 8i Question

No, that's not it either. I get the same errors that I reported earlier.
Long time dabbler, first time Admin / DBA
Scott Buckingham
Regular Advisor

Re: Oracle 8i Question

Call off the search as I found the problem! I had to keep in mind that when using double-quotes to identify the reserved word that the object name is case-sensitive. Therefore, I needed to uppercase COMMENT.

Thank you for you help!
Long time dabbler, first time Admin / DBA
Yogeeraj_1
Honored Contributor

Re: Oracle 8i Question

hi scott!

You cannot rename a column in Oracle 8i.

In Oracle8i, you can drop a column from a table but not rename.

The way to do it is to use a view. You can minimize the impact on applications by doing simply:

rename TableName to TableName_TBL;
create view TableName
as
select c1 New_Name_For_C1, c2, c3, .... cn from TableName_Tbl;

After that you must grant on the view the same privileges that you had normally granted on the table.

This way, all existing applications will see the new column name with the old table name.

hope this helps!

kind regards
yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Oracle 8i Question

hi,

i tried same as you above and it worked!

please check.

yogeeraj@MYDB.MU> create table t1 (id number(1), "COMMENT" varchar2(100));

Table created.

Elapsed: 00:00:00.02
yogeeraj@MYDB.MU> desc t1
Name
Null? Type
-------------------------------------------------------------------------------
ID
NUMBER(1)
COMMENT
VARCHAR2(100)

yogeeraj@MYDB.MU> alter table t1 add new_commnet varchar2(100);

Table altered.

Elapsed: 00:00:00.08
yogeeraj@MYDB.MU> update t1 set new_commnet=t1."COMMENT";

0 rows updated.

Elapsed: 00:00:02.53
yogeeraj@MYDB.MU> desc t1
Name
Null? Type
-------------------------------------------------------------------------------
ID
NUMBER(1)
COMMENT
VARCHAR2(100)
NEW_COMMNET
VARCHAR2(100)

yogeeraj@MYDB.MU> alter table t1 drop column "COMMENT";

Table altered.

Elapsed: 00:00:00.53
yogeeraj@MYDB.MU> desc t1
Name
Null? Type
-------------------------------------------------------------------------------
ID
NUMBER(1)
NEW_COMMNET
VARCHAR2(100)

yogeeraj@MYDB.MU>



kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Oracle 8i Question

Hi Scott,

I'll do it this way:

- Make sure you have a good database backup;
- Test it in a Test database if possible;
- Now the commands:

create table bak_comment_table_name as
select a...., a.comments new_comments, a...
from a -- I'm not sure this will work

desc bak_comment_table_name; -- to confirm the new comment column is new_comments...

drop table cascade constraints; -- this is why you need to test this or, at least, have a good backup!

create table as
select * from bak_comment_table_name;

PS: try Yogeeraj's suggestion first since I didn't test this one...

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Scott Buckingham
Regular Advisor

Re: Oracle 8i Question

I had to keep in mind that when using double-quotes to identify the reserved word that the object name is case-sensitive. Therefore, I needed to uppercase COMMENT.

Thank you for you help!
Long time dabbler, first time Admin / DBA