Re: Transact SQL to PL/SQL question

"Rick Film" <rfilm_at_nycap.rr.com> wrote in message news:<csG88.49249$dh.12554729_at_typhoon.nyroc.rr.com>...
> I'm just learning PL/SQL. Have been using T-SQL for a while.
> Many procedures I write require that I check for existence of a table first.
> If it exists, I must drop it prior to creating a new one with the same name.
> T_SQL: IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'tablename')
> DROP TABLE tablename
>
> I want to do the same thing with PL/SQL. What would be the standard way
> this would be accomplished? I would expect that I would query the
> DBA_TABLES, but I think I have the syntax wrong.

You can do this by querying the oracle metadata, but in Oracle in the instance you are citing it is far simpler to use the "Create or Replace table....." syntax which drops the table first if it already exists.

If you still want the metadata for other purposes try looking at the view/table USER_TABLES, remember that Oracle searches are case sensitive so TABLE_NAME='tablename' would not normally work, unless the table had been created in lower-case and double quotes; you normally need TABLE_NAME='TABLENAME'

Regards

Tom Received on Fri Feb 08 2002 - 16:05:02 CET