how to Alter oracle current_schema via sas pass through?

[jfd]

SQL code_1:

proc sql;

connect to oracle (user="xxxxx" pw="%superq(ex_pw)" path="&ex_path");

execute (ALTER SESSION SET CURRENT_SCHEMA=HCCLNW;)by ORACLE;

DISCONNECT from ORACLE;

run;

result_1:

ERROR: ORACLE execute error: ORA-00922: missing or invalid option.

SQL code_2:

proc sql;

connect to oracle (user="xxxxx" pw="%superq(ex_pw)" path="&ex_path");

execute (EXEC ALTER SESSION SET CURRENT_SCHEMA=HCCLNW;)by ORACLE;

DISCONNECT from ORACLE;

run;

Result_2:

ERROR: ORACLE execute error: ORA-06550: line 1, column 8:PLS-00103: Encountered the symbol "ALTER" when expecting one of the

following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an

identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock

insert open rollback savepoint set sql execute commit forall merge pipe purgeThe symbol "update was inserted before

"ALTER" to continue.ORA-06550: line 1, column 49:PLS-00103: Encountered the symbol ";" when expecting one of the

following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while

with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch

lock insert open rollback savepoint set sql execute commit forall merge pipe purgeThe symbol "exit" was substituted

for ";" to continue..

ANY IDEAs?

Thanks.

[LinusH]

Why can't you set the appropriate schema in the connect statement directly?

[jfd]

It does not work that way in a SAS pass_througth to Oracle. I did try it...

[Tom]

Are you sure the schema exists? 

Does the ALTER command work when using some other tool to connect to your Oracle database?

See if it helps to add semi-colon to the Oracle command?

proc sql;
  connect to oracle (user="xxxxx" pw="%superq(ex_pw)" path="&ex_path");
  execute 
    (ALTER SESSION SET CURRENT_SCHEMA=HCCLNW ; )
  by ORACLE;
  DISCONNECT from ORACLE;
quit;

[jfd]

Thank you for the response.  The schema is right. It doe not require to have a semi-colon at the end of the Oracle command. Strange enough.

Thanks.

[currymj]

Did you ever find a solution? I am struggling to find one.

[_KM]

here's example of alter session that did not error. 

Proc sql;

      Connect to oracle as oraconnect (user="&ora_userid" password="&ora_pw" path=Prod connection=global);

   Execute by oraconnect(ALTER SESSION set "_push_join_predicate"=false);

    Create table……