10-23-2013 09:41 AM
I want to pass the Oracle option "set serveroutput off" from SQL SAS pass through.and another declare the variable
connect to ORACLE (user=&usr pw=&pw);
execute(set serveroutput off) by ORACLE;
execute( dte date:=('&1', 'DDMMYYYY')) by ORACLE;
disconnect from ORACLE;
But it is throughing error.
Please help me how I can execute the above steps.
Thanks in advance.
10-23-2013 10:15 AM
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.
10-23-2013 10:45 AM
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;
Message was edited by: Vijay Kumar
10-23-2013 10:53 AM
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?
10-24-2013 10:58 AM
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.
10-25-2013 07:55 AM
Once again thank you for your input. Issue got resolved. We created the stored proc and call it from SAS sql.