Subject: Re: Help with Long Column Problem From: padderz Sender: padderz Newsgroups: comp.databases.oracle User-Agent: forums@dbforums.com References: Organization: forums@dbforums.com X-Newsreader: forums@dbforums.com X-References: 121378|423477 X-Original-NNTP-Posting-Host: 66.33.64.232 Message-ID: <3b7164ee$1@usenetgateway.com> X-Original-Trace: 8 Aug 2001 12:12:30 -0500, 66.33.64.232 Lines: 26 X-Complaints-To: abuse@usenetserver.com X-Abuse-Info: Please be sure to forward a copy of ALL headers X-Abuse-Info: Otherwise we will be unable to process your complaint properly. NNTP-Posting-Date: Wed, 08 Aug 2001 12:05:32 EDT Date: 8 Aug 2001 12:12:30 -0500 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