How to query a versioned view in PL/SQL Developer?

Question

I'm having trouble getting a SELECT query in PL/SQL to actually return records from a version in my Oracle SDE. I tried with a query layer too but I just can't seem to get the EXEC sde.version_util.set_current_version('') command to work. I'm using a 10.1 SP1 client. My test SDE database is 10.2

Here's how to recreate my little test environment:

1. In ArcCatalog, create a new point feature class on the SDE named 'MY_FC'
2. Version the feature class
3. Right click on the feature class, then click Manage, and then Create Versioned View. This created a view named MY_FC_VW
4. Create a geodatabase version on the SDE named 'MY_VERSION'. It will be a child of the default version
5. Open ArcMap, add the feature class, point it to the version, and add one point to it, then save edits and stop editing

So now the goal is to run a query from PL/SQL that will return that new point record while it is in that child version, and this is where I get lost. I'm using the ESRI help as a guide (http://resources.arcgis.com/en/help/main/10.1/index.html#/in_Oracle/006z0000000v000000/)

6. Open PL/SQL and connect to the database
7. Open a SQL window and run the command EXEC sde.version_util.set_current_version('MY_VERSION')
 -This is when I get an error ORA-00900: Invalid SQL Statement.
 -A coworker recommended trying to run this command in the PL/SQL command window as the following:
 BEGIN 
 EXEC sde.version_util.set_current_version('MY_VERSION')
 END;
 -But then I couldn't figure out how to run the select statement and see a returned record.

Anyone have any ideas? I ultimately just need to run a query external of ArcGIS that returns all the records in a feature class for a particular version in the geodatabase, and then extract those records to a csv file.

MR14 · Answer

Hello,
late to the party, but stumbled over the same problem:

BEGIN

sde.version_Util.Set_Current_Version('MY_VERSION');

sde.version_user_ddl.edit_version('MY_VERSION',1);

END;

/
SELECT * FROM TABLE WHERE ID LIKE '123456';

BEGIN

sde.version_user_ddl.edit_version('MY_VERSION',2);

END;

/

MarcoBoeringa · Answer

7.  Open a SQL window and run the command EXEC sde.version_util.set_current_version('MY_VERSION')
          -This is when I get an error ORA-00900: Invalid SQL Statement.
          -A coworker recommended trying to run this command in the PL/SQL command window as the following:
             BEGIN 
             EXEC sde.version_util.set_current_version('MY_VERSION')
             END;
             -But then I couldn't figure out how to run the select statement and see a returned record.

Have you tried including the SELECT statement inside the same BEGIN/END block? What do you mean with the bold text, I understand you want to export to CSV. I must admit I am less familiar with PL/SQL, but I guess there may be commands for that. Just Googling on "pl/sql export to csv" turns up a whole bunch of code samples you may find useful...

MarcoBoeringa · Answer

I'm using a 10.1 SP1 client.  My test SDE database is 10.2
...
Anyone have any ideas?

Your problem may be here. While ArcMap 10.2 can connect to SDE geodatabases of a lower version, as it is backward compatible with most versions of SDE geodatabases still around, I am pretty sure a 10.1 client is not guaranteed to connect to a 10.2 successfully. Your asking for forward compatibility here...

I would recommend you to upgrade your ArcGIS for Desktop client to 10.2 and try again.

BTW, as of 10.1, versioned views should be automatically created each time you enable versioning on data, so there is no need for step 3 in your description. See this Help text from the link you gave:

"Beginning with ArcGIS 10.1, versioned views are created when you version data. If your data was versioned prior to 10.1, you can create a versioned view by running the Create Versioned View geoprocessing tool."