Is OracleDB is running too fast (?) and not waiting for the previous statement to finish?

[lmeerwood]

I am using OracleDB with Airflow. I have a list of SQL commands that I have parsed from an SQL file and I am trying to run them with OracleDB. There are no parameters for any of the queries.

The queries are all run in order, and they all share the same cursor and connection.

I am having issue with a stored procedure we use to drop a table if it exists. Essentially we use this method for dropping a table:

CREATE OR REPLACE PROCEDURE DROP_TABLE_IF_EXISTS(in_table_name varchar2)
IS
  -- from https://stackoverflow.com/a/1799215/6107339
  c int;
  begin
     select count(*) into c from user_tables where table_name = upper('table_name');
     if c = 1 then
        execute immediate 'drop table table_name';
     end if;
end;

Normally in our SQL code we would call it by EXECUTE DROP_TABLE_IF_EXISTS('TABLE_NAME'). However, for OracleDB and Airflow I convert them all to BEGIN DROP_TABLE_IF_EXISTS('TABLE_NAME'); END: as I parse the file.

The issue I am having is that straight after we run recreate the table with new data, however, the database complains that the table still exist even though we should have just dropped it.

I got the commands to work properly by adding a sql command to compute the first 150 digits of the fibonacci series between each command. By slowing down the execution everything works as expected. Because of this, I feel that the command is not waiting for the procedure to finish.

Does anyone know how I would go about telling OracleDB to wait for the previous command to finish before continuing?

Things I have tried:

• Enabling auto commit
• Manually commiting after every statement
• Slowing down execution by calculating fibonacci (this works, but is less than ideal)

[cjbj]

Have you asked in an Airflow forum? How do you know that the connection is shared?

Are you using Thin or Thick mode? Which version of python-oracledb are you using?

[lmeerwood]

Hey, thanks for the quick reply!

Have you asked in an Airflow forum?

Not yet. I wanted to make sure things at the lowest level is working. Especially because I just upgraded Airflow to Python-OracleDb from cx-oracle. There's an element of wanting to make sure I didn't mess anything up. I'm not part of the Airflow team, I just contributed a pull request.

How do you know that the connection is shared.

This is the code that runs each SQL command.

        scalar = isinstance(sql, str)
        if scalar:
            sql = [sql]
        #  snip
        with closing(self.get_conn()) as conn:
            if self.supports_autocommit:
                self.set_autocommit(conn, autocommit)

            with closing(conn.cursor()) as cur:
                results = []
                for sql_statement in sql:
                    self._run_command(cur, sql_statement, parameters)

And then _run_command simply calls the execute function of the cursor:

        if parameters:
            cur.execute(sql_statement, parameters)
        else:
            cur.execute(sql_statement)

This is in the dbapi.py file, from which the OracleHook inherits from.

Are you using Thin of Thick mode?

Thin mode. (Thin mode is great! Thanks for implementing it.)

Which version of pythyn-oracledb are you using?

1.0.1

I started following the code down, trying to track down why it wasn't waiting for the command to complete. Unfortunately I can't figure out which part is not waiting for the command to complete. I find it odd that something isn't as I have some very long create table statements and it's waiting for all of those.

[lmeerwood]

Sorry, it turns out the issue was operator error, and had nothing to do with run speed.

I was accidentally sending through some empty SQL queries. I haven't looked into it more yet but I think that sending an empty SQL query string causes the last query to repeat.

Edit: After looking into it more, it appears that OracleDB won't update self._statement if the statement passed in is an empty string.

Edit 2: I am an idiot. Just above where the code I was looking at was this statement in the documentation:

A reference to the statement will be retained by the cursor. If None or
the same string object is passed in again, the cursor will execute that
statement again without performing a prepare or rebinding and
redefining.

[anthony-tuininga]

Ah, I see you figured that out. :-)

[lmeerwood]

Thanks, this has been a massive case of me not taking the time to read the documentation properly. Thanks for the reply, and appologies for taking up your time.

[cjbj]

We can improve the doc on this to mention the "" case.

It's great you are creating a PR for Airflow. Can you point us at it?

[lmeerwood]

@cjbj, sure. Here ya go: apache/airflow#24311

It has already been merged in. It's in testing at the moment.

[cjbj]

Brilliant !