Oracle stored procedure call from SAS

Reply
Occasional Contributor
Posts: 9

Oracle stored procedure call from SAS

Hello Team,

 

My question may come with a rather simple answer, but i am somehow not able to call my stored procedure from Oracle.

I have this libname statement:

libname test odbc dsn='orc' user=uat password=xyz datasrc=smth; (this is sas eg 7.1 and oracle 11g, my clients' machine)

 

And using this i am able to connect to the oracle DB by proc sqls and data steps by calling table names as test.table_name.

I want to call a stored procedure from the same DB and i have tried following with many iterations:

 

a) dbconinit='exec abc'; - gives no results or error.

 

b) proc sql;
execute(exec orc.abc);
quit; - gives error:

25 execute(exec orc.abc);

                                      _
22
ERROR 22-322: Expecting a name.

c) proc sql;
connect to odbc (dsn=orc);
execute ( execute = [orc].[abc]
) by odbc;
disconnect from odbc;
quit;

gives error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver
specified

 

Also, i see a lot of connection strings used in some other examples but then i dont know exactly what are the parameter being requested in that connection string so that i can ask my clients for the same.

 

Any lead is appreciated!

 

Thanks,

Apeksha Pathak

Super Contributor
Posts: 259

Re: Oracle stored procedure call from SAS

I will suggest first try to create a small table using the below proc sql to ensure that your connection to Oracle is working. Once this work then try executing stored procedure. 

 

proc sql;
connect to odbc (dsn=orc user=uat password=xyz schema=test);
execute ( 

 

create table test.temp

(Name varchar(20));


) by odbc;
disconnect from odbc;
quit;

 

 

Super User
Posts: 3,233

Re: Oracle stored procedure call from SAS

Here is an example of how to do it. Usually a stored procedure will have parameters - you might like to try with parameters:

 

http://support.sas.com/kb/18/350.html

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 184 views
  • 0 likes
  • 3 in conversation