12-12-2014 02:52 AM
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.
12-12-2014 03:08 AM
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.
12-12-2014 08:46 AM
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.
connect using OraLIb as OraCon;
) by OraCon;
disconnect from OraCon;
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(
12-12-2014 08:48 AM
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?
12-12-2014 11:43 AM
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.