BookmarkSubscribeRSS Feed
NN
Quartz | Level 8 NN
Quartz | Level 8

HI all..

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

Thanks..

5 REPLIES 5
SASKiwi
PROC Star

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.

NN
Quartz | Level 8 NN
Quartz | Level 8

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

Thanks...

BenConner
Pyrite | Level 9

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.

--Ben

NN
Quartz | Level 8 NN
Quartz | Level 8

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

SASKiwi
PROC Star

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.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1495 views
  • 6 likes
  • 3 in conversation