Commit to Oracle via Excel SAS Add-In inconsistent?

[AndreasEM]

I'm updating some data on Oracle via SAS Add-in in Excel (version 7.14). The SAS environment is 9.4. I noticed that insert ("Create New Records") and delete ("Delete Records") happens directly, that is before I leave "Edit mode". For update however, I need to leave Edit mode for the commit to happen. Does this make sense?

[Patrick]

@AndreasEM

I'd say intuitively one would expect a consistent behavior and if there is a commit in AMO then my expectation would be that nothing gets commited before I push the button.

When does the actual commit for insert and delete happen? Right when you move to another row or only when you leave the sheet without commiting? Are you checking directly on the data base - i.e. change something via AMO and then query the table from another SAS session or with SQL Developer and test what's actually in Oracle?

And the next question would be: Is the behavior you observe anywhere documented? If not then I'd raise a SAS TechSupport track for this one.

[Tom]

Sounds like a reasonable implementation to me. If you are in an EDIT mode then you could be making changes to many variables and/or observations.  For example you might want to change two variables for the same observation, say the status and effective date. You wouldn't want it to commit in the middle after you had only changed one value.

But if you are deleting whole observations or inserting whole observations then why not have those operations take effect immediately?

[Patrick]

@Tom

"But if you are deleting whole observations or inserting whole observations then why not have those operations take effect immediately?"

Because SAS AMO has a COMMIT "button" so why should anything commit before you commit? 

[AndreasEM]

Thanks for your replies! I've found that the commit button doesn't do anything at all when updating on Oracle.

To answer some of your previous questions, the "delete records" happens immediately when I click it in Excel, I check this with SQL Developer direclty on the DB. The same goes for "Create New Records", it immediately creates a new record with only *null*-values (this table has no triggers).

When updating the new record however, I can click "Commit" how many times I want, nothing happens on Oracle. I need to click "End Edit", when I do this there seems to be an autocommit that updates the table.

I will check the documentation and in case there's a discrepancy I'll file a track.

[AndreasEM]

I was a bit quick in my latest reply stating that the commit button doesn't do anything. It commits, it seems, but not all the way to Oracle as the insert / delete. You need to "End Edit" for the commit to happen on Oracle. Maybe this is not as bad as I first thought, but maybe a bit inconsistent.