topic Re: Calling PL/SQL procedures and reteriving outputs in SAS in SAS Programming

Calling PL/SQL procedures and reteriving outputs in SAS

DMoovendhan — Fri, 12 Dec 2014 07:52:20 GMT

Hi,

We are tring to do an automation, with pl/sql block and SAS.

Kindly help to call the pl/sql procedure and to reterive a value.

Regards,

Moovendhan D

Re: Calling PL/SQL procedures and reteriving outputs in SAS

Kurt_Bremser — Fri, 12 Dec 2014 08:08:41 GMT

Either use SAS/ACCESS to connect to the database, then you can use SQL passthrough to execute SQL code in the DBMS.

If by "pl" you actually mean PL/1, this makes me think that you are working in a z/OS environment, so another option is to unload the DB data to flat files, process those with PL/1 code, and then import the (flat file) results into SAS.

Re: Calling PL/SQL procedures and reteriving outputs in SAS

DBailey — Fri, 12 Dec 2014 13:46:50 GMT

You can also use passthrough sql to execute packages on the oracle platform. If you already have a libname defined (say OraLib) for the oracle connection, you can use the new connect using syntax. If not, then you'll need to use the expanded connection syntax.

proc sql;

connect using OraLIb as OraCon;

execute (

exec schema.packagename

) by OraCon;

disconnect from OraCon;

quit;

I haven't tested it...but if your package returns values, then you might could use something like

create table work.want as select * from connection to OraCon(

exec schema.packagename

);

Re: Calling PL/SQL procedures and reteriving outputs in SAS

LinusH — Fri, 12 Dec 2014 13:48:35 GMT

You should be able to call a stored procedure through a EXCUTE block in proc sql.

But you can't retrieve a value that way.

The only way to retrieve a value directly back to SAS i through a select statement (from connection to...()).

Perhaps you could store the return value in a table for later retrieval?

Re: Calling PL/SQL procedures and reteriving outputs in SAS

DMoovendhan — Fri, 12 Dec 2014 16:41:00 GMT

Hi Bailey, this was very helpful, I will try this.

Re: Calling PL/SQL procedures and reteriving outputs in SAS

DMoovendhan — Fri, 12 Dec 2014 16:43:26 GMT

Thanks Linus, but reteriving the value form a table stored in DB takes more time, I tried this.

This will occur multiple times in the automations step, so it will increase the execution time.

But if I don't have any other option I will go with this.