Re: Help with Long Column Problem

errrrmm...I don't think that's strictly true...

LONG columns of any length can be fetched piecewise using DBMS_SQL package, so you have no problem there.

It is generally understood that you can only insert up to 32k of LONG using PL/SQL. However you can exploit a bug in DBMS_SQL to allow binding of up to a maximum of 64k. This is achieved by concatenation of 2x32k VARCHAR2 variables during binding. Following example illustrates...

DECLARE new_value VARCHAR2 (32767) := RPAD ('new value ', 32767, 'new value '); cur PLS_INTEGER := DBMS_SQL.open_cursor; sql_text VARCHAR2 (255) := 'UPDATE table_with_long_column SET long_column = :val'; fdbk PLS_INTEGER; BEGIN DBMS_SQL.parse (cur, sql_text, DBMS_SQL.native); DBMS_SQL.bind_variable (cur, ':val', new_value || new_value); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.close_cursor (cur); END;

--
Padderz
SYSOP, RevealNet PL/SQL Pipeline
Oracle Developer, Cellular Operations UK

Posted via dBforums
http://dbforums.com