topic Re: Calling oracle SP through ODBC in SAS Data Management

Calling oracle SP through ODBC

Jenish — Mon, 30 Jan 2017 12:51:46 GMT

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;

Re: Calling oracle SP through ODBC

ChrisHemedinger — Mon, 30 Jan 2017 13:10:02 GMT

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;

Re: Calling oracle SP through ODBC

MichaelLarsen — Mon, 30 Jan 2017 13:29:59 GMT

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
    );

Re: Calling oracle SP through ODBC

JBailey — Mon, 30 Jan 2017 13:31:58 GMT

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

Re: Calling oracle SP through ODBC

Jenish — Mon, 30 Jan 2017 13:38:36 GMT

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;

Re: Calling oracle SP through ODBC

Jenish — Mon, 30 Jan 2017 13:40:27 GMT

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

Re: Calling oracle SP through ODBC

Jenish — Mon, 30 Jan 2017 13:47:22 GMT

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

Re: Calling oracle SP through ODBC

MichaelLarsen — Mon, 30 Jan 2017 14:01:35 GMT

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.

Re: Calling oracle SP through ODBC

JBailey — Thu, 02 Feb 2017 13:14:34 GMT

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.

Re: Calling oracle SP through ODBC

Jenish — Tue, 31 Jan 2017 08:43:30 GMT

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

Please check the log attached.

Re: Calling oracle SP through ODBC

LinusH — Thu, 02 Feb 2017 07:04:31 GMT

@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

Re: Calling oracle SP through ODBC

JBailey — Thu, 02 Feb 2017 13:15:45 GMT

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