ORA-01459: invalid length for variable character string

[adrianboangiu]

Hello,

I am trying to migrate data from Postgres to Oracle and I get the error ORA-01459: invalid length for variable character string when I transfer a certain table with a statement of the type insert into ora_schema.sometable( column1,..., columnn ) select column1,..., columnn from sometable. It already worked for tens of tables before the one giving the error. The problem is that the table causing the error has 320 columns and I don't know whether the error message is for a specific column in the database or for the statement as a whole. Is there a way to have some more information or I have to check the size of all the varchar columns and see if there are differences? I thought that if there would be differences I would have a precise error like one I already had when there was a problem of column size ORA-12899: value too large for column "COSWIN"."T_CATEGORY"."MDCT_DESCRIPTION" (actual: 270, maximum: 256).

Any help is appreciated.

Best regards,

[laurenz]

I'd say the case is pretty clear: you are trying to insert data in an Oracle table column that is defined too short.

If you defined it as VARCHAR2(256), and the PostgreSQL column is defined as character varying(256), the problem may well be that Oracle by default defines column limits in bytes rather than characters. If you have non-ASCII characters in your data, that would cause such an error message (assuming that you are using UTF-8 in Oracle).

The simplest solution may be to define the Oracle column as VARCHAR2(256 CHAR), so that the length limit is in characters.

[adrianboangiu]

I am aware about this problem. Let me explain. Our scripts that create the Oracle database objects are based on NLS_LENGTH_SEMANTICS, i.e. they do not precise a unit for the VARCHAR sizes. Thdrefore I have checked and the database is created with NLS_LENGTH_SEMANTICS = CHAR. The proof is that when I get the description of any table (not only the one having problems) the size of VARCHAR2 columns is not specified. In the USER_TAB_COLUMNS we see the sizes in BYTE. So, the database structure is created in CHAR. Moreover, the error in case of wrong VARCHAR2 column size is

ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-12899: value too large for column "COSWIN"."T_CATEGORY"."MDCT_DESCRIPTION" (actual: 270, maximum: 256)

This was one error when the Oracle database was created with BYTE NLS_LENGTH_SEMANTICS. That is why I was asking if there are any limitations on the size of the statement (which is actually 11298bytes) or any other limitations.

[laurenz]

I see. I misread your question, because I had focused on the error in the question body rather than on the error in the title.

Do the tables have so many columns that the statement is that large?

Anyway, I have no clue what could cause that. Oracle's description of the error message is rather cryptic. One thing that would help is the complete error message from oracle_fdw; that should help figure out if the error happens during "bind" or "execute".

But I guess that I need more information to pin down the cause of the error. Try to determine if the error occurs only for certain rows. You can run statements like

INSERT INTO ... SELECT ... WHERE id BETWEEN 10000 AND 20000;

or similar and see if you always get the error or only in certain cases. By narrowing down the window, you should be able to identify a single row, if the error is caused by certain data. Then it would of course be most helpful if I knew the offending data and the definition of the Oracle table and the PostgreSQL foreign table.

Essentially, if I can reproduce the problem, I have good odds to fix it.

Additional information that would be helpful:

• the version of Oracle client, Oracle server, PostgreSQL server and oracle_fdw that you are using
• the server encoding on the PostgreSQL side and the database character set on the Oracle side

[adrianboangiu]

I have investigated the problem and I found the problem. I was helped by the fact that another table having more columns has been transferred without problems. I checked all table character varying columns and I discovered that all have the same size in Postgres and Oracle. So I understood why the message does not precise a column and a limit size. The problem came from a column of the table which is NUMBER( 2,0 ) in Oracle and is NUMERIC( 2, 0 ) in Postgres instead of smallint. Thank you for your kind support.

[adrianboangiu]

By changing the type in the Postgres database I obtain the same error. I will try to see if the error is given for a certain row in the table or is given for all the rows.

[laurenz]

Thanks for your investigation. So you suspect that the PostgreSQL columns don't match the Oracle columns, right?

[adrianboangiu]

I transferred the rows one by one and I found the first row that gives the error. It is the 105th row. The problem is that there is nothing "different" on that row compared to those that work. The table has 320 columns and among these there are 4 text columns. That row, like many others have only one text column filled (327 characters). And I have transferred 37 rows with much more characters (2962 maximum). For all the other rows in the table I have either null values or values similar to those on the lines transferred.

[laurenz]

We are getting closer. Perhaps you can give me the CREATE TABLE statement from Oracle, the CREATE FOREIGN TABLE statement from PostgreSQL, the CREATE TABLE statement from PostgreSQL that holds the offending row and an INSERT statement that holds the offending row. That should be enough to reproduce the problem.

[adrianboangiu]

Please find attached t_work_order_oracle - definition of the table in the Oracle database (with only the inline constraints) t_work_order_oracle - definition of the table in the Postgres database (idem) t_work_order_ora_coswin - definition of the foreign table t_work_order_data - data pg_dumped from the postgres database

