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
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.
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
);
Hi Bailey, this was very helpful, I will try this.
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?
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.