topic Execute PL/SQL package in DI Studio in SAS Data Management

Execute PL/SQL package in DI Studio

Aidan — Thu, 25 Jul 2019 07:52:27 GMT

Hi there

Is it possible to execute a PL/SQL Oracle package on the Database via SAS DI Studio?

I know it is possible to execute functions in this manner, however haven't come across an actual PL/SQL package been executed in this manner.

If it is possible, would anyone have examples/reference doc's or code snippets to help me achieve this please.

A basic call of the package is below;

execute package.go();

Thank you

Aidan

Re: Execute PL/SQL package in DI Studio

Patrick — Thu, 25 Jul 2019 08:26:09 GMT

I normally split such questions into two parts:

1. How can I do it with SAS

2. Can I use an existing DIS transformation or do I need user written code or a user written transformation

For 1: Yes, via explicit pass-through SQL. Here an example: https://renenyffenegger.ch/notes/Companies-Products/SAS/programming/proc/sql/oracle/call-function

For 2: That will depend on the details of your task but the SQL transformations like Execute will already allow for quite a few things. Just make sure to enable explicit pass-through so that you can use DB SQL syntax in the expression tab.

http://support.sas.com/documentation/cdl/en/etlug/65807/HTML/default/viewer.htm#n0o4rbvvosnwran10wcv2e4ewyvd.htm

Re: Execute PL/SQL package in DI Studio

Aidan — Thu, 25 Jul 2019 11:46:10 GMT

Thank you Patrick, I will test a few bits and let you know how it goes.

Much appreciated