How to create a procedure in an oracle sql script and use it inside the script?

Question

I want to create a script for my oracle DB, which drops tables. If the table does not exist, the script won't exit as fail, just print a text: "does not exists".

The script is the following:

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE mytable';
    DBMS_Output.Put_Line(' table dropped');
EXCEPTION WHEN OTHERS THEN
    IF SQLCODE = -942 THEN
        DBMS_Output.Put_Line(' table not exists');
    ELSE
        DBMS_Output.Put_Line(' Unknown exception while dropping table');
        RAISE;
    END IF;
END;

I want to drop a lot of table in one script, and I don't want to write these lines more than once.

Is there any way, to write it to a procedure or function which gets a parameter (the name of the table), and call this procedure in that script?

Maybe something like this:

drop_table_procedure('mytableA');
drop_table_procedure('mytableB');

Or maybe a procedure, which gets an undefined size list (like in java: String ... table names):

drop_tables_procedure('mytableA','mytableB');

Please give me some examples. Thanks!

Create a procedure and pass the table_name as a parameter to it. And then in an anonymous block, execute all the procedures. — Lalit Kumar B
– Lalit Kumar B, Commented Nov 18, 2015 at 10:04

user330315 · Accepted Answer · 2015-11-18 10:02:09Z

15

Yes, you can declare a "temporary" procedure in an anonymous PL/SQL block:

DECLARE 

  PROCEDURE drop_if_exists(p_tablename VARCHAR)
  IS
  BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE '||p_tablename;
      DBMS_Output.Put_Line(' table dropped');
  EXCEPTION WHEN OTHERS THEN
      IF SQLCODE = -942 THEN
          DBMS_Output.Put_Line(' table not exists');
      ELSE
          DBMS_Output.Put_Line(' Unknown exception while dropping table');
          RAISE;
      END IF;
  END;

BEGIN
  drop_if_exists('TABLE_1');
  drop_if_exists('TABLE_2');
END;
/

answered Nov 18, 2015 at 10:02

user330315

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

Comments

are · Accepted Answer · 2015-11-18 10:05:16Z

1

in execute immediate you need add name of database object. here's the script

create table t1 (col1 int);
create table t2 (col1 int);

create procedure drop_my_table(av_name varchar2)
as
begin
    EXECUTE IMMEDIATE 'DROP TABLE '||av_name;
    DBMS_Output.Put_Line(' table dropped');
EXCEPTION WHEN OTHERS THEN
    IF SQLCODE = -942 THEN
        DBMS_Output.Put_Line(' table not exists');
    ELSE
        DBMS_Output.Put_Line(' Unknown exception while dropping table');
        RAISE;
    END IF;

end drop_my_table;

declare
  type array_t is varray(2) of varchar2(30);
  atbls array_t := array_t('t1', 't2');
begin
  for i in 1..atbls.count loop
       drop_my_table(atbls(i));
   end loop; 
end;

answered Nov 18, 2015 at 10:05

are

2,6153 gold badges24 silver badges27 bronze badges

Comments

Tharunkumar Reddy · Accepted Answer · 2015-11-18 10:07:12Z

1

You can use below one also

create or replace PROCEDURE drop_if_exists(p_tablename in VARCHAR)
      IS
    v_var1 number;
    begin 
    select 1 into v_var1 from user_tables where table_name=upper(p_tablename);
    if v_var1=1
    then 
    EXECUTE IMMEDIATE 'DROP TABLE '||p_tablename;
    DBMS_Output.Put_Line(' table dropped');
    else
    DBMS_Output.Put_Line(' table not exist');
    end if;
    exception 
    when others then
     DBMS_Output.Put_Line(' Unknown exception while dropping table');
            RAISE;
    end;

Call procedure

    begin 
    drop_if_exists('emp');
    end;
    /

answered Nov 18, 2015 at 10:07

Tharunkumar Reddy

2,81320 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to create a procedure in an oracle sql script and use it inside the script?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related