Home » SQL & PL/SQL » SQL & PL/SQL » clob column (11.1.0.6)
clob column [message #442048] Thu, 04 February 2010 09:45 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Hi all,

I have a clob column. I have a requirement where i need to find the length of the longest word in the clob column.

any ideas?
Re: clob column [message #442051 is a reply to message #442048] Thu, 04 February 2010 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
Ananthi wrote on Thu, 04 February 2010 07:45
Hi all,

I have a clob column. I have a requirement where i need to find the length of the longest word in the clob column.

any ideas?


get chunk
loop while more

what defines "word"?

Re: clob column [message #442057 is a reply to message #442048] Thu, 04 February 2010 10:22 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
You should also consider creating your own object type that extends the clob datatype. Consider this code:

SQL> create or replace type my_extended_clob is object
  2  (
  3    thevalue clob
  4      , member function longest_word_length return number
  5  )
  6  /

Type created.

SQL> show errors
No errors.
SQL> create or replace type body my_extended_clob is
  2  
  3     member function longest_word_length return number is
  4     begin
  5         -- you logic here
  6         return (30);
  7     end;
  8  
  9  end;
 10  /

Type body created.

SQL> show errors
No errors.
SQL> select my_extended_clob(to_clob('abc')).longest_word_length() from dual;

MY_EXTENDED_CLOB(TO_CLOB('ABC')).LONGEST_WORD_LENGTH()
------------------------------------------------------
                                                    30

1 row selected.


Naturally I have left the actual logic to get what you want for you to define. Additionally I make no warranty that you can actually do what you want inside an object type, most specifically because you are using CLOB type and there are limits to clobs.

The choice to go with a straight up function, or the same function as a member function of an object type, is really a packaging decision and a decision centered around coding philosophy. But consider XML. Oracle XML type is a glorified clob. They have essentially done the above. So if you think their XML type is useful then you must consider that creating your own type would also be useful. Though I suppose in the spirit of things your needs are not really for a new object type, but rather just to examine an existing type. Still, it is an intersting perspective to consider.

Good luck, Kevin
Previous Topic: can we use MERGE statement in this scenario
Next Topic: how to remove the special characters (All)
Goto Forum:
  


Current Time: Fri Sep 30 03:47:28 CDT 2016

Total time taken to generate the page: 0.12899 seconds