• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4884
  • Last Modified:

Oracle Version, TOAD VI and Drop Column

Hi,

I'm trying to delete a column from an Oracle database... I know there's a SQL command to do this, but only for version 8i and newer.  So, my questions are:

1.- How can I retrieve the version of my Oracle DB using TOAD (you know, using only SQL)
2.- If my version is older than 8i, how do I drop the column

That's about it.
Thanks!

TCV
0
Taconvino
Asked:
Taconvino
  • 6
  • 5
1 Solution
 
seazodiacCommented:
1. that will be :

select * from v$version;


2. alter table <table_name> drop column <column_name>;

0
 
TaconvinoAuthor Commented:
OK! Now I got the version, but when I run the Alter, I get this error message:

The following error has occurred:

ORA-00905: missing keyword

And leaves the word "column" selected.
So, I guess the command doesn't work for Oracle8 Release 8.0.4.0.0.  Please, any other help would be great!

TCV
0
 
seazodiacCommented:
as you said, the drop column does not work for oracle8.

the workaround is to recreate :

create table <new_table_name> as
select col1, col2, ...coln          --DON'T include the column-to-be-dropped
from <table_name>;


additionally, you have to put back in all the constraints and indexes, sequences and synonyms;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
seazodiacCommented:
There is another way:

you can create a VIEW on top of that table:

create view <new_view_name>
as
select col1, col2, ....coln --Exclude the column-to-be-dropped
from <table_name>;


and direct users to use this view instead of this table.


I like this option better, because it does not disrupt your table structure and don't need to re-create all the constraints.
0
 
TaconvinoAuthor Commented:
So, I have to recreate the table with a new name, then drop the old table, and then rename the new table, right?  Does the rename command work for Oracle8?

TCV
0
 
seazodiacCommented:
Nope
0
 
TaconvinoAuthor Commented:
Hummm... that leaves me right in the start, right?  Any way, I can do the same process twice to get where I want.  The view could be better, under other circumstances... anyway, Thanks!

TCV
0
 
seazodiacCommented:
taconvino:

Rename table actually works in Oracle8 .It's getting rusty on me...I havenot used 8 for coon's age.
0
 
TaconvinoAuthor Commented:
You're kidding!  Can you post the correct syntax please?  Thanks!

TCV
0
 
seazodiacCommented:
easy:
just

rename <table_name> to <new_table_name>;


alter table <table_name> rename to <new_table_name>;
0
 
TaconvinoAuthor Commented:
I'll check this on monday.  Thanks again!

TCV
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now