CODEWITHSUNDEEP
sqloracle-databaseplsql
andreww
andreww

Reputation: 223

If table doesn't exists then insert into other table

I have procedure like:

create or replace procedure smth
is
some_variable varchar(2;
begin

insert into **table**(col1,col2) (Select id,'text' from TABLE2),
insert into table(col1,col2) (Select id,'text' from TABLE3)

What I want to do is put some if-else (or smoething else) statement for the case when eg. Table2 doesn't exists. If Table2 doesn't exist then insert into table ('smth','nameoftable') from DUAL. I know exactly what is the name of the table2. ( but for some reason this table could be deleted from database).

It would be great if I could do that this way:

insert into **table**(col1,col2) if table2 doesn't exist then Select (1,'TABLE2') else (Select id,'text' from TABLE2),

if table3 doesn't exist then Select (1,'TABLE3') insert into table(col1,col2)  else (Select id,'text' from TABLE3)

For each case.

EDIT

 insert into **table**(col1,col2) (Select id,'text' from TABLE2),
    insert into table(col1,col2) (Select id,'text' from TABLE3)
    insert into table(col1,col2) (Select id,'text' from TABLE4)
    insert into table(col1,col2) (Select id,'text' from TABLE4)

Assume that table3 doesn't EXIST, then all what I want is INSERT into table ( Select 'text','text2' from dual.

Upvotes: 2

Views: 1047

Answers (3)

Giovanni
Giovanni

Reputation: 4015

In order to get waht you want you need two things

  1. Find if the table exists: you can query the user_tables against the table_name column if the procedure is reading form the table in the same schema
  2. you need to use dynamic sql because if the table does not exists with static pl/sql you get the error PL/SQL: ORA-00942: table or view does not exists because when oracle compiles the procedure it does not find the table.

An example of dynamic plsql is

 sql_stmt := 'insert into table(col1,col2) (Select id,''text'' from ' || <the right table_name> || ')';
 EXECUTE IMMEDIATE sql_stmt;

Here is a link to the oracle documentation for dynamic sql.

EDIT After you clarification can end with a procedure like this one:

  create or replace procedure insert_from_dual_if_not_exists(table_name_in in varchar2)    
    begin
    .....
    if table_exists('<table_name>') then
    sql_stmt := 'insert into table(col1,col2)' (Select id,''text'' from ' || <the right table_name> || ')';
    else
    sql_stmt := 'insert into table(col1,col2)' (Select ''text1'',''text2'' from dual )';
    end if;
    EXECUTE IMMEDIATE sql_stmt;
    end;

and call insert_from_dual_if_not_exists instead of you simple insert; you must also create a procedure (or a simple statement) that telle your code if a table exists.

Upvotes: 3

arminrock
arminrock

Reputation: 535

You can try this kind of procedure executing dynamic sql that contains anonymouse block and catching exception with sqlcode 

create or replace procedure testproc
is 
v_sql varchar2(4000) := q'[
declare
begin
 insert into table3 values(1,2);
end;]';
begin
execute immediate v_sql;
exception when others
  then if
sqlcode='-6550' then 
 insert into **table** values()
end if;
 end testproc;

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could query the USER_TABLES view to see whether the table actually exists or not. And, you must (ab)use EXECUTE IMMEDIATE to execute the dynamic sql.

For example, 

SELECT COUNT(*) 
INTO   v_cnt 
FROM   USER_TABLES 
WHERE  TABLE_NAME = '<TABLE_1>';

v_sql := INSERT INTO TABLE(col1,col2).. SELECT id,'text'  FROM ';

IF v_cnt > 0
THEN
   v:sql := v_sql || TABLE_1;
   EXECUTE IMMEDIATE v_sql;
ELSE
   v:sql := v_sql || TABLE_2;
   EXECUTE IMMEDIATE v_sql;
END IF;

UPDATE OP wants to dynamically use the table_name to insert into another table from multiple tables.

Loop through all the tables, and use the table_name as a variable in the dynamic sql.

For example,

FOR i IN SELECT table_name FROM user_tables WHERE table_name <> 'inserting_table'
LOOP
   v_sql := 'INSERT INTO inserting_table SELECT column_list FROM' || i.table_name;
   EXECUTE IMMEDIATE v_sql;
END LOOP;

Upvotes: 1

Related Questions