Home » SQL & PL/SQL » SQL & PL/SQL » Cant get rid of ORA-14155 after all googling (2 threads merged by bb) (9i, 9.0.1.1, XP)
Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661183] Fri, 10 March 2017 05:44 Go to next message
billgates
Messages: 19
Registered: March 2017
Junior Member
I know there has been a big debate on this BUT nothing has worked for me so far.

SQL> DESC IMS_GRNM;

Name Type
GRNNO VARCHAR2(10)
BILL VARCHAR2(7)


SQL> ALTER TABLE IMS_GRNM RENAME COLUMN BILL TO INVOICE;
ORA-14155: missing PARTITION or SUBPARTITION keyword

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='IMS_GRNM';

TABLE_NAME PARTITION_NAME
----------- ---------------



SQL> SHO PARAMETER COMPATIBLE

NAME TYPE VALUE
---------- ------ -----
compatible string 9.0.0

Can somebody please tell me in simple words how to get rid of it.

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661186 is a reply to message #661183] Fri, 10 March 2017 06:00 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
I am not aware of any debate. You just need to use the correct syntax and it works:
orclz>
orclz> desc dept;
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 DNAME                                                                VARCHAR2(14)
 LOC                                                                  VARCHAR2(13)

orclz> alter table dept rename column loc to location;

Table altered.

orclz> desc dept;
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 DNAME                                                                VARCHAR2(14)
 LOCATION                                                             VARCHAR2(13)

orclz>

[Updated on: Fri, 10 March 2017 06:01]

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661187 is a reply to message #661186] Fri, 10 March 2017 06:03 Go to previous messageGo to next message
billgates
Messages: 19
Registered: March 2017
Junior Member
That is exactly the same syntax I'm using

ALTER TABLE IMS_GRNM RENAME COLUMN BILL TO INVOICE;
orclz> alter table dept rename column loc to location;

[Updated on: Fri, 10 March 2017 06:04]

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661188 is a reply to message #661187] Fri, 10 March 2017 06:04 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Well, perhaps it doesn't work in your release. It is a bit old.

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661189 is a reply to message #661188] Fri, 10 March 2017 06:05 Go to previous messageGo to next message
billgates
Messages: 19
Registered: March 2017
Junior Member
what do i do? how to resolve this release thing

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661190 is a reply to message #661187] Fri, 10 March 2017 06:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
I believe the problem is that RENAME COLUMN was not available in Oracle 9. I think it was introduced in 10.

SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE ims_grnm
  2    (grnno  VARCHAR2(10),
  3  	bill   VARCHAR2(7))
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> DESC ims_grnm
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRNNO                                              VARCHAR2(10)
 BILL                                               VARCHAR2(7)

SCOTT@orcl_12.1.0.2.0> ALTER TABLE ims_grnm RENAME COLUMN bill TO invoice
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> DESC ims_grnm
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRNNO                                              VARCHAR2(10)
 INVOICE                                            VARCHAR2(7)

SCOTT@orcl_12.1.0.2.0>

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661191 is a reply to message #661190] Fri, 10 March 2017 06:09 Go to previous messageGo to next message
billgates
Messages: 19
Registered: March 2017
Junior Member
Is this the only solution to move onto 10g at least from 9i?

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661192 is a reply to message #661191] Fri, 10 March 2017 06:10 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
You could add a column with the new name, copy everything into it, and drop the old column.

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661193 is a reply to message #661191] Fri, 10 March 2017 06:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
In older versions, as a workaround, you can add the new column, update the new column with the data from the old column, then drop the old column, as shown below.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE ims_grnm
  2    (grnno  VARCHAR2(10),
  3  	bill   VARCHAR2(7))
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> DESC ims_grnm
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRNNO                                              VARCHAR2(10)
 BILL                                               VARCHAR2(7)

SCOTT@orcl_12.1.0.2.0> INSERT INTO ims_grnm (grnno, bill) VALUES ('grnno1', 'bill1')
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM ims_grnm
  2  /

GRNNO      BILL
---------- -------
grnno1     bill1

1 row selected.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE ims_grnm ADD (invoice  VARCHAR2(7))
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> UPDATE ims_grnm SET invoice = bill
  2  /

1 row updated.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE ims_grnm DROP COLUMN bill
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> DESC ims_grnm
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRNNO                                              VARCHAR2(10)
 INVOICE                                            VARCHAR2(7)

SCOTT@orcl_12.1.0.2.0> SELECT * FROM ims_grnm
  2  /

GRNNO      INVOICE
---------- -------
grnno1     bill1

1 row selected.

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661194 is a reply to message #661192] Fri, 10 March 2017 06:11 Go to previous messageGo to next message
billgates
Messages: 19
Registered: March 2017
Junior Member
That would be the last option I guess. Thanks Mate.

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661195 is a reply to message #661191] Fri, 10 March 2017 06:21 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
billgates wrote on Fri, 10 March 2017 06:09
Is this the only solution to move onto 10g at least from 9i?
I'd think you'd want to move to a version that is supported . . . or at least was supported at some time in this millenium.

If you are still running 9i you are either running without support, or you are paying through the nose for it. Is the rest of your technology stack (hardware, OS, etc) equally old and outdated?

Report message to a moderator

Re: Cant get rid of ORA-14155 after all googling (2 threads merged by bb) [message #661212 is a reply to message #661195] Fri, 10 March 2017 10:48 Go to previous message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
EdStevens wrote on Fri, 10 March 2017 13:21
billgates wrote on Fri, 10 March 2017 06:09
Is this the only solution to move onto 10g at least from 9i?
I'd think you'd want to move to a version that is supported . . . or at least was supported at some time in this millenium.
...

Worse, he is running the worst version Oracle has ever released.

Column renaming was introduced in 9.2.

Report message to a moderator

Previous Topic: FOR UPDATE NOWAIT and ORA-29285: file write error
Next Topic: Assistance in Query Construction
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 01:13:10 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.