08-25-2016 02:26 AM
We have a requirement in which we would like to write data in Oracle database tables through SAS EG. In our current environment we have connections between SAS and Oracle database, but these connections are meant for reading data from oracle database and then some calculation are done in SAS EG and then some excels are generated manually.
We would like to automate this process like :-
Oracle(Read data through SAS EG) ---> SAS EG(some calculation done here)-----> Oracle(Write in Oracle through SAS EG)
We have tried to do the above scenario by making below changes and it worked:-
1. Go to Oracle library defined in SAS MC
2. Go to Options --> Advanced_Options-->Input/Output
3. Change value of "Data access level for a connection" from READONLY to Blank(no value).
This allow us to write data through SAS EG.
But we would like to check with SAS if there is some other better way to do this or this is the only solution.
08-25-2016 02:31 AM
For libraries defined in SAS metadata, this is exactly how you should proceed. Change the parameters where they are defined, and have one single point where such information (metadata) is kept and worked with.
08-25-2016 04:30 AM
If you go down the route you state, you may end up in a feedback loop. I.e.:
Oracle data is updated independantly
You extract data to EG
You make modifications
You upload to Oracle
Now at this point, the data in Oracle might be different when you upload, or later on the database process/entry person notices data has changed and changes it back again. This is a feedback loop where you change it, and because of that, the DB changes it, and then you have to change etc. Fix your process, send changes to the database owner for them to implement, you should only read the data.
08-25-2016 06:37 AM
Also would writing data in Oracle would require some additional licences ?
No. SAS/ACCESS allows writing without further licensing.
08-25-2016 06:38 AM
Sorry I did not get your point.
"Fix your process, send changes to the database owner for them to implement, you should only read the data."
Could you please explain more
Changes in the DB should not be made directly out of SAS. Instead send a delta table or similar to the DB owner, who then decides how and when the changes will be implemented.
08-25-2016 06:42 AM
As @KurtBremser has mentioned. You are not the owner of the database. The owner of the database should be making updates to the database. You are a consumer of that data. There is one owner, and many consumers.
08-25-2016 08:36 PM
What you describe is only one possible scenario applicable to a data warehousing scenario and may be not applicable here.
If the database owner wouldn't want to allow updates to the tables then I'd hope the Oracle grants would be set accordingly.