Home » SQL & PL/SQL » SQL & PL/SQL » Clob_Agg Order by (Oracle 11g, XP)
| Clob_Agg Order by [message #597054] |
Mon, 30 September 2013 11:21  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Hi,
I am having an issue with clob_agg...ordered by a field.
with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all
select 100,'Technician','AP' from dual union all
select 101,'Faculty','AP' from dual union all
select 102,'Teaching','CS' from dual union all
select 102,'Grad Assist','CS' from dual union all
select 102,'Secretary','AP' from dual union all
select 103,'Director','AP' from dual
)
select ASSIGNMENT_ID,
RTRIM(CLOBAGG(TITLE || ';'),';') TITLE,
RTRIM(CLOBAGG(EMP_CAT || ';'),';') EMP_CAT
from test_asg
group by ASSIGNMENT_ID
order by ASSIGNMENT_ID
Here I need clob concatenation based on ordered assignment name...
op should be based on title in alphabetical order(1st character)..
For 102 it should be
Grad Assist, Secretary, Teaching
G,S,T -- Alphabetical order
Code I tried : (didn't keep the data in the order)
with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all
select 100,'Technician','AP' from dual union all
select 101,'Faculty','AP' from dual union all
select 102,'Teaching','CS' from dual union all
select 102,'Grad Assist','CS' from dual union all
select 102,'Secretary','AP' from dual union all
select 103,'Director','AP' from dual
)
select ASSIGNMENT_ID,
RTRIM(clipmerge_dm.CLOBAGG(TITLE || ';'),';') TITLE,
RTRIM(clipmerge_dm.CLOBAGG(EMP_CAT || ';'),';') EMP_CAT
from (Select * from test_asg order by assignment_id, title)
group by ASSIGNMENT_ID
order by ASSIGNMENT_ID
Clobagg function :
create or replace
type clobagg_type as object(
text clob,
static function ODCIAggregateInitialize(
sctx in out clobagg_type
)
return number,
member function ODCIAggregateIterate(
self in out clobagg_type,
value in clob
)
return number,
member function ODCIAggregateTerminate(
self in clobagg_type,
returnvalue out clob,
flags in number
)
return number,
member function ODCIAggregateMerge(
self in out clobagg_type,
ctx2 in clobagg_type
)
return number
);
/
create or replace
type body clobagg_type
is
static function ODCIAggregateInitialize(
sctx in out clobagg_type
)
return number
is
begin
sctx := clobagg_type(null) ;
return ODCIConst.Success ;
end;
member function ODCIAggregateIterate(
self in out clobagg_type,
value in clob
)
return number
is
begin
self.text := self.text || value ;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self in clobagg_type,
returnvalue out clob,
flags in number
)
return number
is
begin
returnValue := self.text;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self in out clobagg_type ,
ctx2 in clobagg_type
)
return number
is
begin
self.text := self.text || ctx2.text;
return ODCIConst.Success;
end;
end;
/
create or replace
function clobagg(
input clob
)
return clob
deterministic
parallel_enable
aggregate using clobagg_type;
/
Thanks.
[Updated on: Mon, 30 September 2013 11:22] Report message to a moderator |
|
|
|
|
|
| Re: Clob_Agg Order by [message #597062 is a reply to message #597059] |
Mon, 30 September 2013 12:27  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Thanks Mike..I wrote PLSQL code to do this rather than using the function..
This code is not related to the example..but I hope it serves the purpose..
create or replace procedure prc_prog_notes
as
V_NOTE_ID VARCHAR2(1000);
V_NOTE_DATE DATE;
V_NOTE_STATUS VARCHAR2(1000);
V_NOTE_EDITOR_ID VARCHAR2(1000);
V_NOTES CLOB;
V_ENTERED_BY VARCHAR2(1000);
V_ENCOUNTER_ID VARCHAR2(1000);
CURSOR C_ALL_ENCOUNTERS IS
SELECT DISTINCT NOTE_ID,
NOTE_DATE,
NOTE_STATUS,
ENTERED_BY,
NOTE_EDITOR_ID,
ENCOUNTER_ID
FROM CM_PROGRESS_NOTES
/* WHERE ENCOUNTER_ID = 16*/;
CURSOR C_NOTES (P_ENC_ID VARCHAR2, P_NOTE_ID VARCHAR2) IS
SELECT
CP.ENCOUNTER_ID,
CP.NOTE_ID,
CP.NOTE_TEXT
FROM CM_PROGRESS_NOTES CP
WHERE CP.ENCOUNTER_ID = P_ENC_ID
AND CP.NOTE_ID = P_NOTE_ID
-- AND NOTE_ID = 18142669
-- AND ENCOUNTER_ID = 16
ORDER BY NOTE_ID, ENCOUNTER_ID, LINE;
TYPE V_TT IS TABLE OF C_NOTES%ROWTYPE INDEX BY PLS_INTEGER;
L_TT V_TT;
BEGIN
-- DBMS_OUTPUT.put_line('BEFORE LOOP');
FOR L1 IN C_ALL_ENCOUNTERS
LOOP
V_ENCOUNTER_ID := L1.ENCOUNTER_ID;
V_NOTE_ID := L1.NOTE_ID;
V_NOTE_DATE := L1.NOTE_DATE;
V_NOTE_STATUS := L1.NOTE_STATUS;
V_NOTE_EDITOR_ID := L1.NOTE_EDITOR_ID;
V_ENTERED_BY := L1.ENTERED_BY;
OPEN C_NOTES(L1.ENCOUNTER_ID,L1.NOTE_ID);
V_NOTES := '';
LOOP
FETCH C_NOTES BULK COLLECT INTO L_TT LIMIT 7500;
FOR indx IN 1 .. L_TT.COUNT
LOOP
V_NOTES := V_NOTES || L_TT(indx).NOTE_TEXT;
END LOOP;
EXIT WHEN L_TT.COUNT = 0;
END LOOP;
CLOSE C_NOTES;
INSERT /*+ append */
INTO PROGRESS_NOTES VALUES (V_ENCOUNTER_ID,V_NOTE_ID,V_NOTE_DATE,V_NOTE_STATUS,
V_NOTE_EDITOR_ID,V_ENTERED_BY,V_NOTES);
COMMIT;
End Loop;
END;
|
|
|
|
|
|
|
|
 Re: Clob_Agg Order by [message #597070 is a reply to message #597067] |
Mon, 30 September 2013 13:37 |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Do you mean commit outside the loop ...
No, I meant commit outside the procedure.
Quote:Also, what would be the approach if you dont want to use commit inside a proc?
The one I mentioned in my first post.
[Updated on: Mon, 30 September 2013 13:37] Report message to a moderator |
|
|
|
| Re: Clob_Agg Order by [message #597173 is a reply to message #597070] |
Tue, 01 October 2013 08:00 |
 |
ilikesql
Messages: 3
Registered: October 2013
Location: pune
|
Junior Member |
|
|
Try this
with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all
select 100,'Technician','AP' from dual union all
select 101,'Faculty','AP' from dual union all
select 102,'Teaching','CS' from dual union all
select 102,'Grad Assist','CS' from dual union all
select 102,'Secretary','AP' from dual union all
select 103,'Director','AP' from dual
),
test_order_by as (select * from test_asg order by assignment_id,title asc)
select ASSIGNMENT_ID,
RTRIM(wm_concat(TITLE || ';'),';') TITLE,
RTRIM(wm_concat(EMP_CAT || ';'),';') EMP_CAT
from test_order_by
group by ASSIGNMENT_ID
order by ASSIGNMENT_ID
|
|
|
|
|
|
| Re: Clob_Agg Order by [message #597235 is a reply to message #597054] |
Tue, 01 October 2013 19:56 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
rajivn786 wrote on Mon, 30 September 2013 12:21Here I need clob concatenation based on ordered assignment name...
It is not clear why you are using CLOB_AGG. If TITLE and EMP_CAT are VARCHAR2s but concatenation result can exceed 4000 bytes, use XMLAGG:
with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all
select 100,'Technician','AP' from dual union all
select 101,'Faculty','AP' from dual union all
select 102,'Teaching','CS' from dual union all
select 102,'Grad Assist','CS' from dual union all
select 102,'Secretary','AP' from dual union all
select 103,'Director','AP' from dual
)
select ASSIGNMENT_ID,
RTRIM(XMLAGG(XMLELEMENT(e,TITLE,';').EXTRACT('//text()') ORDER BY TITLE).GETCLOBVAL(),';') TITLE,
RTRIM(XMLAGG(XMLELEMENT(e,EMP_CAT,';').EXTRACT('//text()') ORDER BY TITLE).GETCLOBVAL(),';') EMP_CAT
from test_asg
group by ASSIGNMENT_ID
order by ASSIGNMENT_ID
/
ASSIGNMENT_ID TITLE EMP_CAT
------------- ------------------------------ ------------------------------
100 Comp info;Technician AP;AP
101 Faculty AP
102 Grad Assist;Secretary;Teaching CS;AP;CS
103 Director AP
SQL>
If TITLE/EMP_CAT is CLOB you have another issue - order by is not supported for CLOBs:
with test_asg as (
select 100 ASSIGNMENT_ID, to_clob('Comp info') TITLE, to_clob('AP') EMP_CAT from dual union all
select 100,to_clob('Technician'), to_clob('AP') from dual union all
select 101,to_clob('Faculty'), to_clob('AP') from dual union all
select 102,to_clob('Teaching'), to_clob('CS') from dual union all
select 102,to_clob('Grad Assist'), to_clob('CS') from dual union all
select 102,to_clob('Secretary'), to_clob('AP') from dual union all
select 103,to_clob('Director'), to_clob('AP') from dual
)
select ASSIGNMENT_ID,
RTRIM(XMLAGG(XMLELEMENT(e,TITLE,';').EXTRACT('//text()') ORDER BY TITLE).GETCLOBVAL(),';') TITLE,
RTRIM(XMLAGG(XMLELEMENT(e,EMP_CAT,';').EXTRACT('//text()') ORDER BY EMP_CAT).GETCLOBVAL(),';') EMP_CAT
from test_asg
group by ASSIGNMENT_ID
order by ASSIGNMENT_ID
/
RTRIM(XMLAGG(XMLELEMENT(e,EMP_CAT,';').EXTRACT('//text()') ORDER BY EMP_CAT).GETCLOBVAL(),';') EMP_CAT
*
ERROR at line 12:
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL>
One possible solution is object type with ORDER method which will allow sorting CLOBS:
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;
/
Now:
with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all
select 100,'Technician','AP' from dual union all
select 101,'Faculty','AP' from dual union all
select 102,'Teaching','CS' from dual union all
select 102,'Grad Assist','CS' from dual union all
select 102,'Secretary','AP' from dual union all
select 103,'Director','AP' from dual
),
t as (
select ASSIGNMENT_ID,
clob_obj(TITLE) TITLE,
clob_obj(EMP_CAT) EMP_CAT
from test_asg
)
select ASSIGNMENT_ID,
RTRIM(XMLAGG(XMLELEMENT(e,TITLE,';').EXTRACT('//text()') ORDER BY TITLE).GETCLOBVAL(),';') TITLE,
RTRIM(XMLAGG(XMLELEMENT(e,EMP_CAT,';').EXTRACT('//text()') ORDER BY EMP_CAT).GETCLOBVAL(),';') EMP_CAT
from t
group by ASSIGNMENT_ID
order by ASSIGNMENT_ID
/
ASSIGNMENT_ID TITLE EMP_CAT
------------- ------------------------------ ------------------------------
100 Comp info;Technician AP;AP
101 Faculty AP
102 Grad Assist;Secretary;Teaching AP;CS;CS
103 Director AP
SQL>
SY.
[Updated on: Tue, 01 October 2013 19:58] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Aug 04 10:34:13 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
