Take the 2-minute tour ×

Please suppose I have a CLOB field in an Oracle table, in which I have stored the creation script of a package/procedure/function.

I would like to remove all blanks at the end of every line, but:

a) DBMS_LOB.TRIM (CLOB field) is a procedure and not a function; b) RTRIM (CLOB) don't fail, but does not work to achieve this.

How can I solve my problem?

Please note that the spaces at the beginning of every line are useful for indentation of the PL/SQL source code stored in the CLOB field, so they haven't to be removed.

Thank you in advance for your kind help and cooperation.

share|improve this question
Have you considered opening the file , Iterate each row , trim the trailing spaces and write the CLOB back to the table ? –  haki Jul 31 '13 at 8:44
I would prefer a REGEXP_REPLACE approach. The sequence <SPACE> (how many times you want) followed by CHR(10) has to be replaced as CHR(10) simply. How can I achieve this? –  UltraCommit Jul 31 '13 at 8:52
select REGEXP_REPLACE (MY_CLOB_FIELD, '\s*'||chr(10), chr(10)) from MY_TABLE; is totally INEFFICIENT :( –  UltraCommit Jul 31 '13 at 9:26
are you trying to update the table, or simply remove spaces on select? How big is this table? –  tbone Jul 31 '13 at 9:46
This table is a backup table for PL/SQL packages, procedures and functions, so it grows every day. Every row contains two CLOBs: the CLOB of the package/procedure/function before review, and the CLOB of the package/procedure/function after review. Every CLOB can reach up to 50K rows of about 80 characters. –  UltraCommit Jul 31 '13 at 9:54

1 Answer 1

up vote 2 down vote accepted

To remove spaces at the end of each line you could use regexp_replace() regular expression function:

regexp_replace(col, '\s+('||CHR(10)||'|$)', chr(10)) 

Here is an example(Note: replace function is used just to highlight spaces):

with t1(col) as(
  select to_clob('begin  '||chr(10)||chr(32)||chr(32)||'something is going on here'||chr(32)||chr(32)||chr(10)|| 'end'||chr(32)||chr(32))
    from dual
select replace(col, ' ', '_') as with_spaces
     , replace(
               , ' '
               , '_'
               ) as without_spaces
  from t1


WITH_SPACES                           WITHOUT_SPACES
begin__                               begin
__something_is_going_on_here__        __something_is_going_on_here
end__                                 end
share|improve this answer
The PL/SQL scripts stored in the CLOB field are very long (about 50K rows), so this solutions - which works properly! - is inefficient, this means it takes a long time. Do you think that could exist a solution more efficient? –  UltraCommit Jul 31 '13 at 9:41

Your Answer


