How to check if a database objects exist before creating a new one in Oracle?

Is there any similar sql statement in Oracle just like one below in SQL Server?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BU_STATE]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[BU_STATE]

If so then please advise.

Basically, I like to run a sql statement to drop a table or any database objects (for example function, procedure, view, package) if it exists in Oracle Database. If database objects do not exist then sql statement will run without any error.
IT_ETLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
select 1 from dba_objects where object_name = 'BU_STATE'  and owner = 'DBO'

if you're only interested in looking up tables

select 1 from dba_tables where table_name = 'BU_STATE' and owner = 'DBO'


alternately,  you can simply try to create the object and capture the exception which would should be more efficient, not that the either of the above queries will be too bad.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
My preference would be to drop the object and trap the error.  That way you only make one call to the database.
0
sdstuberCommented:
My preference would be to drop the object

that's sort of dangerous given that the question is trying to "create" an object.
If one is already there, they don't want to create a new one.    If the first step is always a drop then they'll always be forced to create a new one (or restore the old one if it had value)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
The code in the original post is drop it if it exists.

It is not create it if it doesn't exist.

Based on the code in the original post, I would just do the drop and trap the error.  Although, depending on the usage, a global temporary table might be a better solution, but that is not what was being asked.
0
sdstuberCommented:
hmmm, fair enough, the code and the question don't coincide.
0
johnsoneSenior Oracle DBACommented:
If the question is the other way around (i.e. create it if it does not exist), then I would just create the table and trap the already exists error.  Still no reason to go through the dictionary twice to see if it is there, the check is built in.  Just be sure that you are checking for the correct error to come back and not ignoring all errors.
0
IT_ETLAuthor Commented:
The purpose is to check whether database objects (tables) exist on a specific schema, if it exists then drop that object and go to the next step otherwise do nothing and go to the next step in a PL/SQL stored procedure. Sample PL/SQL code is below,

create or replace procedure dly_web_test as

    g_stmt      varchar2(8000);
    v_obj_cnt   number;
   
begin

    select 1
    into v_obj_cnt
    from dba_objects
    where object_name = 'TMP_TLF_APP_DATA_STG'
    and owner = 'WEBRPT';

    if (v_obj_cnt = 1) then
        g_stmt := 'drop table webrpt.tmp_tlf_app_data_stg';
        execute immediate (g_stmt);
    end if;

    -- next step will start from here
   
end dly_web_test;
/

I am getting an error "PL/SQL: ORA-00942: table or view does not exist".

When I run below statement using an editor, I don't get an error,

select 1
--into v_obj_cnt
from dba_objects
where object_name = 'TMP_TLF_APP_DATA_STG'
and owner = 'WEBRPT';

But if I try to compile above stored proc then I get an error from above statement "table or view does not exist"

Is that a grant issue? How do I resolve this issue? Please advise.
0
IT_ETLAuthor Commented:
"Based on the code in the original post, I would just do the drop and trap the error.  Although, depending on the usage, a global temporary table might be a better solution, but that is not what was being asked. "

There is no reason to execute a drop statement if objects do not exist in a database as PL/SQL stored proce will fail. But if object does exist then I do want to execute a drop statement and go to the next step in a PL/SQL stored proc.
0
sdstuberCommented:
I am getting an error "PL/SQL: ORA-00942: table or view does not exist".


if you have privileges granted through a role (like DBA) those privileges don't apply when inside a procedure.

Grant select on dba_objects to your procedure owner
0
sdstuberCommented:
'drop table webrpt.tmp_tlf_app_data_stg';


based on the table name, I assume it is a staging table, and hence I agree with the previous post that a global temporary table would be better.

in sql server it is common and good practice to create and drop temporary tables on the fly.
In oracle that is NOT a good practice.  That's why global temporary tables exist.



however, if you do want to continue with a drop/recreate then the best option would be to attempt the drop and simply capture it.  Or,  if you expect the table will already be dropped.  Then attempt the create first, if it fails, then drop/recreate.
0
sdstuberCommented:
If you "really" want to do the drop method,  here's how to do that...

But again, this is NOT a good practice within an Oracle system.
If you are populating data, deleting and populating again.  
Use a global temporary table.

Do NOT create/drop/create



CREATE OR REPLACE PROCEDURE dly_web_test
AS
    g_stmt VARCHAR2(8000);
BEGIN
    DECLARE
        does_not_exist EXCEPTION;
        PRAGMA EXCEPTION_INIT(does_not_exist, -942);
    BEGIN
        g_stmt := 'drop table webrpt.tmp_tlf_app_data_stg';
        EXECUTE IMMEDIATE(g_stmt);
    EXCEPTION
        WHEN does_not_exist
        THEN
            NULL;
    END;

    -- next step will start from here


END dly_web_test;


also check with your dba if they want to use the recyclebin. If so, then you'll probably want to use the drop with purge.

again though, that's only if you pursue the drop method over real temp tables which would likely be a bad idea
0
IT_ETLAuthor Commented:
"But again, this is NOT a good practice within an Oracle system.
If you are populating data, deleting and populating again.  
Use a global temporary table.

Do NOT create/drop/create"

Thank you for quick response.

Just to clarify, I am not dropping stage table. I am running this stored proc as a user "WEBRPT". But I will keep this stage table on a different staging schema which is "ADWSTG" (since I am running this proc as a WEBRPT, so I can not create table on ADWSTG, option is to insert data from WEBRPT schema to ADWSTG schema)

So, here is the flow,

1) If stage table exists on WEBRPT schema then drop this table.
2) Create stage table on WEBRPT schema.
3) Delete data from pre-existing stage table on ADWSTG schema.
4) Insert stage data from WEBRPT schema to ADWSTG schema.
    (Daily stage data will be stored as a common data warehousing practice)
5) Drop stage table from WEBRPT schema.
6) ETL process goes through transformations.
7) Populate stage data from ADWSTG schema to WEBRPT schema (For example FACT    tables) based on data warehousing rules.
8) If there is any errors/exceptions ETL process will terminate immediately.

For restartability option,

I will again start from above step 1. But let's say during previous run, job failed in step 4. So, if I restart then I must need to drop stage table from WEBRPT schema since stage table already exists from previous run.

For Daily run,

No stage table exists on WEBRPT schema, so process will go through below portion of the code first, it means if stage table exists on WEBRPT schema then drop this table otherwise do nothing. This way job will not fail.

I should have explained in detail in my earlier post. I think it may have caused little confusion. But above flow hopefully will clear all confusion.

select 1
    into v_obj_cnt
    from dba_objects
    where object_name = 'TMP_TLF_APP_DATA_STG'
    and owner = 'WEBRPT';

    if (v_obj_cnt = 1) then
        g_stmt := 'drop table webrpt.tmp_tlf_app_data_stg';
        execute immediate (g_stmt);
    end if;
0
johnsoneSenior Oracle DBACommented:
Based on your description, I would certainly use a global temporary table(s) in the WEBRPT schema.

This eliminates the need for steps 1, 2 and 5.  It also has no issue with restart as the table wouldn't exist outside the session.
0
IT_ETLAuthor Commented:
Global Temporary table could be a option. But it's not a single process, there are multiple processes, so we have to create bunch of global temporary tables.

Also, the way our database is organized as well as common data warehousing practice, we will keep stage tables in a staging schema.
0
johnsoneSenior Oracle DBACommented:
You only create on global temporary table.  Then each process has its own copy of that table.  That is all handled for you behind the scenes.  The structure of the table lives in the database so you only have to create it once.  However the storage and the data is transient and cannot be seen across sessions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.