sql - Change/replace the value of a variable dynamically- oracle -

i have set of sql statements (create table, views, sequences), schema name changes time , rest of sql same. in schema name part of has change, example: have schema name abc_xyz, change schema name abc_def_xyz. tried insert variable in schema name abc_&var1_xyz. if pass variable in schema name shown here (abc_&var1_xyz) , pass value variable, ask me declare vaue of variable. have given error , code use below:

error report - ora-06550: line 5, column 52: pls-00201: identifier 'rel4' must declared ora-06550: line 5, column 1: pl/sql: statement ignored ora-06550: line 7, column 51: pls-00201: identifier 'rel4' must declared 

here create statement:

   set echo off set verify off undefine  myschemapart declare      vsql      varchar2(32767); begin  vsql:= 'begin execute immediate alter table abc_'||&&myschemapart||'_owner.test drop constraint employee_id_fk; exception when others if (sqlcode != -02443 , sqlcode != -942) raise; end if; end';  execute immediate vsql; vsql:= 'begin execute immediate drop table abc_'||&myschemapart||'_owner.test cascade constraints purge; exception when others if sqlcode != -942 raise; end if; end';  execute immediate vsql; vsql:= 'create table abc_'||&myschemapart||'owner.test  (    employee_id     number(19)           not null,    last_updt_dt         date                 not null,    )';     execute immediate vsql; vsql:= 'create or replace synonym abc_user.test abc_'||&myschemapart||'owner.test';  execute immediate vsql; vsql:= 'begin execute immediate alter table abc_'||&myschemapart||'owner.test    add constraint employee_id_fk foreign key (employee_id)       references abc_owner.gdsd (employee_id); exception when others if (sqlcode != -02443 , sqlcode != -02275) raise; end if; end';  execute immediate vsql; end; / 

is there other way can try insert value in schema.

pass 'abc_||'&var1||'_xyz'

an example of insert statement;

insert test (a)  select 'abc_'||&var1||'_xyz' dual ; 

on execution ask prompt.

as block can use this:

declare  var varchar2(100); begin  var:='create table  abc_'||&var1||'_xyz.tablnam(a number)'; --add columns  dbms_output.put_line(var);   execute immediate var;  end; 

as per @bin request his/her statments:

declare  var varchar2(500); begin  var:='create table abc_'||&var||'_owner.test(employee_id  number(19) not null, last_updt_dt  date  not null )';  --table created dbms_output.put_line(var); execute immediate var;  var:='alter table abc_'||&var1||'_owner.test add constraint employee_id_fk foreign key (employee_id) references abc_owner.gdsd(employee_id)';  --constraint added dbms_output.put_line(var); execute immediate var;   var:='create or replace synonym abc_user.test abc_'||&var1||'_owner.test';  --synonym  created dbms_output.put_line(var); execute immediate var;   var:='alter table abc_'||&var1||'_owner.test drop constraint employee_id_fk';  --constraint dropped dbms_output.put_line(var); execute immediate var;   var:='drop table abc_'||&var1||'_owner.test cascade constraints purge';  --table dropped dbms_output.put_line(var); execute immediate var;   exception  when others   dbms_output.put_line('error--'||sqlerrm||'-'||sqlcode);   end; 

output:

sql> declare      var varchar2(100);      begin      var:='create table  abc_'||&var1||'owner.test(employee_id  number(19) not null, last_updt_dt  date  not null )';  '   '  ' dbms_output.put_line(var);   execute immediate var;  end;  enter value var1: 'rel4'      create table abc_rel4_owner.test(employee_id  number(19) not null, last_updt_dt  date  not null )     table created.      alter table abc_rel4_owner.test add constraint employee_id_fk foreign key (employee_id) references abc_owner.gdsd(employee_id)      table altered.      create or replace synonym abc_user.test abc_rel4_owner.test      synonym created.      alter table abc_rel4_owner.test drop constraint employee_id_fk      table altered.      drop table abc_rel4_owner.test cascade constraints purge      table dropped.