Home » SQL & PL/SQL » SQL & PL/SQL » Oracle9i (conversion)
Oracle9i [message #436995] Wed, 30 December 2009 04:32 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
How can we convert LONG (or) LOB data type columns of a table to NUMBER data type.
Please help me
Re: Oracle9i [message #436998 is a reply to message #436995] Wed, 30 December 2009 04:42 Go to previous messageGo to next message
Mohsin_Tm
Messages: 15
Registered: December 2009
Location: Pune
Junior Member
Hi you can uses alter table command

Ex: alter table <Table name> modify <Colunm name> <Data type>
Re: Oracle9i [message #436999 is a reply to message #436995] Wed, 30 December 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot directly modify from LONG or CLOB to NUMBER:
SQL> create table t (c1 clob, c2 long);

Table created.

SQL> alter table t modify (c1 number, c2 number);
alter table t modify (c1 number, c2 number)
                      *
ERROR at line 1:
ORA-22859: invalid modification of columns

You have to create new columns and drop old ones.
Or better create a new table and if the old one is not empty move the data into it.
This move can be done directly with CLOB datatype using TO_NUMBER but you must go for a PL/SQL procedure for LONG one.

Regards
Michel
Re: Oracle9i [message #437000 is a reply to message #436999] Wed, 30 December 2009 04:56 Go to previous messageGo to next message
Mohsin_Tm
Messages: 15
Registered: December 2009
Location: Pune
Junior Member
Hi Michel,

I have tried and able to change long to number.

Please look below for example and update if anything is wrong.

SQL> create table test123 (a long);

Table created.

SQL> desc test123
Name Null? Type
----------------------------------------- -------- ----------------------------
A LONG

SQL> alter table test123 modify a number;

Table altered.

SQL> desc test123
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL>
Re: Oracle9i [message #437001 is a reply to message #437000] Wed, 30 December 2009 05:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You are required to learn 'How to post to this forum'.
Use CODE tag for showing coding.

regards,
Delna
Re: Oracle9i [message #437002 is a reply to message #437001] Wed, 30 December 2009 05:05 Go to previous messageGo to next message
Mohsin_Tm
Messages: 15
Registered: December 2009
Location: Pune
Junior Member
Sorry delna I will remember next time

[Updated on: Wed, 30 December 2009 05:05]

Report message to a moderator

Re: Oracle9i [message #437009 is a reply to message #437000] Wed, 30 December 2009 05:09 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
I have tried and able to change long to number.


And what about LOB?

regards,
Delna
Re: Oracle9i [message #437011 is a reply to message #437000] Wed, 30 December 2009 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is possible to change LONG to NUMBER (sorry about that) but not for CLOB.
Note that it is possible only if there is no value in this column.

Regards
Michel
Re: Oracle9i [message #437012 is a reply to message #437002] Wed, 30 December 2009 05:22 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Nothing to say sorry.
Everybody here is human being, and mistakes are done by them...
But it should not be repeated.

One more thing,
SQL>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

SQL>create table t12
  2  (col1 long);

Table created.

SQL>insert into t12 values('1234');

1 row created.

SQL>alter table t12 modify col1 number;
alter table t12 modify col1 number
                       *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


regards,
Delna
Re: Oracle9i [message #437013 is a reply to message #437011] Wed, 30 December 2009 05:24 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Oh...
I repeated same thing, but I didn't found Michel sir's post when I clicked on 'Submit Reply'.

regards,
Delna

[Updated on: Wed, 30 December 2009 05:25]

Report message to a moderator

Previous Topic: need procedure
Next Topic: display column to row
Goto Forum:
  


Current Time: Sun Dec 11 04:11:52 CST 2016

Total time taken to generate the page: 0.07601 seconds