SQL prefix for multiple insert/update

Question

I have prepared one script that updates/insert new values into few tables. Instead of using hardcoded values, I want to use prefix that will allow me to refer to this schema as variable.

Example as per below. Instead of using SKC I want to use for example value like abc

insert into SKC.type_table (TYPE_CD, STATUS_CD, CREATED_BY, CREATION_DATE, UPD_USER_ID, LAST_UPDATE_DATE)


insert into SKC.type_df (TYPE_CD, LANGUAGE_CD, TYPE_DESCRIPTION, CREATED_BY, CREATION_DATE, UPD_USER_ID, LAST_UPDATE_DATE)

Do you know all the possible target tables upfront? In standard SQL identifiers should be known before the actual query execution, because Oracle [builds the execution plan](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/sql.html#GUID-1B95E60C-99C5-446D-9C6B-5D16EFE59ACF) and does semantic check. So you have to prepare your statements somwhere outside the execution context: be it external language or PL/SQL block with `execute immediate` doesn't really matter. — astentx, Aug 10 '21 at 10:15
Yes I actually do. Script does have all table names upfront. — matmen, Aug 10 '21 at 10:28

score 0 · Answer 1 · answered Aug 10 '21 at 10:39

If you know all the table names upfront, you may use multitable insert:

create table templ (
  id int
);

create procedure generate_tabs(num int)
as
begin
  for i in 1..num loop
    execute immediate 'create table t_' || to_char(i, 'TM9')
    || ' as select * from templ where 1 = 0';
  
  end loop;
end;/

begin
  generate_tabs(5);
end;/

insert all
  when tab_name = 1 then into t_1
    values(val)
  when tab_name = 2 then into t_2
    values(val)
  when tab_name = 3 then into t_3
    values(val)
  when tab_name = 4 then into t_4
    values(val)
  when tab_name = 5 then into t_5
    values(val)
select
  level as tab_name,
  level as val
from dual
connect by level < 5

select 't_1' as src, t_1.*
from t_1
union all
select 't_2' as src, t_2.*
from t_2
union all
select 't_3' as src, t_3.*
from t_3
union all
select 't_4' as src, t_4.*
from t_4
union all
select 't_5' as src, t_5.*
from t_5

SRC | ID
:-- | -:
t_1 |  1
t_2 |  2
t_3 |  3
t_4 |  4

db<>fiddle here

score 0 · Answer 2 · answered Aug 11 '21 at 02:42

Remove all schema names from your script and set the default schema name before running the script. For example, if you're running your scripts in SQL*Plus:

alter session set current_schema = abc;
@your_script.sql
alter session set current_schema = skc;
@your_script.sql

SQL prefix for multiple insert/update

2 Answers2