Home » SQL & PL/SQL » SQL & PL/SQL » Clob with trim
Clob with trim [message #358742] Wed, 12 November 2008 06:07 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

How to use the trim with LOb.

Here below is my scenario

select trim('abcccc ') from dual;
o/p abcccc (It trims spaces).

I tried the same with dbms_lob.trim function. I am getting an error.

select dbms_lob.trim('abcccc ') from dual;


Could pls let me know how to trim empty spaces for clob string.


Thanks in advance
Re: Clob with trim [message #358752 is a reply to message #358742] Wed, 12 November 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no error in your post.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Clob with trim [message #358758 is a reply to message #358742] Wed, 12 November 2008 07:06 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,



SQL> select trim('abcccc ') from dual;

TRIM('
------
abcccc

SQL> select dbms_lob.trim('abcccc ') from dual;
select dbms_lob.trim('abcccc ') from dual
       *
ERROR at line 1:
ORA-00904: "DBMS_LOB"."TRIM": invalid identifier


Thanks
Re: Clob with trim [message #358760 is a reply to message #358758] Wed, 12 November 2008 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still no version.

Regards
Michel
Re: Clob with trim [message #358763 is a reply to message #358760] Wed, 12 November 2008 07:15 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Here below is the version. I am using.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Thanks

[Updated on: Wed, 12 November 2008 07:19] by Moderator

Report message to a moderator

Re: Clob with trim [message #358765 is a reply to message #358763] Wed, 12 November 2008 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select owner, table_owner, table_name from dba_synonyms where synonym_name='DBMS_LOB' order by 1,2,3;
select * from user_objects where object_name='DBMS_LOB';
select grantee, owner, privilege from dba_tab_privs where table_name='DBMS_LOB' order by 1,2,3;

Post result of all those.

Regards
Michel

Re: Clob with trim [message #358766 is a reply to message #358765] Wed, 12 November 2008 07:30 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi Michel,

Pls find the result of the queries.


SQL> select owner, table_owner, table_name from dba_synonyms where synonym_name='DBMS_LOB' order by 1,2,3;

OWNER                          TABLE_OWNER                                      
------------------------------ ------------------------------                   
TABLE_NAME                                                                      
------------------------------                                                  
PUBLIC                         SYS                                              
DBMS_LOB                                                                        
                                                                                

SQL> select * from user_objects where object_name='DBMS_LOB';

no rows selected

SQL> select grantee, owner, privilege from dba_tab_privs where table_name='DBMS_LOB' order by 1,2,3;

GRANTEE                        OWNER                                            
------------------------------ ------------------------------                   
PRIVILEGE                                                                       
----------------------------------------                                        
PUBLIC                         SYS                                              
EXECUTE    


Thanks

[Updated on: Wed, 12 November 2008 07:31]

Report message to a moderator

Re: Clob with trim [message #358768 is a reply to message #358766] Wed, 12 November 2008 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Silly me, dbms_lob.trim is not a function, it is a procedure, you can't directly use it in SQL.
But TRIM works on clob.
SQL> create table t (col clob);

Table created.

SQL> insert into t values (lpad(lpad('X',1000,'X'),4000));

1 row created.

SQL> select length(col), length(trim(col)) from t;
LENGTH(COL) LENGTH(TRIM(COL))
----------- -----------------
       4000              1000

1 row selected.


Regards
Michel
Re: Clob with trim [message #358797 is a reply to message #358768] Wed, 12 November 2008 09:09 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi Michel,

It works for small strings. If I want to use the same trim function for strings which are of 400kb. It doesn't works right.

In that case i have to use dbms_lob.trim function.

So let me know one small example with dbms_lob.trim.

Thanks
Re: Clob with trim [message #358830 is a reply to message #358797] Wed, 12 November 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (col clob);

Table created.

SQL> insert into t values (lpad(lpad('X',1000,'X'),4000));

1 row created.

SQL> update t set col=col||col;

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> select length(col), length(trim(col)) from t;
LENGTH(COL) LENGTH(TRIM(COL))
----------- -----------------
     256000            253000

1 row selected.

Regards
Michel
Re: Clob with trim [message #358846 is a reply to message #358830] Wed, 12 November 2008 12:01 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Its other way. When do we use the function dbms_lob.trim?

If we are able to do get same functionality as trim then whats the need of dbms_lob.trim.

Thanks
Re: Clob with trim [message #358850 is a reply to message #358846] Wed, 12 November 2008 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In PL/SQL.
And remember dbms_lob.trim (and substr and...) was there when you could not do it with built-in functions in previous versions.

Regards
Michel
Re: Clob with trim [message #391051 is a reply to message #358742] Tue, 10 March 2009 16:25 Go to previous messageGo to next message
tivrfoa
Messages: 2
Registered: March 2009
Junior Member
hello!

it doesn't work in just trim:

create table clob_table(clob_column clob);
insert into clob_table values('something');
select clob_column
from clob_table
where trim(clob_column) = 'something';


results in an error: inconsistent datatypes.

someone knows how to solve this problem?
how can I compare a clob with a string?
Re: Clob with trim [message #391054 is a reply to message #391051] Tue, 10 March 2009 17:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> create table clob_table(clob_column clob);

Table created.

SCOTT@orcl_11g> insert into clob_table values('something ');

1 row created.

SCOTT@orcl_11g> select clob_column
2 from clob_table
3 where trim (dbms_lob.substr (clob_column, 4000, 1)) = 'something'
4 /

CLOB_COLUMN
-------------------------------------------------------------------------
something

SCOTT@orcl_11g>
Re: Clob with trim [message #391092 is a reply to message #391051] Wed, 11 March 2009 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
someone knows how to solve this problem?
how can I compare a clob with a string?

Use a version that was released in this century.

Regards
Michel
Re: Clob with trim [message #391203 is a reply to message #358742] Wed, 11 March 2009 07:47 Go to previous message
tivrfoa
Messages: 2
Registered: March 2009
Junior Member
thanks very much Barbara!! =D

I'm using Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product.
Previous Topic: EXISTS statement...
Next Topic: check if system time falls between start_time and end_time (merged)
Goto Forum:
  


Current Time: Wed Dec 07 09:08:21 CST 2016

Total time taken to generate the page: 0.25696 seconds