BookmarkSubscribeRSS Feed
Jenish
Calcite | Level 5

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;

11 REPLIES 11
ChrisHemedinger
Community Manager

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;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Jenish
Calcite | Level 5

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
SAS Employee

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
Calcite | Level 5
I want to call a stored procedure from oracle will
select * from schema_name.view_name work.??
JBailey
Barite | Level 11

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
Calcite | Level 5
SAS/Access interface to SQL works perfect.
guess there should be some way out.
MichaelLarsen
SAS Employee

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
Calcite | Level 5

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

Please check the log attached.

JBailey
Barite | Level 11

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
Tourmaline | Level 20
@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
JBailey
Barite | Level 11
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 2054 views
  • 0 likes
  • 5 in conversation