Calling oracle SP through ODBC

[Jenish]

Hi ,

I am using below code to execute a stored process (view) from oracle but its giving me "access violation" error.

proc sql;
connect to odbc (dsn = dnsname user=xxxxx  pwd=xxxxx);
create table schema.table_name (compress = yes) as
select * from connection to odbc (
execute schema_name.view_name
);

disconnect from odbc;
 quit;

[ChrisHemedinger]

By "Access violation" do you mean a SAS error, like a crash with a traceback?  Can you share the log from the error?  Might be a defect and if so, there might be a hotfix -- need details about your SAS version.

Typical syntax for a stored procedure from SAS that I've used:

proc sql;
 connect to odbc (dsn=dsname /* and credentials */) ;
  execute (execute testproc) by odbc;
 disconnect from odbc;
quit;

[Jenish]

I am running the below code to call Stored process from oracle its giving me the error ( error log attached) 

proc sql;
connect to odbc as oracledb
(datasrc="RF_LMS" user=**** password=**** );

create table work.test_dataset as
select *
from connection to oracledb
(
execute HF_REPORTING.LMS_DATA_ARCHIVED

);
quit;

[MichaelLarsen]

Judging from the name after the word execute, I think you might want to extract from a view.

If that is the case change your code to this:

proc sql;
  connect to odbc (dsn = dnsname user=xxxxx  pwd=xxxxx);
  create table schema.table_name (compress = yes) as 
    select * from connection to odbc (
      select * from schema_name.view_name
    );

[Jenish]

I want to call a stored procedure from oracle will
select * from schema_name.view_name work.??

[JBailey]

Hi @Jenish

I am not sure if this is the case with calling an Oracle stored procedure via SAS/ACCESS Interface to ODBC, but SAS/ACCESS Interface to Oracle cannot return a result set, or output parameters, to SAS. It may be helpful to alter the stored procedure so that it writes its output to an Oracle table and then SELECT from the table.

Best wishes,

Jeff

[Jenish]

SAS/Access interface to SQL works perfect.
guess there should be some way out.

[MichaelLarsen]

I found this post:

Call-Oracle-stored-procedure-using-odbc/td-p/89822

with a similar problem and that was solved by using the Oracle statement CALL instead of EXECUTE.

So your example would then be:

proc sql;
connect to odbc (dsn = dnsname user=xxxxx  pwd=xxxxx);
create table schema.table_name (compress = yes) as 
select * from connection to odbc (
call schema_name.view_name
);

I am not familiar with the difference between EXECUTE and CALL within Oracle.

[Jenish]

Hi used your code but this time it save me different error.

Please check the log attached.

[JBailey]

Hi @Jenish

You may be right. Who knows? I looked up the SAS Note regarding resultant sets and stored procedures.

Usage Note 18350: Calling stored procedures using SAS/ACCESS® Interface to Oracle

If you don't mind, can you provide Oracle stored procedure code.

[LinusH]

@J BAILEY that's an institute internal link, you wish to post the corresponding public one 😆

This might come on handy as well:
https://docs.oracle.com/cd/B19306_01/server.102/b15658/app_odbc.htm#UNXAR423

[JBailey]

Hi @LinusH,

I have repaired the link. Thanks for letting me know.

Here is the new link: http://support.sas.com/kb/18/350.html