mORMot Open Source

dottor_jeckill

Member

Registered: 2017-11-22

Posts: 5

Oracle - Ora-345 TFDParam - Firedac

Hi to all, i'm using the Mormot version 1.18 to access to a Oracle DB (latest version), in my definition of TSQLRecord have a long text field (is a JSON data of 5/6 thousand of bytes), this was converted in NCLOB field on the Oracle DB.

This is the definition of the TSQLRecord :

        TASTelemetry = class(TSQLRecord)
        private
                fWORKORDER: RawUTF8;
                fGIORNO: integer;
                fCREATO_IL: TDateTime;
                fANNO: integer;
                fCENTRO_DI_LAVORO: Int64;
                fMESE: integer;
                fDATA: RawUTF8;
                fCUSTOM_KEY2: RawUTF8;
                fCUSTOM_KEY3: RawUTF8;
                fCUSTOM_KEY1: RawUTF8;
                fCUSTOM_KEY4: RawUTF8;
                fCUSTOM_KEY5: RawUTF8;
        public
        published
                property ID_CENTRO_DI_LAVORO: Int64 read fCENTRO_DI_LAVORO write fCENTRO_DI_LAVORO;
                property WORKORDER: RawUTF8 index 30 read fWORKORDER write fWORKORDER;
                property CUSTOM_KEY1: RawUTF8 index 30 read fCUSTOM_KEY1 write fCUSTOM_KEY1;
                property CUSTOM_KEY2: RawUTF8 index 30 read fCUSTOM_KEY2 write fCUSTOM_KEY2;
                property CUSTOM_KEY3: RawUTF8 index 30 read fCUSTOM_KEY3 write fCUSTOM_KEY3;
                property CUSTOM_KEY4: RawUTF8 index 30 read fCUSTOM_KEY4 write fCUSTOM_KEY4;
                property CUSTOM_KEY5: RawUTF8 index 30 read fCUSTOM_KEY5 write fCUSTOM_KEY5;
                property ANNO: integer read fANNO write fANNO;
                property MESE: integer read fMESE write fMESE;
                property GIORNO: integer read fGIORNO write fGIORNO;
                property CREATO_IL: TDateTime read fCREATO_IL write fCREATO_IL;
                property DATA: RawUTF8 index 10000 read fDATA write fDATA;
        end;

when i write the data on the DB, with the TSQLRestClient.Add method, if the DATA field is less than 2000 bytes, it works fine, if it have more (like 5000 bytes) i have an error :
'[FireDAC][Phys][Ora]-345. Data too large for variable [:MA]. Max len = [2000], actual len = [5335] Hint: set the TFDParam.Size to a greater value'

I found the solution to convert the RawUTF8 into TSQLRawBlob field... but is very strange, the same structure works fine on MySQL Server, on SQL Server, always with Firedac, but not in Oracle.

Someone know how to increase the TFDParam.Size in the ORM?

Thanks in advance.

Cristiano.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,649

Website

Re: Oracle - Ora-345 TFDParam - Firedac

Did you try with our direct SynDBOracle driver?

dottor_jeckill

Member

Registered: 2017-11-22

Posts: 5

Re: Oracle - Ora-345 TFDParam - Firedac

Hi AB,

thanks for your reply. I tried also the SynDBOracle (better performance...), but in this case i must use only the TSQLRawBlob, retruieving the data, with a normal REST call cause a large number of exceptions and don't work. If i use the TSQLRawBlob, no problem, the two rest calls (one for the record and one for the blob) are perfect.

I think that the problem could be the data into the JSON uploaded. Is a data logger and there are many couples, with a great amount of decimal, date, time, and byte data.

To make a test, i uploaded some data with the TSQLRawBlob, then changed the field DATA to RawUTF8 and tried to retrieve the record with the rest call, it works becouse the field DATA contains Byte64 encoded data.

It's strange, becouse in the others DB it work perfect, only on the latest Oracle (with APEX).

For now i must use the two calls and the TSQLRawBlob.. i must change some client, but it's ok.

Thanks.