How to check the current schemas and change the default schema in PL/SQL?-CodePudding

I'm trying to write queries in Oracle. I wanted to make sure it worked with the correct schema, so I thought the below code would solve my issue. I guess syntax is wrong.

Could you fix it?

DECLARE
  v_current_schema varchar2(30);
BEGIN
  v_current_schema := SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA');
  IF V_CURRENT_SCHEMA <> 'PRODUCTION' THEN
    ALTER  SESSION SET CURRENT_SCHEMA = "PRODUCTION" ;
  END IF;
END;

ORA-06550: row 6, column 13:PLS-00103: Encountered symbol "ALTER", expected one of the 
following:( start report status go to exit if loop mod empty pragma remove back select 
update while with <determinant><double quote delimited specifier>
<connection variable> << continue close current delete fetch lock
insert open undo savepoint set sql execute save merge for all clear pipe json_exists 
json_value json_query json_object json_array
Symbol "update", select "ALTER" to continue.

CodePudding user response：

Two things

DDL commands must run always by execute immediate in PL/SQL.
You don't need the variable at all, as you can use sys_context directly inside the if-then statement.

Code simplified

declare
begin
  if sys_context('USERENV', 'CURRENT_SCHEMA') <> 'PRODUCTION' 
  then
     execute immediate 'ALTER  SESSION SET CURRENT_SCHEMA = PRODUCTION ' ;
  end if;
end;

CodePudding user response：

It is dynamic SQL you should use:

declare
v_current_schema varchar2(30);
begin
  v_current_schema := sys_context('USERENV', 'CURRENT_SCHEMA');
  if v_current_schema <> 'PRODUCTION' then
     execute immediate 'ALTER  SESSION SET CURRENT_SCHEMA = PRODUCTION' ;
  end if ; 
end; 
/