DBD::Oracle::db ora_lob_read failed: ORA-01403: no data found

[joniux]

Hi

While trying to export the data, the program do an infinite loop, and shows this message in the screen

DBD::Oracle::db ora_lob_read failed: ORA-01403: no data found (DBD INVALID_HANDLE: OCILobCharSetForm) at /usr/local/share/perl/5.20.2/Ora2Pg.pm line 10151.
DBD::Oracle::db ora_lob_read failed: ORA-01403: no data found (DBD INVALID_HANDLE: OCILobCharSetForm) at /usr/local/share/perl/5.20.2/Ora2Pg.pm line 10151.

After pressing Ctrl + C the message is

Received terminating signal (INT).
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(HOST=dev01)(PROTOCOL=tcp)(PORT=1526))(ADDRESS=(HOST=dev01)(PROTOCOL=tcp)(PORT=1521)))(CONNECT_DATA=(SID=XXXX))) at /usr/local/bin/ora2pg line 230.

It seems a problem related with handling BLOB data.

Kind regards
Jose Zorrilla

[darold]

Please use latest development code from github, commit a77ba53 might solves this issue. This patch fix empty LOB data export with Oracle Lob locator (NO_LOB_LOCATOR set to 0) but also adds a condition to prevent the infinite loop. Please give it a try and let me know.

[joniux]

Hi

I installed the latest version, and fails again but now the error is different and return to command prompt

Dumping data from ROAM_PRODUCT to file: ./data/ROAM_PRODUCT_data.sql
Truncating table ROAM_PRODUCT...
Dumping data from ROAM_PRODUCT to file: ./data/ROAM_PRODUCT_data.sql
Looking how to retrieve data from ROAM_PRODUCT...
Fetching all data from ROAM_PRODUCT tuples...
DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr at /usr/local/share/perl/5.20.2/Ora2Pg.pm line 10190.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(HOST=dev01)(PROTOCOL=tcp)(PORT=1526))(ADDRESS=(HOST=dev01)(PROTOCOL=tcp)(PORT=1521)))(CONNECT_DATA=(SID=XXXX))) at /usr/local/share/perl/5.20.2/Ora2Pg.pm line 10190.

The version is

$ora2pg -v
Ora2Pg v16.2

Thanks for your help!
JZ

[darold]

I'm not able to reproduce the issue. Please post here the Oracle database and DBD::Oracle perl module versions.

As a workaround I think you might not export LOB using locators. Set NO_LOB_LOCATOR to 0 in your configuration file and set LONGREADLEN to the highest lob object size. If it is smaller ora2pg will fail with an error about truncated LOB, just increase LONGREADLEN into ora2pg.conf to a higher value and repeat the export.

Note that exporting BLOB is slow and using multiprocess will help a lot.

Let me know.

Best regards,

[darold]

A major issue have been fixed in last release 17.0 about export of BLOB with lob locator, please upgrade.