Link to home
Start Free TrialLog in
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.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My preference would be to drop the object and trap the error.  That way you only make one call to the database.
Avatar of Sean Stuber
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)
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.
hmmm, fair enough, the code and the question don't coincide.
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.
Avatar of IT_ETL

ASKER

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.
Avatar of IT_ETL

ASKER

"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.
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
'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.
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
Avatar of IT_ETL

ASKER

"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;
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.
Avatar of IT_ETL

ASKER

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