SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Calling oracle SP through ODBC

Reply
Occasional Contributor
Posts: 5

Calling oracle SP through ODBC

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;

Community Manager
Posts: 2,764

Re: Calling oracle SP through ODBC

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;
Occasional Contributor
Posts: 5

Re: Calling oracle SP through ODBC

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;

SAS Employee
Posts: 20

Re: Calling oracle SP through ODBC

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
    );
Occasional Contributor
Posts: 5

Re: Calling oracle SP through ODBC

I want to call a stored procedure from oracle will
select * from schema_name.view_name work.??
SAS Employee
Posts: 203

Re: Calling oracle SP through ODBC

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

Occasional Contributor
Posts: 5

Re: Calling oracle SP through ODBC

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

Re: Calling oracle SP through ODBC

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.

 

Occasional Contributor
Posts: 5

Re: Calling oracle SP through ODBC

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

Please check the log attached.

SAS Employee
Posts: 203

Re: Calling oracle SP through ODBC

[ Edited ]

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.

Super User
Posts: 5,257

Re: Calling oracle SP through ODBC

@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
Data never sleeps
SAS Employee
Posts: 203

Re: Calling oracle SP through ODBC

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
Ask a Question
Discussion stats
  • 11 replies
  • 345 views
  • 0 likes
  • 5 in conversation