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.
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;
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.
Did you ever find a solution? I am struggling to find one.
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……
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.