Re: What kind of boolean/conditionals can I evaluate in SQL/PL?

On 25 May 2002 11:54:30 -0700, ramon_at_conexus.net (Ramon F Herrera) wrote:

>Some of the conditional statements are pretty obvious and well
>documented, like "select * from table where day='1-may-2002'",
>but I would like to know what other boolean expressions can
>be evaluated. For instance: how do I "say" this in SQL/PL:
>
> if (table 'payroll' exists) then do whatever
> if (there is an index on table xyz) then drop index
>
>Some of my scripts fail because they are trying to do a
>select on a group of tables (some of which may not exist),
>or because the script is trying to drop an index which
>has been dropped already.

That's usually a 2-step process, with the first step being to query the data dictionary:

declare
table_exists number;
begin
select count(*) into table_exists from user_tables where table_name = 'MY_TABLE' and rownum <= 1;
-- Now table_exists is either 0 or 1 and you can use regular comparison statements.

You can get details on all the data dictionary views here:

http://tahiti.oracle.com/pls/db92/db92.catalog_views (9i) http://tahiti.oracle.com/pls/tahiti/tahiti.catalog_views (8i)

and the ones relevant to things like this are listed in the chapters about "schema objects" in the Administrator's Guide and Application Developer's Guide - Fundamentals.

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/

The opinions expressed above are mine and do not
necessarily reflect those of Oracle Corporation.