Not sure that "insert all" works well

[nmoreaud]

Hello

I build queries that look like this:

insert all  into mydb.mytable values (:0, :1, :2, :3, :4, :5, :6, :7)
 into mydb.mytable values (:8, :9, :10, :11, :12, :13, :14, :15)
 into mydb.mytable values (:16, :17, :18, :19, :20, :21, :22, :23)
 into mydb.mytable values (:24, :25, :26, :27, :28, :29, :30, :31)
 into mydb.mytable values (:32, :33, :34, :35, :36, :37, :38, :39)
select * from dual

Depending on how many rows I try to insert at once, I get this error:

copy_cursor.execute(insert_query, params)

  File "src/oracledb/impl/base/cursor.pyx", line 409, in oracledb.base_impl.BaseCursorImpl._prepare_for_execute
  File "src/oracledb/impl/base/cursor.pyx", line 509, in oracledb.base_impl.BaseCursorImpl.bind_one
  File "src/oracledb/impl/base/cursor.pyx", line 66, in oracledb.base_impl.BaseCursorImpl._bind_values
  File "src/oracledb/impl/base/cursor.pyx", line 127, in oracledb.base_impl.BaseCursorImpl._bind_values_by_position
  File "src/oracledb/impl/base/bind_var.pyx", line 130, in oracledb.base_impl.BindVar._set_by_value
  File "src/oracledb/impl/base/var.pyx", line 87, in oracledb.base_impl.BaseVarImpl._check_and_set_value
  File "src/oracledb/impl/base/var.pyx", line 60, in oracledb.base_impl.BaseVarImpl._check_and_set_scalar_value
  File "src/oracledb/impl/base/connection.pyx", line 78, in oracledb.base_impl.BaseConnImpl._check_value
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x84 in position 63: invalid start byte

However, the same insertion with this syntax works well:

insert_query = f'insert intomydb.mytable values  (:1, :2, :3, :4, :5, :6, :7, :8)'
copy_cursor.executemany(insert_query, rows)

The error seems to happen for tables containing blobs.
Do you think there might be a parsing error or a memory issue in oracledb?

[nmoreaud]

The code looks like that, I wanted to benchmark the use of "executemany" vs "insert all".

with connect_to_copied_db(copy_db_info) as copy_connection: # sqlalchemy connection
    with src_cnx.engine.raw_connection().cursor() as src_cursor, \
            copy_connection.connection.cursor() as copy_cursor:

        for table_name, query in filtered_table_queries:
            src_cursor.arraysize = table_batch_size(table_name)

            try:
                src_cursor.execute(query)
            except Exception as e:
                logger.exception(f'Cannot execute query {query}')
                raise e from None

            fulltable = f'{copy_db_info.schema}.{table_name}'
            previous_nb_rows = 0
            while batch := src_cursor.fetchmany():
                nb_rows = len(batch)
                if nb_rows != previous_nb_rows:
                    num_columns = len(batch[0])
                    previous_nb_rows = nb_rows

                    insert_query = 'insert all '
                    for irow in range(nb_rows):
                        placeholders = ', '.join([f":{irow*num_columns + i + 1}" for i in range(num_columns)])
                        insert_query += f' into {fulltable} values ({", ".join([placeholders])})\n'
                    insert_query += 'select * from dual'

                params = []
                for row in batch:
                    params.extend(list(row))

                try:
                    copy_cursor.execute(insert_query, params)
                except Exception as e:
                    logger.exception(f'Cannot insert into {table_name}')
                    raise e from None
            copy_connection.connection.commit()

[cjbj]

Thanks for the report.

Note that in 23ai you can also try a multi-value insert https://github.com/oracle/python-oracledb/blob/v3.0.0/samples/sql/create_schema_23.sql#L62-L70

Regarding the benchmark: using a large number of binds could hit the issue in oracle/python-cx_Oracle#567 Although binds are handled linearly in python-oracledb Thin mode itself, and in Thick mode the Oracle Client 23ai libraries improved its handling of large numbers of them, the DB itself still doesn't give the performance I think it should when a large number of binds are used in a statement - I was chasing this up just last week.

Fundamentally, the best recommendation is to use SQL*Loader for loading data.

[nmoreaud]

Thanks for your answer.
SQL*Loader seems to be a good tool to load data, however I haven't found the equivalent tool to export data to a CSV.
Do you know a performant way to generate a CSV, compatible with SQL*Loader, from a query (table filtering) on the client computer?

I have considered the following alternatives:

• impdp + network_link + filtering is very slow, and there are bugs with spatial columns
• imp/exp would be great but are not maintained anymore and not compatible with BINARY_FLOAT

[anthony-tuininga]

With python-oracledb 3.0 you can now write to a data frame, convert that to Pandas and then use Pandas to write a CSV file. That should be relatively quick. You could also write to Parquet instead of CSV -- depending on what you want to do with the data afterwards. We are actively working on methods of quick ingestion (similar to what SQL*Loader is capable of doing) thta should improve the performance of loading data as well.

[cjbj]

@anthony-tuininga I'm not so sure data frames are the way to go if you have > 1 column. While writing a CSV file via data frames is faster with a single column, when fetching more columns I think it's better to fetch in a traditional row form since the CSV is also in row form (see write_csv.py). At least, that's what my basic testing indicated. I'd be happy to be proven wrong.

[anthony-tuininga]

Do you think there might be a parsing error or a memory issue in oracledb?

Anything is possible! :-) Can you share a test case that reliably demonstrates the problem? A quick glance suggests, however, that you are passing bytes that are not UTF-8 encoded to a string bind variable. I'd be happy to take a look, though, if you can supply a test case.

[cjbj]

@nmoreaud how is your testing going?

[nmoreaud]

I stopped testing with insert all, because I had other things to do.
I have tested if my data contains non UTF-8 characters, but I didn't find any.
However, UTF-8 errors may come from a trigger from my database, I should test again to be sure.