topic Re: Using sde.version_util.set_current_version in Oracle stored procedure in Geodatabase Questions

Using sde.version_util.set_current_version in Oracle stored procedure

ApurvDanke — Wed, 20 Dec 2017 17:33:37 GMT

Hi All,

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

Error(12,8): PLS-00103: Encountered the symbol "SDE" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "SDE" to continue.

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');

Error(12,21): PLS-00222: no function with name 'SET_CURRENT_VERSION' exists in this scope

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.

Regards,

Apurv

Re: Using sde.version_util.set_current_version in Oracle stored procedure

Uday_BhanuBurrey2 — Thu, 30 Aug 2018 18:46:47 GMT

Apurv,

Did you get around the prblem?

Re: Using sde.version_util.set_current_version in Oracle stored procedure

ApurvDanke — Fri, 31 Aug 2018 10:16:22 GMT

Hi Uday,

Before querying the versioned view in my stored procedure I wrote the following statement.

sde.version_util.set_current_version(Version_Name);

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.

Regards,

Apurv

Re: Using sde.version_util.set_current_version in Oracle stored procedure

Uday_BhanuBurrey2 — Mon, 10 Sep 2018 21:20:11 GMT

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.