BookmarkSubscribeRSS Feed
mvskumar16
Calcite | Level 5

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.

6 REPLIES 6
snoopy369
Barite | Level 11

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
Calcite | Level 5

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
Barite | Level 11

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
Tourmaline | Level 20

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
mvskumar16
Calcite | Level 5

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1899 views
  • 0 likes
  • 3 in conversation