I work on a perl/DBI OR mapper that generates UPDATE statements for entities such that NULL values won't override existing values by enclosing them in NVL, like in the following schematic example.
UPDATE ... SET col_name = NVL(?, col_name)
WHERE pk_name = ?If col_name happens to be a LOB type (CLOB in the concrete case) and the parameter bound is longer than (I guess) 4000 (the max length of a VARCHAR2 column type) I get an
ORA-01461: can bind a LONG value only for insert into a LONG column (DBD ERROR:
OCIStmtExecute)
if I do a $sth->bind_param($index, $value) call. (Note that this doesn't happen if the length of the value is < 4000.)
If I do supply the LOB type and column as suggested in the POD, as in
$sth->bind_param($index, $value, { ora_type => ORA_CLOB, ora_field => 'SEQ' });
then I receive an
ORA-04043: object biosequence does not exist (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch)
biosequence is a (non-public) synonym to the table that contains the LOB column. The real table is under a different user and also has a different name. So, if there was an attempt to look up anything about 'biosequence' in Oracle's dictionaries then that would probably fail.
It seems to me that what I'm left with is keep different statements around depending on whether the value to be bound to the LOB column is undef or not. (If it's not undef I can leave out the NVL() wrap and I suppose it would work then.)
Does anybody have any other idea, or am I doing something wrong, or have I overlooked something?
-hilmar -- ------------------------------------------------------------- Hilmar Lapp email: lapp at gnf.org GNF, San Diego, Ca. 92121 phone: +1-858-812-1757 -------------------------------------------------------------
