DATA Step, Macro, Functions and more

Calling PL/SQL procedures and reteriving outputs in SAS

Reply
Contributor
Posts: 65

Calling PL/SQL procedures and reteriving outputs in SAS

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

Super User
Posts: 7,760

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

Posted in reply to DMoovendhan

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 578

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

Posted in reply to KurtBremser

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

);

Contributor
Posts: 65

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

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

Super User
Posts: 5,424

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

Posted in reply to DMoovendhan

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?

Data never sleeps
Contributor
Posts: 65

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

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.

Ask a Question
Discussion stats
  • 5 replies
  • 1539 views
  • 3 likes
  • 4 in conversation