Re: Need to determine if a column exists or not

Oops, I pasted to wrong script. This is correct.

DECLARE
  fldCount NUMBER(5);
BEGIN
  SELECT COUNT(COLUMN_NAME) INTO fldCount 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;
/

"Al Anderson" <aanderson_at_lillysoftware.com> wrote in message news:ur0ea1og53n947_at_corp.supernews.com...
> 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 - 15:53:39 CDT