bin bin - 2 months ago 10
SQL Question

Change/replace the value of a variable dynamically- oracle

I have set of sql statements (create table, views, sequences), Where my schema name changes all the time and rest of the sql be the same.
Even in schema name a part of it has to change, for example:
I have a

schema
name
ABC_XYZ
, i would like to change this schema name to
ABC_DEF_XYZ
.
For this i tried to insert a variable in the schema name like
ABC_&var1_XYZ
. If i pass the variable in the schema name as shown here (
ABC_&var1_XYZ
) doesn't ask for user prompt.
I tried using escape characters and all but didn't work.
here is my create statement:

set echo off
set verify off
undefine Relowner
BEGIN
begin EXECUTE IMMEDIATE 'DROP TABLE RDS_&Relowner_Owner.Test CASCADE CONSTRAINTS PURGE'; EXCEPTION when others then if (SQLCODE != -2441 and SQLCODE != -942) then RAISE; end if; end;
END;
/
CREATE TABLE ABC_&Relowner_XYZ.Test
(
employee_id NUMBER(19) NOT NULL,
employee_name VARCHAR2(50) NOT NULL,
CONSTRAINT Test_pk PRIMARY KEY (employee_id)
);


Is there any other way i can try to insert my value in this schema.

Answer

You can use dynamic SQL to handle a varying schema in your DDL; for example:

accept mySchemaPart PROMPT "Schema part: "
declare
    vMySchema varchar2(30) := 'ABC_' || '&mySchemaPart' || '_XYZ';
    vSQL      varchar2(32767);
begin
   vSQL := 'create table ' || vMySchema || '.tableName (...)';
   execute immediate vSQL;
end;
/