I am trying to write a stored procedure in Oracle to fetch data from a particular version, by using the sde.version_util.set_current_version function. When I run the following statement individually in SQL Developer, it works fine.
EXEC sde.version_util.set_current_version('My Version Name');
It shows a message in SQL Developer as -> anonymous block completed
After this I can query this version to get the data that I want.
However when I try to use the same statement in a stored procedure it gives following error ->
If I use EXECUTE IMMEDIATE (as given below) instead of EXEC, then I get a different error.
EXECUTE IMMEDIATE sde.version_util.set_current_version('My Version Name');
ArcGIS geodatabase is 10.4.1 and database which we're using is Oracle 12g.
Can someone suggest how this function has to be used in a stored procedure?
Once this statement gets executed, I want to select some data from versioned view in a cursor.
Before querying the versioned view in my stored procedure I wrote the following statement.
After that I wrote the query to retrieve the data from this version.
Lastly you need to set the current version back to SDE.DEFAULT, so you must call the function again after getting the data. For some reason which I cannot recall , I had to do this particular thing in a separate stored procedure which has just one line which is to set the current version.
So I made 2 SP calls - first one containing setting the query version, and executing the query to get the data.
2nd one simply to set the current version back to SDE.DEFAULT. This approach worked for me.
I am not sure if you can write the last statement to set back to SDE.DEFAULT in the same stored procedure, but you can try.
Hope this input helps.
Thanks Apurv, your answer was very helpful !
I was doing updates and inserts to the featureclass using the stored procedure. I used the actual featureclass instead of using it corresponding view. That is where everything went wrong for me. Though i was doing the edits in an edit version, it was apparently updating the base table as well.
I was able to set the version back to SDE.DEFAULT after the updates and it seems working for me.