Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle XMLType data size

 
Santosh Ramachandrula
Ranch Hand
Posts: 252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
I have an table in Oracle DB that has a column of type XMLType, I want to know how do I find/calculate the average size of XML document stored.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Santosh,
How about the following query:
SELECT avg(length(column.getStringVal())) from tableName


Note this query is likely to perform slowly. If it's one time query for data gathering, that's probably ok.
 
Santosh Ramachandrula
Ranch Hand
Posts: 252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Jeanne,
Thanks for your reply. I tried the SQL query given by you, Oracle complains about ORA-00904 getStringVal invalid identifier


SELECT avg(length(column.getStringVal())) from tableName


Once again column is of type XMLTYPE
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Santosh,
getStringVal() is the name of the method. See an Oracle doc reference.

Try double checking you got all the characters including the dot before the method.
 
Santosh Ramachandrula
Ranch Hand
Posts: 252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Jeanne, Could that be because I am using Oracle 9i client? I will try the 10G client
 
Chris Hendy
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't know why - still looking into it - but I get the same Oracle error as Santosh.

However if I alias the table it works ok



One thing to bear in mind, if one of your XMLTYPE values exceeds 4000 characters you will get a

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Try



instead.
 
Santosh Ramachandrula
Ranch Hand
Posts: 252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Jeanne/Chris,

1. I tried the query with 10G client but does not work.

2. I tried
SELECT avg(length(a.col1.getStringVal()))
FROM tab1 a;



but got ORA-06502: PL/SQL: numeric or value error: character string buffer too small

3. Tried
SELECT avg(length(a.col1.getClobVal())) from tab1 a;
as mentioned by Chris Hendy and it returned a number to me and I am thinking that it is the average size in BYTES.


I will keep you guys posted.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic