01-21-2014 01:53 PM
I have an application server which uses an oracle database and i have SAS/CONNECT available using which i can connect to the oracle database for my data requirements.
Now my question is ... is it possible that SAS can pickup data from oracle within seconds of an event getting triggered in the application server.
If some one could even guide me to some documents i would be extremely thankful..
01-21-2014 02:10 PM
You need SAS/ACCESS to Oracle to access an Oracle database - do you have this? SAS/CONNECT is for connecting between SAS clients / servers.
What is the event that would signify that new data is available? One option would be to have an Oracle table that recorded the date and time of the most recent update. Then you could have a continuously-running SAS program that checks this to confirm if it is later than the last update and if so trigger another SAS program to read the latest update. Yes it is definitely doable.
The Oracle querying would probably involve SQL and the looping of the querying to poll for the latest data is probably best handled using SAS macro language. The standard SAS documentation should help you here.
01-21-2014 02:38 PM
Sorry.. i meant to write access..wrote connect instead..
yes the event will get captured as a new row in a table with a datetime ..
From the point you have mentioned , yes i can run a loop which could connect to the server at a regular interval or look at an option where a single connection will loop within itself..
But both these options will require SAS sessions to be kept live for a considerable amount of time..
That is the reason why i thought of asking this question... though logically i do understand that this is the only way..
01-21-2014 02:50 PM
There's really only two ways to accomplish this--having SAS check on a recurring basis per the above quote, or having Oracle execute an external procedure when a new row gets inserted in the table. That procedure could be as simple as running a batch job/script which used SAS to process the new rows. That said, I don't know enough about Oracle to advise on how to do that. I'm sure others can provide the details.
01-23-2014 01:16 AM
Thanks Ben.. I too wanted to find something in the lines where oracle could somehow force a sas session.. However SASKIWI has given a workable solution which i would like to explore into..
01-21-2014 04:20 PM
Actually, there is another way to do this that would not involve a continuously-running SAS program. You could get the Oracle update to write out an empty external text file each time there is an update. You can then have a job scheduler checking for the existence of this file for the trigger to start the SAS job to read Oracle. At the end of the job the trigger file should be deleted, ready for the next time it appears.
The LSF scheduler that SAS supplies as part of many SAS BI server installs is capable of doing this. And its all controlled from SAS Management Console which is a bonus. Most other full-featured job schedulers can do this as well.
If you are not familiar with running SAS in batch mode, check out the documentation.