t_work_order_data.zip t_work_order_ora_coswin.zip t_work_order_oracle.zip t_work_order_postgres.zip

[laurenz]

I can reproduce it with your data, but I have no clue what is going on. The error happens only for certain rows; one of them has PK_WORK_ORDER = 2653. Dealing with a table with 321 columns isn't any fun. I'll mark it as a bug, but I cannot fix it right now.

[adrianboangiu]

I found several rows having the problem and by checking what is common to all those rows I found that all have a column which is not shown as NULL by the pgAdmin but is empty. The column is wowo_notes_helper.

[laurenz]

That's a great observation that makes my task much easier. Thanks!

[laurenz]

I think I found and fixed the problem. Could you verify that the latest version works for you? I hope you don't mind if oracle_fdw inserts empty strings as NULLs in Oracle.

[adrianboangiu]

I tried to test the correction. The problem is that I was unable to make the extension. I used the same environment and the same procedure that I used previously to make the extension. First I had to add -std=c99 in the makefile otherwise I had a lot of errors. Afterwards, i had just some warnings:

11 [17:34:05][root@quasar23 /home/admin/Downloads/oracle_fdw-master]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -DLINUX_OOM_SCORE_ADJ=0 -fPIC -I"/usr/local/instantclient_19/sdk/include" -I"/usr/local/instantclient_19/oci/include" -I"/usr/local/instantclient_19/rdbms/public" -I"/usr/local/instantclient_19/" -std=c99  -I. -I./ -I/usr/pgpure/postgres/13/include/server -I/usr/pgpure/postgres/13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_fdw.o oracle_fdw.c
oracle_fdw.c: In function ‘oraclePlanForeignModify’:
oracle_fdw.c:1590:3: warning: implicit declaration of function ‘get_rel_all_updated_cols’ [-Wimplicit-function-declaration]
   updated_cols = get_rel_all_updated_cols(root, roi);
   ^
oracle_fdw.c:1590:16: warning: assignment makes pointer from integer without a cast [enabled by default]
   updated_cols = get_rel_all_updated_cols(root, roi);
                ^
oracle_fdw.c: At top level:
cc1: warning: unrecognized command line option "-Wno-stringop-truncation" [enabled by default]
cc1: warning: unrecognized command line option "-Wno-format-truncation" [enabled by default]
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -DLINUX_OOM_SCORE_ADJ=0 -fPIC -I"/usr/local/instantclient_19/sdk/include" -I"/usr/local/instantclient_19/oci/include" -I"/usr/local/instantclient_19/rdbms/public" -I"/usr/local/instantclient_19/" -std=c99  -I. -I./ -I/usr/pgpure/postgres/13/include/server -I/usr/pgpure/postgres/13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -DLINUX_OOM_SCORE_ADJ=0 -fPIC -I"/usr/local/instantclient_19/sdk/include" -I"/usr/local/instantclient_19/oci/include" -I"/usr/local/instantclient_19/rdbms/public" -I"/usr/local/instantclient_19/" -std=c99  -I. -I./ -I/usr/pgpure/postgres/13/include/server -I/usr/pgpure/postgres/13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -DLINUX_OOM_SCORE_ADJ=0 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/pgpure/postgres/13/lib   -L/opt/rh/llvm-toolset-7.0/root/usr/lib64  -L/usr/lib -Wl,--as-needed -Wl,-rpath,'/usr/pgpure/postgres/13/lib',--enable-new-dtags  -L"/usr/local/instantclient_19/" -L"/usr/local/instantclient_19/bin" -L"/usr/local/instantclient_19/lib" -L"/usr/local/instantclient_19/lib/amd64"  -lclntsh 
12 [17:34:15][root@quasar23 /home/admin/Downloads/oracle_fdw-master]$ make install
/usr/bin/mkdir -p '/usr/pgpure/postgres/13/lib'
/usr/bin/mkdir -p '/usr/pgpure/postgres/13/share/extension'
/usr/bin/mkdir -p '/usr/pgpure/postgres/13/share/extension'
/usr/bin/mkdir -p '/usr/pgpure/postgres/13/share/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/pgpure/postgres/13/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgpure/postgres/13/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql  '/usr/pgpure/postgres/13/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgpure/postgres/13/share/doc/extension/'

but it is impossible to create the extension in Postgres:

localhost:5432 ali0002prd@ali0002prd=# create extension if not exists oracle_fdw;
ERROR:  could not load library "/usr/pgpure/postgres/13/lib/oracle_fdw.so": /usr/pgpure/postgres/13/lib/oracle_fdw.so: undefined symbol: get_rel_all_updated_cols

I am using instantclient 19 but unfortunatelly (because I see it afterwards) my version of Postgres is 13.9. What can I do?

[laurenz]

PostgreSQL 13.0 to 13.9 don't work with oracle_fdw. The reason is a backward incompatibility introduced in PostgreSQL commit ad38e2f891.

You'll have to update to 13.10 or better (but you should be running the latest minor release (currently, 13.15) anyway).