Passing Oracle option from SAS SQL pass through facility.

[mvskumar16]

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.

[snoopy369]

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.

[mvskumar16]

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

[snoopy369]

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?

[LinusH]

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.

[mvskumar16]

Hi Linush,

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

Thank you,

Vijay

[mvskumar16]

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