Clob with trim [message #358742] |
Wed, 12 November 2008 06:07  |
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 #358758 is a reply to message #358742] |
Wed, 12 November 2008 07:06   |
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 #358763 is a reply to message #358760] |
Wed, 12 November 2008 07:15   |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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 #358846 is a reply to message #358830] |
Wed, 12 November 2008 12:01   |
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 #391051 is a reply to message #358742] |
Tue, 10 March 2009 16:25   |
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?
|
|
|
|
|
|