I am attempting to post changes to an Oracle BLOB field, which is configued in the DA Schema Modeler as datblob/dabtOraBlob with the UniDAC driver. I am receiving the error:
ORA-01461: can bind a LONG value only for insert into a LONG column
I have two other columns configured as CLOB which post without issue.
Please advise how to resolve this issue. Note this was working prior to our recent upgrade to RO/DA 9.4.0.1353
Also, as an after thought, I switched to FireDAC and received this error:
[FireDAC][Phys][Ora]-338. Param [REPORT_DEF] type changed from [ftBlob] to [ftHugeBlob]. Query must be reprepared. Possible reason: an assignment to a TFDParam.AsXXX property implicitly changed the parameter data type. Hint: use the TFDParam.Value or appropriate TFDParam.AsXXX property
I finally resolved this problem, which however raises a statement/request.
BACKGROUND
When using a UniDAC driver, creating/updating the fields from a given query incorrectly sets the data field type for BLOB/CLOB oracle columns, which was the root of my recently solved problem. This has been an issue for a very long time and I have simply tolerated it. I will try to explain with a simple table.
Oracle DataType Auto Mapped DataType Auto Mapped BlobType Result
____________________________________________________________________________________
BLOB datBlob dabtUnknown Errors
CLOB datBlob dabtUnknown Errors
One must then remember of all the myriad of queries which ones contain BLOB/CLOB, and then remember to tweak the columns as follows:
Oracle DataType Manual Mapped DataType Manual Mapped BlobType Result
____________________________________________________________________________________
BLOB datBlob dabtBlob No Errors
CLOB datMemo dabtUnknown No Errors
REQUEST
Please consider modifying DA to correctly (what ever correctly means) auto determine the BLOB/CLOB fields such that they work as expected. I for one am confused by a CLOB being interpreted as a datMemo (I.e. LONG). To me an Oracle CLOB should map as a datBlob / dabtMemo. And if the BlobType must be set for a BLOB to work, then set it accordingly as well.
What sql type you have used for your table? datAutoSQL or datSQL?
in datAutoSQL mode (when DA generates SELECT) we detect these types:
else if (aTypeName = 'RAW') or (aTypeName = 'LONG RAW') OR
(aTypeName = 'BLOB') OR (aTypeName = 'LOB') OR
(aTypeName = 'BFILE') OR (aTypeName = 'CFILE') then begin
Result:= datBlob;
subtype:=dabtOraBlob;
end
else if (aTypeName = 'LONG') or (aTypeName = 'CLOB') then begin
Result:= datMemo;
subtype:=dabtOraClob;
end
for datSQL (i.e. when SQL was typed manually), field types were detected by UniDAC …
I never use datAutoSQL as it does not provide the flexibility I am looking for. Are you suggesting that while using UniDAC and datAutoSQL that I am stuck with this irritating and inconvenient behaviour? Note that FIreDAC behaves the same way. Can’t the RODA code fire regardless of sql type?
Here is an example of how we write most our our queries:
SELECT
R.REPORT_ID
, R.REPORT_DEF -- BLOB
, R.SCRIPT_DEF -- CLOB
, R.DESCRIPT -- CLOB
, LK.SHORT_DESC AS SALC_REPORT_TYPE -- LOG_CHANGES = FALSE
, CASE WHEN R.LOCK_PW IS NULL THEN 'F'
ELSE 'T'
END AS SALC_IS_LOCKED -- LOG_CHANGES = FALSE
, CAST(FX_DISPLAY_NAME(R.MODIFY_USER_ID, 'DISPLAY') AS VARCHAR(200)) AS SALC_MODIFY_USER -- LOG_CHANGES = FALSE
, CAST(FX_DISPLAY_NAME(R.CREATE_USER_ID, 'DISPLAY') AS VARCHAR(200)) AS SALC_CREATE_USER -- LOG_CHANGES = FALSE
FROM WD_REPORT R
INNER JOIN WD_LOOK_UP LK
ON R.REPORT_TYPE_LK = LK.LOOKUP_ID
WHERE R.REPORT_OBJ_ID = :REPORT_OBJ_ID
AND {WHERE}
ORDER BY EFF_DATE
the main problem: when we have just Table name (stAutoSQL mode), we can grab metadata for this table from DB.
in case custom SQL (stSQL mode) we can’t perform above actions because custom SQL can fetch data from different sources ( i.e. embedded selects, views, stored procedures, etc) so we use data that returns Direct Access Component , in your case this is UniDAC. in some cases it returns valid types, in some - invalid, as in your case.
workaround for stSQL mode - validate field types, PK, etc manually.
Can we not do something where behind the scenes, an stAutoSQL is performed which matches fields names against those retrieved with the stSQL?. Then simply change the fields to those defined with stAutoSQL
That approach seems to me an easy compromise. One could even add a “flag” such that the default of the flag uses the “classic” behavior so there is no potentially breaking changes.
select
view_a.A as FLD1,
view_b.B as FLD2,
table1.FLD3
from
table`,
view_a,
view_b
where ....
and table1 contains FLD1, FLD2, FLD3.
according to your logic, we should grab types for FLD1/FLD2 from table1 because this table contains these fields…
we prefer to use types what DAC (=UniDAC) returns. if they are invalid, users always can fix them manually.
just checked. this code was present in DAv3 so looks like old versions of Oracle 9 or prior require this syntax. Probably we can remove it as deprecated code …
