topic Passing Oracle option from SAS SQL pass through facility. in SAS Procedures

Passing Oracle option from SAS SQL pass through facility.

mvskumar16 — Wed, 23 Oct 2013 13:41:32 GMT

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.

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

snoopy369 — Wed, 23 Oct 2013 14:15:51 GMT

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.

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

mvskumar16 — Wed, 23 Oct 2013 14:45:02 GMT

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

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

snoopy369 — Wed, 23 Oct 2013 14:53:36 GMT

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?

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

LinusH — Thu, 24 Oct 2013 14:58:36 GMT

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.

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

mvskumar16 — Fri, 25 Oct 2013 11:55:44 GMT

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

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

mvskumar16 — Fri, 25 Oct 2013 11:57:14 GMT

Hi Linush,

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

Thank you,

Vijay