jwfox1

How to select on an Oracle Database Sequence value in arcpy

Discussion created by jwfox1 on Nov 19, 2013
Latest reply on Nov 20, 2013 by jamesfreddyc
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

Outcomes