more than 5 delete statements in a loop gives an error when using uuid type in the where clause

[viktorras]

I have noticed a strange problem where trying to delete a number of rows one by one in a loop gives an error when the loop is more than 5 records long and the where clause is an uuid

The code below gives this exception: [HV00L] ERROR: error executing query: OCINumberFromText failed to convert parameter Detail: OCI-22061: invalid format text [99EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE] Where: SQL statement "delete from jupiter_link.pej$pejling p where p.guid=rec.meas_uuid" PL/pgSQL function inline_code_block line 15 at SQL statement

If the number of records is less than 6, then there is no problem.

jupiter_link.pej$pejling.guid is an RAW(16) column in Oracle and an uuid column in the postgresql external table

do $$ declare rec RECORD; begin FOR rec IN select column1::uuid meas_uuid from (values ('8feee298-7ce5-4761-9487-34cab831fead')) as t CROSS JOIN generate_series(1,6) as x LOOP delete from jupiter_link.pej$pejling p where p.guid=rec.meas_uuid; end loop; end; $$

SELECT oracle_diag('jupiter_dblnk'); oracle_fdw 2.5.0, PostgreSQL 13.11 (Debian 13.11-1.pgdg120+1), Oracle client 21.1.0.0.0, Oracle server 12.2.0.1.0

[laurenz]

That looks wrong indeed.

Could you try with oracle_fdw 2.6.0? I changed the way numeric parameters are handled in commit 3ca26bba190fd2e38b4d3f7f811d05417c14ba56, which may (or may not) have fixed this problem.

[laurenz]

Did you get to try the latest version?

[viktorras]

Not yet. I have to get my server administrator to upgrade. But I will test it for sure.

[viktorras]

I have tried with oracle_fdw 2.6.0

The problem is still there when the loop has more than five records, but the Oracle error is a little different

[HV00L] ERROR: error executing query: OCINumberFromText failed to convert parameter Detail: OCI-22062: invalid input string [8feee298-7ce5-4761-9487-34cab831fead] Where: SQL statement "delete from jupiter_link.pej$pejling p where p.guid=rec.meas_uuid" PL/pgSQL function inline_code_block line 7 at SQL statement

SELECT oracle_diag('jupiter_dblnk'); oracle_fdw 2.6.0, PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1), Oracle client 21.12.0.0.0, Oracle server 12.2.0.1.0

[laurenz]

That looks like you are trying to substitute an UUID parameter for a numeric Oracle column.

Can I see that CREATE TABLE statement from Oracle and the CREATE FOREIGN TABLE statement from PostgreSQL?

[viktorras]

I have made this simplified version of the tables which also gives the same error:

--in Oracle create table VRA.PEJ$PEJLING_TEST ( PEJLINGSID NUMBER(8) not null, BORID NUMBER(8) not null, GUID RAW(16) default SYS_GUID() not null, constraint XPKPEJ$PEJLING_TEST primary key (pejlingsid) );

--in Postgresql create foreign table jupiter_link.pej$pejling_test ( pejlingsid integer options (key 'true') not null, borid integer not null, guid uuid not null ) server jupiter_dblnk options (schema 'VRA', table 'PEJ$PEJLING_TEST');

[laurenz]

I can reproduce it with a prepared statement:

PREPARE stmt(uuid) AS DELETE FROM pej$pejling_test p WHERE p.guid = $1;
PREPARE
EXECUTE stmt('8feee298-7ce5-4761-9487-34cab831fead');
DELETE 0
EXECUTE stmt('8feee298-7ce5-4761-9487-34cab831fead');
DELETE 0
EXECUTE stmt('8feee298-7ce5-4761-9487-34cab831fead');
DELETE 0
EXECUTE stmt('8feee298-7ce5-4761-9487-34cab831fead');
DELETE 0
EXECUTE stmt('8feee298-7ce5-4761-9487-34cab831fead');
DELETE 0
EXECUTE stmt('8feee298-7ce5-4761-9487-34cab831fead');
ERROR:  error executing query: OCINumberFromText failed to convert parameter
DETAIL:  OCI-22062: invalid input string [8feee298-7ce5-4761-9487-34cab831fead]

I'll have a look.

[laurenz]

I have pushed a fix.

Please try with the latest development version to see if it works for you.

[viktorras]

I can confirm that the problem is gone in oracle_fdw 2.7.0devel. Thank you.

[laurenz]

Thanks for testing.