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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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