Re: Need to determine if a column exists or not

Hi All,

Sorry that the From in my post does not say my name.

Thanks for all of the input!

For those interested, here is the solution that I came to. It seems to work fine.(However, I am going to brush up on Oracle Script Error handling. And play with TurkBear's route)

DECLARE
  fldCount NUMBER(5);
BEGIN
  SELECT COUNT(COLUMN_NAME) INTO xx FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MYTABLE' AND COLUMN_NAME = 'MYCOLUMN';
  IF ( fldCount != 1) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MYTABLE ADD MYCOLUMN NUMBER(6,3) NULL';   END IF;
END;
/

"news.integraonline.com" <aanderson_at_lillysoftware.com> wrote in message news:uqud2qd1o2pab9_at_corp.supernews.com...
> I am in a situation where I need to determine is a column exists in a
> database or not.
>
> Does anyone know a way to do this.
>
>
> If you are asking the "Why would one need to do this!", that I have seen
in
> response to others ?'s that are like this.
>
> I will explain.
>
> Overview: I have an application that executes scripts using the Oracle
OLEDB
> provider, the application queries a database, and determines what changes
> need to be made to a customers database. It then executes a SQL script
based
> on the determination made.
>
> Problem: Somewhere in the past a bad version of a script file was
> distributed to an undetermined amount of customers, this file did NOT add
a
> column X to the database. So we are left with some customers that DO have
> column X and some that DON'T.
>
> We can't just drop column X for the ones that already have it, because
they
> have data in the field. And creating temp tables seems like a pain.
>
> Resolution(hopefully): Find a way to do determine if the column exists...
If
> it does not we need to add it.
> Example (not intended to be Oracle Syntax),
> If ColumnX.Exists Then
> Alter table TableName Add X
> End If
>
> If we can do this, everyone is happy. And we don't have some customers
with,
> and some without.
>
>
Received on Fri Oct 18 2002 - 11:37:09 CDT