CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
smackenzie
smackenzie

Reputation: 3022

Getting a sequence number from Oracle DB in Python cx_Oracle

I am trying to get the next value of an Oracle sequence.

def get_change_id():
    # get a sequence number for each change, this will be used to group before and after changes

    id = cursor_analytics.var(cx_Oracle.NUMBER)

    sql = "select SNAPSHOT_GENERAL_SEQ.nextval into :next_id from sys.dual"
    cursor_analytics.execute(sql, {"next_id":id})

    change_id = id.getvalue()

    return change_id

I am getting this error message:

Traceback (most recent call last):
  File "C:/ariel_deltas/main.py", line 93, in <module>
    print(get_change_id())
  File "C:\ariel_deltas\snapshot.py", line 125, in get_change_id
    cursor_analytics.execute(sql, {"next_id":id})
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

If I wrap a begin and end around the SQL, I get this:

def get_change_id():
    # get a sequence number for each change, this will be used to group before and after changes

    id = cursor_analytics.var(cx_Oracle.NUMBER)

    sql = """"
        BEGIN
            select SNAPSHOT_GENERAL_SEQ.nextval into :next_id from sys.dual;
        END
        """

    cursor_analytics.execute(sql, {"next_id":id})

    change_id = id.getvalue()

    return change_id

Traceback (most recent call last): File "C:/ariel_deltas/main.py", line 93, in print(get_change_id()) File "C:\ariel_deltas\snapshot.py", line 130, in get_change_id cursor_analytics.execute(sql, {"next_id":id}) cx_Oracle.DatabaseError: ORA-01740: missing double quote in identifier

Doing this works but feels wrong:

def get_change_id():
    # get a sequence number for each change, this will be used to group before and after changes

    sql = 'select SNAPSHOT_GENERAL_SEQ.nextval from sys.dual'

    cursor_analytics.execute(sql)

    for row in cursor_analytics.fetchall():
        r = reg(cursor_analytics, row, False)
        change_id = r.NEXTVAL
        return change_id

Upvotes: 1

Views: 1244

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64969

You get an the error "missing double-quote in identifier" in your second function because you are using four double-quote characters instead of three at the start of a multiline string:

    # ----1234
    sql = """"
        BEGIN
        ...
        """

The fourth " ends up being the first character of the string sent to Oracle, and there are no other " characters in the string, hence the error about a missing double-quote. Delete it and this function should start working.

Your first function will not work because the INTO clause of a SELECT query is only supported in PL/SQL.

Finally, unless you're using an old version of Oracle (11g or earlier I think), you can just assign the next sequence value directly without using a query:

    sql = """
        BEGIN
            :next_id := SNAPSHOT_GENERAL_SEQ.nextval;
        END;
        """

Upvotes: 1

Related Questions