12-01-2014 10:48 AM
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:
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;
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.
12-01-2014 01:11 PM
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.
12-01-2014 02:08 PM
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?