Re: Length of Long Column

A copy of this was sent to "Mike Bertrand" <mbertrand_at_clinicaldata.com> (if that email address didn't require changing) On Wed, 28 Jul 1999 12:06:29 -0400, you wrote:

>Is there an easy way to determine the length of a long column? What we are
>actually trying to do is append a value to a long column from a procedure or
>function. We have an example with the dbms_sql package but are getting the
>message function cannot guarantee to not update the database.
>
>Thanks,
>
>Mike
>
>

plsql will only deal with 32k of a LONG. If you are trying to write a stored procedure or function that updates a long, it will look like this:

is

   l_rec table_name%rowtype;
   ...
begin

   select * into l_rec from table_name where <some condition>;

   l_rec.long_column := l_rec.long_column || more_data;

   update table_name set long_column = l_rec.long_column    where some_condition;

end;

if the total length of the long column ever exceeds 32k, you *will not* be able to do the update in plsql -- you will need Pro*c or OCI to some other 3gl like interface.

dbms_sql cannot every be called in a function from sql. you are trying to write a function like "get_long_length" that uses dbms_sql to piecewise read the long and add up bytes -- you are then trying to call get_long_length from SQL (eg: select get_long_length( ... ) from T). You cannot do this as dbms_sql can easily write to the database (it can update as easily as it can select) and we do not permit functions called in sql to write to the database.

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation