Calling PL/SQL procedures and reteriving outputs in SAS

[DMoovendhan]

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

[Kurt_Bremser]

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.

[DBailey]

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

);

[DMoovendhan]

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

[LinusH]

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?

[DMoovendhan]

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.