Drop and Create table if exist if not then Create in oracle Procedure

Question

I have a query that takes start and end year for data also it takes a name of a table as procedure parameters..

the query then will create a table with that name if it doesn't exist or if exist it will drop and recreate it

    CREATE OR REPLACE Procedure USE_RAWDATA
   ( START_RP IN NUMBER, END_RP IN NUMBER,TABLE_NAME varchar)
IS
v_listStr CLOB;
DAT VARCHAR(500):=to_char(to_date(SYSDATE), 'yyyymmdd');
cnt NUMBER;
BEGIN 
 BEGIN
   SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = TABLE_NAME||'_'||DAT;
   dbms_output.put_line(TABLE_NAME||'_'||DAT);
   dbms_output.put_line(cnt);
 IF (cnt) = 1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE '||TABLE_NAME||'_'||DAT;
   dbms_output.put_line('DROP TABLE '||TABLE_NAME||'_'||DAT);
END IF;
END;
    select rtrim(xmlagg(xmlelement(e,column_name,', ').extract('//text()') order by column_id).getclobval(),', ') x INTO v_listStr from RAW_DATA_METADATA WHERE (START_ROUND<= END_RP and END_ROUND is NULL) or (START_ROUND >= START_RP and END_ROUND <= END_RP );
    EXECUTE IMMEDIATE 'CREATE TABLE '||TABLE_NAME||'_'||DAT||' AS SELECT '||v_listStr ||' FROM RAW_DATA WHERE ROUND_ID BETWEEN '||START_RP ||' AND '||END_RP;
END;
/

my problem is that cnt is always giving me 0 even the table exists then the procedure will end with an error table already exist... I don't know why .. since when I try it as PL\SQL query it gives me a correct result cnt =1 if exist

declare
v_listStr CLOB;
DAT VARCHAR(500):=to_char(to_date(SYSDATE), 'yyyymmdd');
cnt NUMBER;
BEGIN 
 BEGIN
   SELECT COUNT(*) INTO cnt FROM user_tables WHERE :TABLE_NAME = :TABLE_NAME||'_'||DAT;
   dbms_output.put_line(:TABLE_NAME||'_'||DAT);
   dbms_output.put_line(cnt);
 IF (cnt) = 1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE '||:TABLE_NAME||'_'||DAT;
   dbms_output.put_line('DROP TABLE '||:TABLE_NAME||'_'||DAT);
END IF;
END;
    select rtrim(xmlagg(xmlelement(e,column_name,', ').extract('//text()') order by column_id).getclobval(),', ') x INTO v_listStr from RAW_DATA_METADATA WHERE (START_ROUND<= :END_RP and END_ROUND is NULL) or (START_ROUND >= :START_RP and END_ROUND <= :END_RP );
    EXECUTE IMMEDIATE 'CREATE TABLE '||:TABLE_NAME||'_'||DAT||' AS SELECT '||v_listStr ||' FROM RAW_DATA WHERE ROUND_ID BETWEEN '||:START_RP ||' AND '||:END_RP;
END;
/

can you help me figure out why it always cnt gives 0 even the table is exist

Some questions: 1) why are you using to_date on sysdate, when sysdate is already a DATE? Don't do that. That's a hidden bug waiting to happen! to_char(sysdate, 'yyyymmdd') would do the trick. 2) What do you mean by "PL/SQL query"? That's not a thing; did you mean when you run the select statement on its own as a standalone SQL statement, or did you mean running the procedure as an anonymous PL/SQL block? 3) What happens if you change the code to always attempt to drop the table and handle the exception where it doesn't exist? Does the create table still fail? — Boneist
– Boneist, Commented May 20, 2021 at 18:23
thank you for the respond 1) it seems something that I missed out ... thanks for the note 2) what I mean when I run the query using a (declare,begin and used :parameter) this give a right result instead of (stored procedure and its parameter) ...(declare,begin and used :parameter) is what I mentioned in the second block of code vs the first block 3) can I tell it to create the table if it doesn't exist.. can you help me to do it? — Radi Soufan
– Radi Soufan, Commented May 20, 2021 at 19:10

William Robertson · Accepted Answer · 2021-05-23 11:05:54Z

3

This query can only ever return 0:

select count(*) into cnt
from   user_tables
where  table_name = table_name || '_' || dat;

You need to either prefix table_name with the procedure name

select count(*) into cnt
from   user_tables
where  table_name = use_rawdata.table_name || '_' || dat;

or else name your parameters differently (tableName, p_table_name etc).

I'd also suggest removing the first begin and end as they aren't doing anything, and adjusting your indentation to reflect the code structure more accurately.

I make it something like this:

create or replace procedure use_rawdata
    ( start_rp   in number
    , end_rp     in number
    , table_name varchar2 )
is
    v_liststr clob;
    dat  varchar2(8) := to_char(sysdate, 'yyyymmdd');
    cnt  number;
begin
    select count(*) into cnt
    from   user_tables
    where  table_name = upper(use_rawdata.table_name) || '_' || dat;

    dbms_output.put_line(table_name || '_' || dat);
    dbms_output.put_line(cnt);

    if cnt = 1 then
        execute immediate 'DROP TABLE ' || table_name || '_' || dat;
        dbms_output.put_line('DROP TABLE ' || table_name || '_' || dat);
    end if;

    select rtrim(xmlagg(xmlelement(e, column_name, ', ').extract('//text()') order by column_id).getclobval(), ', ') x
    into   v_liststr
    from   raw_data_metadata
    where  (start_round <= end_rp and end_round is null)
    or     (start_round >= start_rp and end_round <= end_rp);

    execute immediate 'create table ' || table_name || '_' || dat || ' as select ' || v_liststr || ' from raw_data where round_id between ' || start_rp || ' and ' || end_rp;
end;

edited May 23, 2021 at 11:05

answered May 20, 2021 at 19:40

William Robertson

16.1k4 gold badges41 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Belayer Over a year ago

Additionally, you should use upper(use_rawdata.table_name) || '_' || dat;. Keep in mind that when selecting from data dictionary views the table names are data values, not object names, and therefore are case sensitive. Using upper protects you from entering a lower case value for the table name, and the resulting headaches when it fails.

Collectives™ on Stack Overflow

Drop and Create table if exist if not then Create in oracle Procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related