Hi All:
I am using SAS/ACCESS ODBC (v9.3) to access an Oracle table. I would like to execute a stored process but I keep getting an error:
proc sql;
connect TO ODBC (datasrc=odbc_name user=myuserid password=XXXX1234 );
execute (execute AAA_BBB_CCCC.MY.STORED.PROCESS.NAME ) by ODBC;
ERROR: CLI execute error: [Oracle][ODBC]Syntax error or access violation.
disconnect from ODBC;
quit;
NOTE: The SAS System stopped processing this step because of errors.
I have used this same syntax for a SQL Server store process and it works there.
Any help would be appreciated.
Are you sure of the package name? You're probably obfuscating, but I don't think it can have all of the periods.
Hi:
Just trying to be obtuse.... I messed up the periods - should be AAA_BBB_CCCC.MY_STORED_PROCESS_NAME
But I can run the stored process from SQL + so that shouldn't be an issue.
Have you tried a semicolon on your EXECUTE statement?
execute (execute AAA_BBB_CCCC.MY_STORED_PROCESS_NAME; ) by ODBC;
SASKiwi:
That give me a syntax error.....
I think you are right, semicolons are only required on SELECT statements. Unfortunately I don't have Oracle to test this out on, although I too have a stored procedure running under SQL Server and it works fine.
Have a look at this: 18350 - Calling stored procedures using SAS/ACCESS® Interface to Oracle
And maybe try: execute (execute AAA_BBB_CCCC.MY_STORED_PROCESS_NAME() ) by ODBC; Does your stored proc have any parameters?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.