Prev: Can Oracle Pass-Through SQL execute a Stored Procedure?
Next: proc tabulate
From: Joe Matise on 5 Jan 2010 14:46
Yep, I don't have any problems executing Oracle stored procedures in
pass-through. Another possibility depending on the task is a view that
might be able to contain all of your code in one view [that's accessible by
both pass-through and libname SQL].

-Joe

On Tue, Jan 5, 2010 at 1:39 PM, Mary <mlhoward(a)avalon.net> wrote:

> Has anyone ever executed an Oracle Stored Procedures from Pass-through SAS
> SQL? I'm pretty sure this would work, but wanted to ask if there were any
> problems with this before asking our database group to setup a stored
> procedure on a task we do often that we'd like to execute from SAS since we
> always have SAS programs to run on either side of it.
>
> A stored procedure in Oracle would be the same as a PL/SQL block
>
> -Mary
>
From: jclguy on 6 Jan 2010 10:43
Yes, absolutely.
Please see my paper on Lex Jansen's most excellent site:
www.lexjansen.com/pharmasug/2005/technicaltechniques/tt07.pdf
From: Mary on 6 Jan 2010 11:37
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(a)HOTMAIL.COM wrote:

From: jclguy <vogelmann74(a)HOTMAIL.COM>
To: SAS-L(a)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
From: jclguy on 6 Jan 2010 15:16
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.
From: Michael Davis on 6 Jan 2010 16:26
Hello Friends,

I believe it is SAS/ACCESS Interface to Oracle,
not SAS/CONNECT that is needed.

Best wishes,

Michael
Davis


On Wed, January 6, 2010 3:16 pm, jclguy
<vogelmann74(a)HOTMAIL.COM>wrote:
> 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.
>


--
Michael L. Davis
Ambler PA
E-Mail:
michael.davis(a)alumni.duke.edu
 | 
Pages: 1
Prev: Can Oracle Pass-Through SQL execute a Stored Procedure?
Next: proc tabulate