(cx_Oracle.DatabaseError) ORA-00972

[Aurèle Durand]

Hello,

SqlAchemy automatically specify the table name in front of columns and thus my query parameters are too long and I get the "(cx_Oracle.DatabaseError) ORA-00972" error on Oracle. For example if my table name is "TABLE_NAME_TOO_LONG" and my columns are "id" and "name" a request will look like this:

SELECT "TABLE_NAME_TOO_LONG".id, "TABLE_NAME_TOO_LONG".name FROM "TABLE_NAME_TOO_LONG" where ... 

I could use alias for select request in order to bypass this issue if I understand well (https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_using_aliases.htm).

However for an insert I cannot find any solution. 

Is there a way to set an alias to a table name for an insert ? or remove the table name ?

Best regards ;)

[Jonathan Vanasco]

Try passing a small number to `label_length` in your `create_engine`.  Something like `label_length=5` might work.  I typically use 4-6 on Production/Staging servers, and no argument on Development.

    * https://docs.sqlalchemy.org/en/14/core/engines.html#sqlalchemy.create_engine.params.label_length

I don't have Oracle, so I am not sure if this fixes your exact problem or just related ones.

`label_length` will limit the length of aliases that sqlalchemy generates.  so you would see something like this:

- SELECT very_long_table_name_i_mean_it_is_long.id AS very_long_table_name_i_mean_it_is_long_id, very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo AS very_long_table_name_i_mean_it_is_long_foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo 

FROM very_long_table_name_i_mean_it_is_long

 LIMIT ? OFFSET ?

+ SELECT very_long_table_name_i_mean_it_is_long.id AS _1, very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo AS _2 

FROM very_long_table_name_i_mean_it_is_long

 LIMIT ? OFFSET ?

If the exception is caused by the generated alias (notice the underscore separator) `very_long_table_name_i_mean_it_is_long_foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo` being too long, that will solve your problem.

but if the exception is caused by (notice the dot separator in table/column addressing) "very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo" , then I don't know the remedy.

[Mike Bayer]

can you please include the SQLAlchemy version and Oracle version in use? as well as an example Table model.  These issues should be resolved for Oracle, the label names are truncated automatically.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/03d4cb6c-ce89-47ee-bf5d-58d0c162667fn%40googlegroups.com.