topic Re: Oracle 8i Question in Operating System - HP-UX

Oracle 8i Question

Scott Buckingham — Tue, 08 Nov 2005 15:03:26 GMT

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.

Re: Oracle 8i Question

Steven E. Protter — Tue, 08 Nov 2005 15:20:18 GMT

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

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

SEP

Re: Oracle 8i Question

Scott Buckingham — Tue, 08 Nov 2005 15:23:16 GMT

That is one thing Metalink did point out is that export/import can't handle this either so I didn't even try.

Re: Oracle 8i Question

Sandman! — Tue, 08 Nov 2005 15:35:26 GMT

How about...

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

Re: Oracle 8i Question

Scott Buckingham — Tue, 08 Nov 2005 15:53:16 GMT

No, that's not it either. I get the same errors that I reported earlier.

Re: Oracle 8i Question

Scott Buckingham — Tue, 08 Nov 2005 16:19:00 GMT

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!

Re: Oracle 8i Question

Yogeeraj_1 — Tue, 08 Nov 2005 23:20:59 GMT

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

Re: Oracle 8i Question

Yogeeraj_1 — Tue, 08 Nov 2005 23:39:06 GMT

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

Re: Oracle 8i Question

Eric Antunes — Wed, 09 Nov 2005 04:44:52 GMT

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

Re: Oracle 8i Question

Scott Buckingham — Wed, 09 Nov 2005 08:09:51 GMT

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!