PL/SQL - Let the variable behave as source code

Question

I have a stored procedure in which I need to "switching" between 2 names of schemas. Here is an example:

declare
  schema1 varchar2(16) := 'left';
  schema2 varchar2(16) := 'right';
begin
  if (some condition) then
    select * from ???schema1???.tbl1 where id = 1;
  else
    select * from ???schema2???.tbl1 where id = 1;
  end if;
end;

How can I let the value of variable (e.g. schema1) behave as a part of source code? I mean that the code will be understood as:

...
  select * from left.tbl1 where id = 1;
else
  select * from right.tbl1 where id = 1;
...

SmartDumb · Accepted Answer · 2019-01-09 10:26:04Z

1

You would have to go with dynamic sql if you want to use variable for schema reference, which would like this:

declare

schema1 varchar2(16) := 'left';
schema2 varchar2(16) := 'right';
myVar varchar2(100);

begin

  if (some condition) then
    execute immediate 'select * from ' || schema1 || '.tbl1 where id = 1' into myVar;
  else
    execute immediate 'select * from ' || schema2 || '.tbl1 where id = 1' into myVar;
  end if;

end;

answered Jan 9, 2019 at 10:26

SmartDumb

3584 silver badges15 bronze badges

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

1 Comment

Shortik Over a year ago

Yes, execute immediate is what I need. Many thanks. Shortik

Collectives™ on Stack Overflow

PL/SQL - Let the variable behave as source code

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related