On Sunday, July 13, 2003, at 12:19 PM, Tim Bunce wrote:
I also tried against the real table (instead of the synonym to it) and then even wrapping the parameter in NVL() works just fine (!).
Good.
Hi Tim, thanks for your response. To give another glimpse on how this story unfolds, I'm now scrapping my second iteration to solve this problem, which was to edit the generated update statement to replace the synonym with the real fully qualified USER.TABLE (the dictionary view ALL_SYNONYMS let's you figure out that piece). I then added { ora_type=>ORA_CLOB, ora_field=>'SEQ' } to every $sth->bind_param() call for that specific statement when the value is longer than 4000 chars. (that piece of code doesn't know about the order of columns in the statement.)
The problem with this approach is that it works fine if the first bind for that NVL-wrapped LOB column gets passed the hash of type parameters, but it doesn't if the first one does not pass those parameters (because the value to be bound happens to be shorter than 4000 chars). I was back then at the ORA-01461 exception.
Apparently subsequently (after the first execution - or first bind for that column?) passed type parameters are ignored if the statement handle is the same as before.
Is this a feature or a bug :-) I can imagine it's a constraint imposed by OCI, but I don't know. At any rate, my humble suggestion is to mention this in the documentation as I was really unprepared for that and it took me a long night to figure out what was going on.
-hilmar -- ------------------------------------------------------------- Hilmar Lapp email: lapp at gnf.org GNF, San Diego, Ca. 92121 phone: +1-858-812-1757 -------------------------------------------------------------
