OracleXE problem saving BLOB

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ths
Posts: 53
Joined: Thu 29 Dec 2011 16:18

OracleXE problem saving BLOB

Post by ths » Mon 23 Jan 2017 10:32

Hello Support,

we have the following very nasty problem.
Saving BLOB into OracleXE (we do not see this problem on Oracle Server Standard Edition 11 and 12) in DIRECT mode results in the corrupted data (the size of this BLOB will strongly increase about factor of 2). For small BLOBs (we cannot give exact limit) it works correctely. In our case the limit is about 100 KB.
We use ODAC Version 9.5.16 (! but please see below!). We see that there were many changes and bug fixes afterwards.
Could you tell us please if this problem was reproduced and corrected? If this is the case, could you tell us
in what Version was it corrected?
Unfortunately we cannot check it on the newest version of components.

Thankyou very much,
Vladimir

P.S. We just have bought the last version of components with the hope to see that it works correctely. Unfortunately, we have the same problem. We use the newest version of ODAC 9.7.28 in DIRECT mode on Oracle XE

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

and Delphi 7. We are ready to provide a test project.
The behaviour is the same with Oracle XE 64

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

In our example the size of the source file is 573154 and saved to the database it is 944736 and is not readable.

We urgently need some answer.

Vladimir

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: OracleXE problem saving BLOB

Post by MaximG » Wed 25 Jan 2017 09:01

We checked ODAC 9.7.28 for Delphi 7 behavior when working with BLOB type fields using Oracle 11 XE. Unfortunately, we could not reproduce the described problem. Please compose and send us via the e-support form ( https://www.devart.com menu "Support"\"Request Support" ) a small sample in which the problem occurs.

ths
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: OracleXE problem saving BLOB

Post by ths » Wed 25 Jan 2017 10:01

Hello Maxim,

I have sent you a small test that does not work correctely by us in DIRECT mode with OracleXE 32 and 64.

Regards,
Vladimir

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: OracleXE problem saving BLOB

Post by MaximG » Wed 25 Jan 2017 13:32

We have received your project and checked its work in our environment. Unfortunately, we could not reproduce the described problem on ODAC 9.5.16 version or on the latest ODAC version 9.7.28 . We saved using your project a file of 100 megabytes size in the BLOB field and then successfully read it from this field. In case you have a version with source codes, make sure that your project uses when compiling their latest version. Please provide us NLS parameters of the server and your environment.

ths
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: OracleXE problem saving BLOB

Post by ths » Wed 25 Jan 2017 14:02

Hello Support,
please see our Oracle settings.

OracleXE 32

select * from v$version

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


SELECT * FROM nls_session_parameters
PARAMETER VALUE

NLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
NLS_CURRENCY €
NLS_ISO_CURRENCY GERMANY
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

SELECT * FROM nls_instance_parameters
PARAMETER VALUE

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

SELECT * FROM nls_database_parameters
PARAMETER VALUE

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.2.0


OracleXE 64

select * from v$version

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SELECT * FROM nls_session_parameters

NLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
NLS_CURRENCY €
NLS_ISO_CURRENCY GERMANY
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR HH24:MI:SS
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

SELECT * FROM nls_instance_parameters

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

SELECT * FROM nls_database_parameters

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.2.0

ths
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: OracleXE problem saving BLOB

Post by ths » Fri 27 Jan 2017 12:26

Hello Maxim,

may be the following additional information will help you to locolize the problem in DIRECT mode:
The test that we sent you will work in our environment in DIRECT mode if we remove Empty_Blob() and RETURNING clause from the query and set TemporaryLobUpdate to TRUE. Apparently the processing goes along some other way in this case and somehow avoids the error.

Regards,
Vladimir

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: OracleXE problem saving BLOB

Post by MaximG » Tue 31 Jan 2017 07:47

We continued to investigate the sent NLS-parameters. Unfortunately, we still failed to reproduce the described problem. The sent project is successfully executed when using the phrase Empty_Blob() in SQL query and without it. For further investigation, please, via the e-support form send us the script to create the oleobject table used in your project and also the file which when recorded causes the described error to occur in the BLOB field.

ths
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: OracleXE problem saving BLOB

Post by ths » Tue 31 Jan 2017 10:26

Hello Maxim,

we have found the following. I think this will be reproducible in your environment.
The behaviour is different depending on the clause SecureFile / BasicFile by the table creation:
CREATE TABLE OLEOBJECT
(
OBJECT BLOB
)
LOB (OBJECT) STORE AS SECUREFILE;
This will not work with default settings on OraQuery and SQL with Empty_Blob().
Changing to BASICFILE resolve this situation.
All this depends on the default settings in Oracle (see db_securefile parameter).
Actually we do not use the clause "LOB (OBJECT) STORE AS SECUREFILE" explicitly in our scripts. However the procedure is as follows - we create database under Oracle Server where default for db_securefiles is PREFERRED and use the dump of this database for OracleXE where default db_securefiles is PERMITTED. As result we have STORE AS SECUREFILE on OracleXE. For some reason this does not work with ODAC in direct mode.
As I wrote, we have found a workaround setting TemporaryLobUpdate to TRUE and removing Empty_Blob() clause. Then it works although this is probably a wrong way to solve the problem.
Please, let us know how you see this situation.

Regards,
Vladimir

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: OracleXE problem saving BLOB

Post by MaximG » Tue 31 Jan 2017 17:01

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: OracleXE problem saving BLOB

Post by MaximG » Wed 01 Feb 2017 14:59

Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next ODAC build. Currently, we can send you a night build of ODAC with the fix. For this, please specify your license number and Delphi version you are interested in via the e-support form.

Post Reply