Thanks; this is a very good paper and I've passed it along to my manager.

If one wants to pass in a data set to a stored procedure and get a data set back to SAS, what approach would you use to do that?  I could see inserting data into a temporary Oracle table, but you'd want to make sure you didn't overwrite other people's data.  Or perhaps would you pass a delimited string containing all the data?  For the stored procedure we are thinking of doing we usually have two fields in, but can have lots of rows, like 500,000, and usually get back about 5 fields also with 500,000 rows.

-Mary

--- vogelmann74@HOTMAIL.COM wrote:

From: jclguy <vogelmann74@HOTMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject:      Re: Can Oracle Pass-Through SQL execute a Stored Procedure?
Date:         Wed, 6 Jan 2010 07:43:55 -0800

Yes, absolutely.
Please see my paper on Lex Jansen's most excellent site:
www.lexjansen.com/pharmasug/2005/technicaltechniques/tt07.pdf

Assuming you have SAS/CONNECT for ORACLE (and that's a big if) - once
you've "connected" to the database, SAS "sees" the tables as sas
datasets, so in theory, any operation you can perform on a sas dataset
you can perform on an Oracle table. Of course, there's nothing
stopping you from creating a "work" data set from an Oracle table and
doing whatever you wish to it without hurting anything. What you can
do to the oracle tables is restricted to the permissions of the userid
you are using to access the data - I am careful to use a "read-only"
userid for exactly that reason. Hope this helps.