topic Re: Call Oracle stored procedure using odbc in SAS Programming

Call Oracle stored procedure using odbc

chrwag — Tue, 09 Oct 2012 14:06:34 GMT

Hi,

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.

proc sql;

connect to odbc as server (dsn="Finrepo" uid = "finrepo" PWD="******" );

EXECUTE (EXECUTE FINREPO.REFRESH_MV_SALES) by server ;

quit;

I hope some one has a idea?

Christoph

Re: Call Oracle stored procedure using odbc

FriedEgg — Tue, 09 Oct 2012 14:46:33 GMT

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:

http://www.oracle.com/technetwork/database/windows/odbcfaq-128251.pdf

Re: Call Oracle stored procedure using odbc

SASKiwi — Tue, 09 Oct 2012 19:34:37 GMT

Try your statement out in Oracle SQL Plus or other SQL tool like Toad and see if it works OK:

EXECUTE FINREPO.REFRESH_MV_SALES

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:

proc sql;

connect to odbc as mydb

(datasrc="Finrepo" user=testuser password=testpass);

create table test as

select * from connection to mydb

(ODBC::SQLProcedures "REFRESH_MV_SALES");

quit;

Re: Call Oracle stored procedure using odbc

chrwag — Thu, 11 Oct 2012 13:54:46 GMT

Perfect. Writing "call" instead "execute" was the solution. Thanks.

Christoph

Re: Call Oracle stored procedure using odbc

Kalind_Patel — Tue, 03 Jan 2017 06:17:42 GMT

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.