topic Re: how to Alter oracle current_schema via sas pass through? in SAS Programming

how to Alter oracle current_schema via sas pass through?

jfd — Fri, 09 Feb 2018 21:52:26 GMT

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.

Re: how to Alter oracle current_schema via sas pass through?

LinusH — Sat, 10 Feb 2018 06:27:49 GMT

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

Re: how to Alter oracle current_schema via sas pass through?

Tom — Sat, 10 Feb 2018 20:35:41 GMT

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;

Re: how to Alter oracle current_schema via sas pass through?

jfd — Mon, 26 Feb 2018 20:30:07 GMT

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.

Re: how to Alter oracle current_schema via sas pass through?

jfd — Mon, 26 Feb 2018 20:33:21 GMT

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

Re: how to Alter oracle current_schema via sas pass through?

currymj — Tue, 21 May 2019 17:24:00 GMT

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

Re: how to Alter oracle current_schema via sas pass through?

_KM — Fri, 01 Oct 2021 01:05:34 GMT

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……