10-09-2012 10:06 AM
I try to call a stored procedure in our Oracle database. I tried a lot of combinations of this code but it doesn't work. This message appears:
CLI execute error: [Oracle][ODBC]Syntax error or access violation.
The User finrepo has the right to execute the procedure.
connect to odbc as server (dsn="Finrepo" uid = "finrepo" PWD="******" );
EXECUTE (EXECUTE FINREPO.REFRESH_MV_SALES) by server ;
I hope some one has a idea?
10-09-2012 10:46 AM
Try using the ODBC command CALL instead of the Oracle command EXECUTE
See section XIV. Calling a Stored Procedure Via ODBC on Page 13 of the following:
10-09-2012 03:34 PM
Try your statement out in Oracle SQL Plus or other SQL tool like Toad and see if it works OK:
What does the stored procedure do? Does it produce a result set? If so then you may need to look at a different approach to capture it:
connect to odbc as mydb
(datasrc="Finrepo" user=testuser password=testpass);
create table test as
select * from connection to mydb
01-03-2017 01:17 AM
Okay, than try this code
proc sql; connect to odbc as oracledb (datasrc="yourDSN" user=xxxx password=xxxx); create table work.test_dataset as select * from connection to oracledb ( execute schema.oracleStoredProcedure @Param1 = 'value1', @Param2 = 'value2', @Param3 = 333; ); quit;
Please note that you need SAS/Access to ODBC or SAS/Access to Oracle license to execute oracle stored process.