How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562629] |
Thu, 02 August 2012 23:59  |
 |
kumar0828
Messages: 22 Registered: August 2012 Location: Bengaluru
|
Junior Member |
|
|
I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset.
But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error.
I know that DISTINCT keyword cannot be used for CLOB datatypes.
So can anyone help me out here with any work around/ alternate way to get the CLOB value in the SELECT QUERY.
So please help me out in this.
|
|
|
|
|
|
|
|
|
|
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562671 is a reply to message #562629] |
Fri, 03 August 2012 05:34   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
kumar0828 wrote on Fri, 03 August 2012 00:59So can anyone help me out here with any work around/ alternate way to get the CLOB value in the SELECT QUERY.
SQL> create table tbl(c clob)
2 /
Table created.
SQL> insert
2 into tbl
3 select dname
4 from dept d,
5 emp e
6 where e.deptno = d.deptno
7 /
14 rows created.
SQL> commit
2 /
Commit complete.
SQL> select distinct c
2 from tbl
3 /
select distinct c
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL> select c
2 from tbl t1
3 where not exists (
4 select 1
5 from tbl t2
6 where t2.rowid < t1.rowid
7 and dbms_lob.compare(t1.c,t2.c) = 0
8 )
9 /
C
--------------------------------------------------------------------------------
ACCOUNTING
RESEARCH
SALES
SQL>
SY.
|
|
|
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562673 is a reply to message #562629] |
Fri, 03 August 2012 05:54   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Another method:
SQL> CREATE OR REPLACE
2 TYPE clob_obj IS OBJECT(
3 c CLOB,
4 ORDER MEMBER FUNCTION equals(
5 p_c clob_obj
6 )
7 RETURN NUMBER
8 )
9 /
Type created.
SQL> CREATE OR REPLACE
2 TYPE BODY clob_obj
3 IS
4 ORDER MEMBER FUNCTION equals(
5 p_c clob_obj
6 )
7 RETURN NUMBER
8 IS
9 BEGIN
10 RETURN dbms_lob.compare(SELF.c,p_c.c);
11 END;
12 END;
13 /
Type body created.
SQL> with t as (
2 select distinct(clob_obj(c)) c
3 from tbl
4 )
5 select treat(c as clob_obj).c
6 from t
7 /
TREAT(CASCLOB_OBJ).C
--------------------------------------------------------------------------------
ACCOUNTING
RESEARCH
SALES
SQL>
SY.
|
|
|
|
|
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562775 is a reply to message #562773] |
Sat, 04 August 2012 15:30   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
No, sorry. By the time I got to your reply, I was skimming. Since I don't use objects that much, it never occurred to me to do what you did. It is very good, thanks.
Aside from an issue with null:
ORA-22951: NULL returned by ORDER method
It appears to work very well. I think I will use this if you don't mind. For those who need a more generalized example with additional attributes:
drop table tbl2;
create table tbl2 (a integer,b varchar2(30),c date,d clob)
/
declare
v1 clob;
begin
v1 := lpad('1',32767,'1')||lpad('2',32767,'2');
insert into tbl2 values (1,'a',sysdate,v1);
insert into tbl2 values (1,'a',sysdate,v1);
end;
/
commit
/
CREATE OR REPLACE
TYPE clob_obj IS OBJECT(
c CLOB,
ORDER MEMBER FUNCTION equals(
p_c clob_obj
)
RETURN NUMBER
)
/
CREATE OR REPLACE
TYPE BODY clob_obj
IS
ORDER MEMBER FUNCTION equals(
p_c clob_obj
)
RETURN NUMBER
IS
BEGIN
RETURN dbms_lob.compare(SELF.c,p_c.c);
END;
END;
/
set long 30
with t as (
select distinct a,b,c,clob_obj(d) d
from tbl2
)
select a,b,c,treat(d as clob_obj).c d
from t
/
Looking at just the data
16:26:49 SQL> select * from tbl2;
A B C D
---------- ------------------------------ -------------------- ------------------------------
1 a 04-aug-2012 16:23:14 111111111111111111111111111111
1 a 04-aug-2012 16:23:14 111111111111111111111111111111
2 rows selected.
Elapsed: 00:00:00.29
16:28:11 SQL> with t as (
16:28:19 2 select distinct a,b,c,clob_obj(d) d
16:28:19 3 from tbl2
16:28:19 4 )
16:28:19 5 select a,b,c,treat(d as clob_obj).c d
16:28:19 6 from t
16:28:19 7 /
A B C D
---------- ------------------------------ -------------------- ------------------------------
1 a 04-aug-2012 16:23:14 111111111111111111111111111111
1 row selected.
Elapsed: 00:00:00.20
Thanks SY. Kevin
|
|
|
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562776 is a reply to message #562775] |
Sat, 04 August 2012 15:54   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Kevin Meade wrote on Sat, 04 August 2012 16:30 Since I don't use objects that much, it never occurred to me to do what you did.
You do not have to use objects. And yes, I just gave a sample of a complete solution without dealing with NULLs. So first method ajusted to account for NULLs:
SQL> create table tbl(v varchar2(10),c clob)
2 /
Table created.
SQL> insert
2 into tbl
3 select job,
4 dname
5 from dept d,
6 emp e
7 where e.deptno = d.deptno
8 /
14 rows created.
SQL> commit;
Commit complete.
SQL> column c format a20
SQL> select v,
2 c
3 from tbl t1
4 where not exists (
5 select 1
6 from tbl t2
7 where t2.rowid < t1.rowid
8 and (dbms_lob.compare(t1.c,t2.c) = 0 or (t1.c is null and t2.c is null))
9 and (t2.v = t1.v or (t1.v is null and t2.v is null))
10 )
11 /
V C
---------- --------------------
MANAGER ACCOUNTING
PRESIDENT ACCOUNTING
CLERK ACCOUNTING
CLERK RESEARCH
MANAGER RESEARCH
ANALYST RESEARCH
SALESMAN SALES
MANAGER SALES
CLERK SALES
9 rows selected.
SQL>
Second method ajusted to account for NULLs:
SQL> CREATE OR REPLACE
2 TYPE clob_obj IS OBJECT(
3 c CLOB,
4 ORDER MEMBER FUNCTION equals(
5 p_c clob_obj
6 )
7 RETURN NUMBER
8 )
9 /
Type created.
SQL> CREATE OR REPLACE
2 TYPE BODY clob_obj
3 IS
4 ORDER MEMBER FUNCTION equals(
5 p_c clob_obj
6 )
7 RETURN NUMBER
8 IS
9 BEGIN
10 RETURN CASE
11 WHEN SELF.c IS NULL AND p_c.c IS NULL THEN 0
12 ELSE NVL(dbms_lob.compare(SELF.c,p_c.c),1)
13 END;
14 END;
15 END;
16 /
Type body created.
SQL> column c format a15
SQL> with t as (
2 select distinct v,
3 clob_obj(c) c
4 from tbl
5 )
6 select v,
7 treat(c as clob_obj).c as c
8 from t
9 /
V C
---------- ---------------
ANALYST RESEARCH
CLERK ACCOUNTING
CLERK RESEARCH
CLERK SALES
MANAGER ACCOUNTING
MANAGER RESEARCH
MANAGER SALES
PRESIDENT ACCOUNTING
SALESMAN SALES
9 rows selected.
SQL>
SY.
[Updated on: Sat, 04 August 2012 15:56] Report message to a moderator
|
|
|
|
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562823 is a reply to message #562822] |
Mon, 06 August 2012 02:54  |
John Watson
Messages: 8988 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Just a little extra, on the chance someone might find it useful: DBA_LOGSTDBY_NOT_UNIQUE lists all tables which do not have a primary key (or unique not null columns), flagging those which include an unbounded column. So you can determine which tables will have this problem, perhaps before it hits you:orcl> create table withoutlob(c1 number,c2 varchar2(2));
Table created.
orcl> create table withlob(c1 number, c2 clob);
Table created.
orcl> select * from DBA_LOGSTDBY_NOT_UNIQUE where owner='JON';
OWNER TABLE_NAME B
------------------------------ ------------------------------ -
JON PARTS N
JON WITHOUTLOB N
JON WITHLOB Y
orcl>
|
|
|