How to select on an Oracle Database Sequence value in arcpy

[JamesFox1]

I need to be able to get an Oracle Database Sequence value (either CURRVAL or NEXTVAL) using an arcpy sql query. I used a
generic sql execute function as opposed to the SelectCursor function but cannot seem to be able to get the sequence value
returned fromthe sql call into the python script. The examples I have seen can execute the call but the value I get returned is a
float, I am guessing a representation of a boolean which represents whether the sql successfully executed or not. The code we
have is something like this:

   sql = "SELECT ID_SEQ.CURRVAL FROM DUAL"
   connSQl = arcpy.ArcSDESQLExecute("\Database Connections\Geodatabase.sde")

   sqlresults = connSQL.execute(sql)

   ## sqlResults is non-iterable so I cannot for loop through a list [as rows to qualify]

   The sequence value is a positive integer yet I get some value 2e-10 back

1. Can anyone show me what I am doing wrong here or if there is another function I should be using to talk to a a non-table
database object like a sequence. There must be since SDE uses so many of them for IDs.

2. If not, the same question for a PL/SQL stored procedure call, getting a return value out.  Our sequences have PL/SQL function
accessors so if there is way to get these values this way that would be fine.

We would prefer to use arcpy and avoid the other oracle based sql call modules simply to keep our connection credentials
encapsulated in our sde objects.

If you know of some examples out there, throw me bone since my searches come up[ with only table examples.
("table scraps" if you will...)



Thanks much,

-James Fox

[JamesCrandall]

James,

When you execute the SQL in SQL Developer, Toad or whatever database tool/UI, what is the result?

I copied your sql "SELECT ID_SEQ.CURRVAL FROM DUAL" into a SQL Developer query window and ran it and it returned the error:

"ORA-02289: squence does not exist"
Cause: the sequence does not exist or the user does not have the required privilege to perform this operation
Action: make sure the sequence name is correct, and that you have the right to perform the desired operatoin on this sequence.
Vendor code 2289Error at Line: 1 Column 7

If you can run the statement on your SQL developer without error, then you may want to implement cx_Oracle Python library to execute your sql commands.

[JamesFox1]

James,

Yes, the sequence does exists and I get an integer value back when I run the SQL in SQL*PLUS. The second part of the question deals calling a PL/SQL function from arcpy. If so, we can go that route. I do not get an ORA error from the code but the return value is giberish and unusable, as clearly I did not get the sequence value back from the call.

In the meantime, I will look into cx_oracle but like I said I am useasy about hardcoing login credentials into code like the examples I have seen. That is why I would prefer to do this using arcpy because the credentials are obtained from the users sde files which are
used connect to the database when they pick their input feature classes in the GP tool interface.

-Jim

[JamesCrandall]

James,

Yes, the sequence does exists and I get an integer value back when I run the SQL in SQL*PLUS. The second part of the question deals calling a PL/SQL function from arcpy. If so, we can go that route. I do not get an ORA error from the code but the return value is giberish and unusable, as clearly I did not get the sequence value back from the call.

In the meantime, I will look into cx_oracle but like I said I am useasy about hardcoing login credentials into code like the examples I have seen. That is why I would prefer to do this using arcpy because the credentials are obtained from the users sde files which are
used connect to the database when they pick their input feature classes in the GP tool interface.

-Jim

I'm fairly new to Oracle, but lots of RDBMS development work on other systems (SQL Server), so the sequence thing is new for me.  But that doesn't seem to be the problem and I suspect it has to do with ArcSDESQLExecute inability to determine just what the heck is going on. Here's hoping you can find a way 🙂

You can use a DSN or TNS name for the cx_Oracle connection parameters and you should have no problem interfacing with your Stored Procedures.  We use the cx_Oracle library and it works very well for us (and we are in a Citrix envrionment too).

[JamesCrandall]

From http://resources.arcgis.com/en/help/main/10.2/index.html#//002z00000021000000

"The object will return a list of lists in the case where the statement returns rows from a table; for statements that do not return rows, it will return an indication of the success or failure of the statement (True for success, None for failure)."

So I suspect that since you are not returning rows, you are evaluating something else as a result.

Edit: try this and see if it produces your expected value...

sqlresults = connSQL.execute(sql)
for result in sqlresults:
   print result