Call Oracle stored procedure using odbc

Reply
Contributor
Posts: 21

Call Oracle stored procedure using odbc

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

Trusted Advisor
Posts: 1,300

Re: Call Oracle stored procedure using odbc

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

Respected Advisor
Posts: 3,065

Re: Call Oracle stored procedure using odbc

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:Smiley FrustratedQLProcedures "REFRESH_MV_SALES");

quit;

Contributor
Posts: 21

Re: Call Oracle stored procedure using odbc

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

Christoph

Frequent Contributor
Posts: 96

Re: Call Oracle stored procedure using odbc

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.

Ask a Question
Discussion stats
  • 4 replies
  • 3342 views
  • 3 likes
  • 4 in conversation