DATA Step, Macro, Functions and more

how to Alter oracle current_schema via sas pass through?

Reply
Highlighted
New Contributor jfd
New Contributor
Posts: 3

how to Alter oracle current_schema via sas pass through?

SQL code_1:

 

proc sql;

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

execute (ALTER SESSION SET CURRENT_SCHEMA=HCCLNWSmiley Winkby 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=HCCLNWSmiley Winkby ORACLE;

DISCONNECT from ORACLE;

run;

Result_2:

ERROR: ORACLE execute error: ORA-06550: line 1, column 8Smiley TongueLS-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 49Smiley TongueLS-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.

 

Super User
Posts: 5,849

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

Why can't you set the appropriate schema in the connect statement directly?
Data never sleeps
New Contributor jfd
New Contributor
Posts: 3

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

It does not work that way in a SAS pass_througth to Oracle. I did try it...
Super User
Super User
Posts: 7,929

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

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;

 

New Contributor jfd
New Contributor
Posts: 3

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 240 views
  • 0 likes
  • 3 in conversation