Help using Base SAS procedures

Passing Oracle option from SAS SQL pass through facility.

Reply
New Contributor
Posts: 4

Passing Oracle option from SAS SQL pass through facility.

Hi ,

I want to pass the Oracle option "set serveroutput off" from SQL SAS pass through.and another declare the variable

Like:

Proc sql;

connect to ORACLE (user=&usr pw=&pw);

execute(set serveroutput off) by ORACLE;

execute( dte date:=('&1', 'DDMMYYYY')) by ORACLE;

disconnect from ORACLE;

quit;

But it is throughing error.

Please help me how I can execute the above steps.

Thanks in advance.

Regular Contributor
Posts: 244

Re: Passing Oracle option from SAS SQL pass through facility.

Is the option throwing the error or is the second statement? I think you will have trouble with the single quotes around a macro variable - you either need to have &1 contain its own single quotes, or use something like %bquote('&1') to force it to resolve.

New Contributor
Posts: 4

Re: Passing Oracle option from SAS SQL pass through facility.

Hi Snoopy,

Thank you for your response.

I am getting error for both the statements.

I tried even using %bquote. But, didn't work either.

For the first statement The error msg is " ORA 00922 missing or invalid option".

second the error msg is " ORA 06550 execute error"

The second statement was execute(declare dte date:=('&1', 'DDMMYYYY') by ORACLE;

Vijay

Message was edited by: Vijay Kumar

Regular Contributor
Posts: 244

Re: Passing Oracle option from SAS SQL pass through facility.

You're in the land of Oracle issues, then, or at least beyond my knowledge of passthrough.  I don't know if you can even set a variable in passthrough; that's PL/SQL, and I don't know what happens there.  You might try setting up a stored procedure in Oracle to do these things, and call it through passthrough?

Super User
Posts: 5,257

Re: Passing Oracle option from SAS SQL pass through facility.

In SQL pass-thru, you are usually limited to use "pure" SQL statement, such as create, grant etc. So I don't think you can use other type of Oracle PL/SQL statements.

So you need to figure out from the Oracle side how set any specific variable values, perhaps via a Stored Procedure as https://communities.sas.com/people/snoopy369 suggests.

Data never sleeps
New Contributor
Posts: 4

Re: Passing Oracle option from SAS SQL pass through facility.

Hi Linush,

Thank you for the information. Issue got resolved. We created the stored proc and call it from SAS sql.

Thank you,

Vijay

New Contributor
Posts: 4

Re: Passing Oracle option from SAS SQL pass through facility.

Hi Snoopy,

Once again thank you for your input. Issue got resolved. We created the stored proc and call it from SAS sql.

Thank you,

Vijay

Ask a Question
Discussion stats
  • 6 replies
  • 685 views
  • 0 likes
  • 3 in conversation