Re: How to check the existance of a table ?

From: Robert Vabo <robert.vabo_at_gecko.no>
Date: Fri, 01 Sep 2000 06:00:48 GMT
Message-ID: <kCHr5.12559$541.2591337_at_juliett.dax.net>


Thanks a lot for the help. It works fine.

Another "freshman" question.

I'm going to test if the table exists and if not I am going to create it.

bolExist = sp_TableExist(TempTableName)
if bolExist = False
THEN
  CREATE TABLE || TempTableName || || ' (ID_COL int IDENTITY, INPUT_ID int ) '; (Where ID_COL is a autonumber)
END IF; Robert :-)

"Valentin Mînzatu" <mvali_at_rds.ro> skrev i melding news:7Trr5.100$bo5.3665_at_nreader1.kpnqwest.net...
> Try this:
>
> FUNCTION sp_TableExist( tbl IN user_tables.table_name%TYPE )
> RETURN BOOLEAN IS
> CURSOR c_user_tables IS
> SELECT 1
> FROM user_tables
> WHERE table_name = UPPER(NVL(tbl, ''))
> AND ROWNUM < 2;
> BEGIN
> FOR c IN c_user_tables
> LOOP
> RETURN TRUE;
> END LOOP;
> RETURN FALSE;
> END;
>
> The boolean type of return may be any other type ...
> --
> Vali
>
>
> Robert Vabo <robert.vabo_at_gecko.no> wrote in message
> news:2npr5.12195$541.2514526_at_juliett.dax.net...
> > I want tocheck if a table exist in the database via a SP.
> >
> > Procedure SP_TableExist
> > ( TableName varchar2)
> > IS
> > if exists(select * from User_Objects WHERE Object_Name = || TableName)
> > return 1;
> > else
> > return 0;
> > end if;
> > end SP_TableExist;
> >
> > Any suggestions ?
> >
> > --
> > Regards
> > Robert Vabo
> > Application developer
> > Gecko Informasjonssystemer AS
> > www.gecko.no
> > robert.vabo_at_gecko.no
> >
> >
>
>
Received on Fri Sep 01 2000 - 08:00:48 CEST

Original text of this message