BookmarkSubscribeRSS Feed
DMoovendhan
Quartz | Level 8

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

5 REPLIES 5
Kurt_Bremser
Super User

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

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

LinusH
Tourmaline | Level 20

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
DMoovendhan
Quartz | Level 8

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 4056 views
  • 4 likes
  • 4 in conversation