How to keep continuously write to Oracle table even if table is not accessible?

Question

I am trying to insert multiple records into the one Oracle table continuously. For which I have written below python script.

import cx_Oracle
import config

connection = None
try:
    # Make a connection
    connection = cx_Oracle.connect(
        config.username,
        config.password,
        config.dsn,
        encoding=config.encoding)

    # show the version of the Oracle Database
    print(connection.version)

    # Insert 20000 records
    for i in range(1, 20001):
        cursor = connection.cursor()
        sql = "INSERT into SCHEMA.ABC (EVENT_ID, EVENT_TIME) VALUES( "+ str(i)+" , CURRENT_TIMESTAMP)"
        cursor.execute(sql)
        connection.commit()
except cx_Oracle.Error as error:
    print(error)
finally:
    if connection:
        connection.close()

So, During the insert, when I change the table name it just create an exception and comes out of script(as the table is not available and cannot write). What I want is, Even if when I do the rename and table is not available, the script needs to keep continuously trying insert. Is there a way this can be possible?

Do not commit inside the loop : this will lock database and modification from elsewhere will be suspended. Commit once you have finished loop — Ptit Xav, Oct 11 '21 at 14:40
If I do that, It won't replicate the current production scenario where commit is happening after each and every statement — Viral Patel, Oct 11 '21 at 14:50
The main problem is the script stops when the table is not available. I want the script to keep retrying until the table gets available — Viral Patel, Oct 11 '21 at 14:51
Add a while (data not inserted) loop around database execution . Put this one in a try except pattern . The try for executing database stuff and exit of while loop. The except part does a sleep. — Ptit Xav, Oct 11 '21 at 14:57
See [this similar question](https://stackoverflow.com/questions/27835674/end-a-sleep-loop-in-python) for examples of a "sleep loop" to keep retrying an action. — kfinity, Oct 11 '21 at 15:07
I expect the solution would want to be checking connection validity, and closing/reopening connection if needed. A pool (with max=1) can be useful, since the pool has some internal checks and internal reconnection logic. — Christopher Jones, Oct 11 '21 at 20:56

score 2 · Accepted Answer · answered Oct 11 '21 at 15:26

Here's an example of what Ptit Xav was talking about. I added some code to quit after a max number of retries, since that's often desirable.

# Insert 20000 records
for i in range(1, 20001):
    retry_count = 0
    data_inserted = False
    while not data_inserted:
        try:
            cursor = connection.cursor()
            sql = "INSERT into SCHEMA.ABC (EVENT_ID, EVENT_TIME) VALUES( "+ str(i)+" , CURRENT_TIMESTAMP)"
            cursor.execute(sql)
            connection.commit()
            data_inserted = True
        except cx_Oracle.Error as error:
            print(error)
            time.sleep(5) # wait for 5 seconds between retries
            retry_count += 1
            if retry_count > 100:
               print(f"Retry count exceeded on record {i}, quitting")
               break
    else:
        # continue to next record if the data was inserted
        continue
    # retry count was exceeded; break the for loop.
    break

See this answer for more explanation of the while... else logic.

Instead of using "+" string concatenation you should use bind variables for security. And since the statement is re-executed you also need bind variables for performance & scalability. This problem was noted in the other answer. — Christopher Jones, Oct 12 '21 at 01:37

score 2 · Answer 2 · answered Oct 11 '21 at 19:11

2

You may want to encapsule the insert logik in a function that catches the possible exception and performs the retry

def safe_insert(con, i):
    """
    insert a row with retry after exception
    """
    retry_cnt = 0
    sql_text = "insert into ABC(EVENT_ID, EVENT_TIME) VALUES(:EVENT_ID,CURRENT_TIMESTAMP) "
    while True:
        try:
            with con.cursor() as cur:
                cur.execute(sql_text, [i])
                con.commit()
                return
        except cx_Oracle.Error as error:
            print(f'error on inserting row {i}')
            print(error)
            time.sleep(1)
            retry_cnt += 1
            if (retry_cnt > 10):
                raise error

Similar to @kfinity's answer I also added a limit on retry - if this limit is exceeded the function raise an exception.

Note also that the function uses bind variables in the INSERT statement which is preferable to the concatenation of the values in the statement.

The usage is as simple as

for i in range(1, 20001): 
    safe_insert(con, i)

answered Oct 11 '21 at 19:11

Marmite Bomber

19,886
4
26
53

The poster's definition of 'table is not available and cannot write' isn't clear, but perhaps the solution could use [`connection.callTimeout`](https://cx-oracle.readthedocs.io/en/latest/api_manual/connection.html#Connection.call_timeout). And/or the sqlnet.ora file could have settings like `RECV_TIMEOUT`, see the cx_Oracle manual [Network Configuration](https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#network-configuration). – Christopher Jones Oct 12 '21 at 01:40
1

A side comment: since the example commits for each INSERT, it would be more efficient to turn on [autocommit](https://cx-oracle.readthedocs.io/en/latest/user_guide/txn_management.html#autocommitting). This will halve the number of 'round-trips' between Python and Oracle DB. – Christopher Jones Oct 12 '21 at 01:43
Interesting comments thanks @ChristopherJones. *can't write*: I guess the OP impelements some DIY *rolling logging* with *rename table* plus *create new table* and wan't loose rows at the time the table doesn't exists (so timeout will not help). *Autocommit*: could be also good idea for this toy example. – Marmite Bomber Oct 12 '21 at 05:54

How to keep continuously write to Oracle table even if table is not accessible?

2 Answers2