Referencing Schema Name as Variable in Oracle Procedure

Question

Is there a way to set schema name as variable in oracle procedure?

create or replace procedure test is v_schema varchar2(30);

begin

insert into v_schema.tab_a ( a, b) select (a, b) from xyz;

end; /

Thanks

Answer 1

You'd need to resort to dynamic SQL

create or replace procedure test 
is 
  v_schema varchar2(30);
  v_sql    varchar2(1000);
begin
   v_sql := 'insert into ' || v_schema || '.tab_a( a, b ) ' ||
               'select a, b from xyz';
   dbms_output.put_line( 'About to execute the statement ' || v_sql );
   execute immediate v_sql;
end; 

A couple of points

1. You almost certainly want to build the SQL statement in a local variable that you can print out and/or log before executing it. Otherwise, when there are syntax errors, you're going to have a much harder time debugging.
2. You almost never want to resort to dynamic SQL in the first place. The fact that you have a procedure where you know you want to insert all the rows from xyz into a table named tab_a but you don't know which schema that table is in is a red flag. That's unusual and often indicates a problem with your design. Very, very occasionally dynamic SQL is a wonderful tool when you need extra flexibility. But more often than not when you're thinking about a problem and dynamic SQL is the answer you want to reconsider the problem.