SAS writer

[AmitSingh]

Hi,

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.

[Kurt_Bremser]

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.

[RW9]

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.

[AmitSingh]

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

[AmitSingh]

Also would writing data in Oracle would require some additional licences ?

[Kurt_Bremser]

---

@AmitSingh wrote:
Also would writing data in Oracle would require some additional licences ?

---

No. SAS/ACCESS allows writing without further licensing.

[Kurt_Bremser]

---

@AmitSingh wrote:
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.

[RW9]

As @Kurt_Bremser 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.

[AmitSingh]

Okey. I got the point. Thanks!!
We have dedicated db users defined who can make the changes in database. So all the changes will be done with consent with DB owner.

[Patrick]

@RW

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.