Re: IF in PL/SQL-Block

"Caja" <carsten.jacobs_at_web.de> wrote in message news:1135687278.186346.282240_at_z14g2000cwz.googlegroups.com...
> Hi all,
>
> I have a simple problem but can't get it fixed.
> I want to execute an DELETE-statement only if a certain table (order)
> exists.
> The following PL/SQL-Block in an .sql-file runs always into the IF
> -body.
> What is wrong with it?
>
> DECLARE
> amount NUMBER;
> BEGIN
> amount := 0;
> -- only database, which includes the table 'order'
>
> SELECT count(*)
> INTO amount
> FROM all_tables
> WHERE lower(table_name) = 'order';
>
> IF amount = 1 THEN
> DELETE FROM order WHERE delivered = 0;
> COMMIT;
> END IF;
> END;
>
> Thanx for any help
>
> Carsten
>

1. don't name tables with keywords -- see the V$RESERVED_WORDS dynamic view
2. don't force table names to lower case (if you really do have table named 'order' in lower case)
3. you don't need to check for the existence of a table before issuing the DELETE statement -- if the table does not exist, your code won't compile (unless you use native dynamic sql, ie, EXECUTE IMMEDIATE)
4. ALL_TABLES lists all tables in the database (which includes multiple schemas) to which the current session has access -- not just tables owned by the current session (current user/schema). if the current session (current user/schema) does not have a table by that name, your code will still compile and run if some other schema owns a table by that name, the current session (current user/schema) has privilege on it, and there is a public or private synonym that exposes that table to the current session(u/s).

so, if any table by that name exists (and is accessible to the current schema), your code compiles, and you always get a value of 1 from your select statement and your if condition is always true

your comment indicates you may be submitting this statement to multiple 'databases' -- i suspect you mean schema, and are using SQL Server or MS Access terminology

if you are submitting this PL/SQL from a front-end application to multiple schemas, some of which do not have the table (which is a very suspicious design), all you need to do is submit the SQL statement (delete) and trap for a ORA-00922 error in your front-end -- you don't need PL/SQL

if you want your oracle code to work against schemas that don't have the table (not at all a good idea -- very strange code) without returning an exception to the front-end, you'll have to use PL/SQL native dynamic sql for the DELETE statement (however, i'm guessing that's probably not a great design)

++ mcs Received on Tue Dec 27 2005 - 07:39:28 CST