How to find the maximum size of data in a column

[mpramods]

Hi,

I have a column defined as LONG which contains some XML code. Is it possible to find 'The maximum size of data in this column'?.

The data in the column looks as below(I have pasted 3 rows of data from this column).

<!-- MemberObjectGroups="#ID#XXXXIS:NAM" cost="11111" costDescription="Home Loans" employeeStatus="J" employeeType="D" firstname="John" fullname="John D Simmer" jobCode="UNH5535" ssn id="111111111" origin="USA" prov="5" res="#ID#FG794GD55B732D6372D35D5:1DE4A4:102D4C1372GG:-6SS4" startDate="02/13/2003" tedsDN="HD=KJDK347,OU=NAM,O=DIS"-->
<Attribute name='ssn id' type='string' value='111111111'/>

<!-- MemberObjectGroups="#ID#XXXXIS:NAM" cost="11111" costDescription="Home Loans" employeeStatus="J" employeeType="D" firstname="John" fullname="John D Simmer" jobCode="UNH5535" ssn id="456372828" origin="USA" prov="5" res="#ID#FG794GD55B732D35D5:1DE4A4:102D4C1372GG:-6SS4" startDate="02/13/2003" tedsDN="HD=KJDK347,OU=NAM,O=DIS"-->
<Attribute name='ssn id' type='string' value='456372828'/>

<!-- MemberObjectGroups="#ID#XXXXIS:NAM" cost="11111" costDescription="Home Loans" employeeStatus="J" employeeType="D" firstname="John" fullname="John D Simmer" jobCode="UNH5535" ssn id="FH647383J" origin="USA" prov="5" res="#ID#FG794GD55BD356G63H73sK367732D35D5:1DE4A4:102D4C1372GG:-6SS4" startDate="02/13/2003" tedsDN="HD=KJDK347,OU=NAM,O=DIS"-->
<Attribute name='ssn id' type='string' value='FH647383J'/>

Thanks,
Pramod

 

[Beilstwh]

The long datatype should never be used unless you are accessing a legacy system that forces you to use long. You should be using CLOB for this, which allowes most of the string commands (substr, like, length...) to be used.

Bill
Oracle DBA/Developer
New York State, USA

 

[carp]

While I agree with Bill's assessment of LONG datatypes, I also know you don't always get to choose what you inherit.

I'm sure somebody (SantaMufasa?!) will have a much more elegant way to do this, but the following is a kluge that will get your answer for you (sorry - I just don't work with LONGs so can't help too much here):
Scenario: Table x has one column of datatype LONG.
You need to find the row with the longest LONG string in it.

CREATE TABLE temp_lobs AS 
   SELECT rowid row_id, to_lob(y) the_clobs 
     FROM x;
SELECT x.*, dbms_lob.getlength(c.the_clobs) 
  FROM x, temp_lobs c
 WHERE x.rowid = c.row_id
   AND dbms_lob.getlength(c.the_clobs) = 
           (SELECT max(dbms_lob.getlength(the_clobs))
              FROM temp_lobs);

Once you are done with it, don't forget to drop table temp_lobs;

 

[ddiamond]

If the maximum size is all you care about, then the following might work:

SELECT max(dbms_lob.getlength(to_lob(y)))
  FROM x;

 

[mpramods]

carp,

I am getting tablespace issues when I try to run the commands you provided. I cannot get the tablespace increased for this.

ddiamond,

I get an error with your command:
SELECT max(dbms_lob.getlength(to_lob(Y))) FROM X;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG.

Do you guys have any other suggestions?

Thanks,
Pramod

 

[carp]

Offhand, no. Would it be possible to build the table in a different tablespace besides your default tablespace?
The knock on LONGs has always been that they're rather cumbersome to deal with. TO_LOB is the only function I'm aware of for casting them into something a little easier to deal with, but this can only be used in a subquery to an INSERT statement.

 

[ddiamond]

but this can only be used in a subquery to an INSERT statement.

That would explain why my version of the query did not work.