Re: length of a long field

Mein Name wrote:

> How can I determine the length of a long field with SQL in ORACLE 7.3.4. ?
> 2 GB is a little bit inaccurate. The question is - how many bytes actually
> in this field.
>
> Thanxxxx! Michael Münzner

Michael,

     Actually, 2 GB is pretty accurate. If you want more precision then: a LONG is a variable length character field that can hold up to 2^32 -1 bytes.

     If you are asking how to determine the actual size of a LONG record in your database from SQL, about the only way to do it is executing an anonymous PL/SQL procedure which uses the built-in package DBMS_SQL.COLUMN_VALUE_LONG. COLUMN_VALUE_LONG allows you to retrieve LONG values from a dynamic query, so you could loop and grab chunks of the LONG and tally the total size up at the end.

Why not to do this:
Lets say you have the contents of entire audio CD in one record (roughly 500MB = 2^29).
Assume that you grab the maximum 32760 byte chunks (2^15) allowed in PL/SQL. You would have to loop through this procedure over 16,000 (2^14) times.

More visual:
Say you have a record that is the full 2GB. Dump to the screen (80x24 = 1920) to monitor the result. 2GB / 1920 = 2.2 million screens of text.

I can send you a prodedure that will give you the size of a LONG record, but if you need to do it as a common procedure, I would recomend using a different datatype like VARCHAR2 which can hold up to 2,000 characters in the database.

Jay!!! Received on Tue Oct 20 1998 - 17:13:53 CDT