Avatar of IT_ETL
IT_ETL asked on

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.
Oracle DatabaseSQL

Avatar of undefined
Last Comment
johnsone

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
johnsone

My preference would be to drop the object and trap the error.  That way you only make one call to the database.
Sean Stuber

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)
johnsone

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Sean Stuber

hmmm, fair enough, the code and the question don't coincide.
johnsone

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.
ASKER
IT_ETL

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
IT_ETL

"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.
Sean Stuber

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
Sean Stuber

'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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sean Stuber

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
ASKER
IT_ETL

"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;
johnsone

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
IT_ETL

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.
johnsone

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.