Call Oracle stored procedure using odbc

[chrwag]

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

[FriedEgg]

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

[SASKiwi]

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;

[chrwag]

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

Christoph

[Kalind_Patel]

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.