Home » SQL & PL/SQL » SQL & PL/SQL » clob aggregation (merged)
clob aggregation (merged) [message #464767] |
Fri, 09 July 2010 05:42 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
What is the best way to aggregate million rows? I defined user defined function "clob"...but never stops executing...The one which I wrote was :
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;
/
Then doing :
select id,
clbaggg(' ' || combined_val) combinedval
from
tb_row_2_col
group by id;
This keeps on executing...never stops..concatenating million rows.
[Updated on: Fri, 09 July 2010 06:08] by Moderator Report message to a moderator
|
|
|
|
Passing value to ref cursor [message #464950 is a reply to message #464767] |
Sun, 11 July 2010 10:49 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Hi,
I have a doubt in the code mentioned below ..indicated in the code by
" -- id shoud be the value fetched from variable v_samp;".
I am not sure of how I get the value into the ref cursor from the variable.
declare
V_Sql Varchar2(32767);
V1 clob;
V_tbl Varchar2(200);
V_samp Varchar2(200);
Cursor C1 Is
Select Table_Name, Rownum R_Num
From User_Tables
Where Table_Name Like 'T%'
Order By Table_Name;
Cursor C2 Is
Select Sample From All_F;
Type Ref1 Is Ref Cursor;
R_1 Ref1;
Type T_Samp Is Table Of Varchar2(100);
Lv_Samp T_Samp;
lv_geno T_Samp;
Begin
For L1 in C1 loop
V_tbl := L1.table_name;
For L2 In C2 Loop
V_samp := L2.sample ;
Open R_1 for 'Select sample_id,type from ' || V_tbl || ' where sample_id = where id = ? '; -- id shoud be the value fetched from variable v_samp;
Loop
Fetch R_1 Bulk Collect
Into Lv_Samp, Lv_Geno Limit 10000;
FOR indx IN 1 .. lv_samp.COUNT Loop
If v_samp = lv_samp(indx) then
V1 := V1 || lv_geno(indx);
end if;
End Loop;
EXIT WHEN Lv_samp.COUNT = 0;
end loop;
end loop;
end loop;
Insert into x values (V_samp || V1);
commit;
end;
Thanks
|
|
|
|
Re: Passing value to ref cursor [message #464953 is a reply to message #464950] |
Sun, 11 July 2010 11:13 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Do not just post a code, explain what it intends to do.
From your previous topic:
Michel Cadot wrote on Fri, 09 July 2010 13:10Please 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
...
And we are waiting for your feedback in your previous topics. did they give a solution? Did you find another solution?
It is fair to feedback to those that have spent time to help you and post the solution you eventually chose.
Regards
Michel
[Updated on: Sun, 11 July 2010 11:58] Report message to a moderator
|
|
|
Re: Passing value to ref cursor [message #464954 is a reply to message #464953] |
Sun, 11 July 2010 11:33 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Sorry guys...My mistake,...
Here is what the code is intended to do...
Find the table name starting with T from user tables
For all samples in cursor 2 ...find the concatenated value of type from the (execute immediate sql query) for the corresponding sample.
-- First cursor to get the table name from user_tables.
-- Second cursor to get the samples.
-- Loop through the second cursor for the corresponding samples from C3 and concatenate the rows for the corresponding sample
-- Can u provide me better approach?
This is the modified code I have :
DECLARE
v_sql VARCHAR2(32767);
v1 CLOB;
v_tbl VARCHAR2(200);
v_samp VARCHAR2(200);
CURSOR c1 IS
SELECT table_name,
ROWNUM r_num
FROM user_tables
WHERE table_name LIKE 'T%'
ORDER BY table_name;
CURSOR c2 IS
SELECT SAMPLE
FROM all_f;
TYPE ref1 IS REF CURSOR;
r_1 REF1;
TYPE t_samp
IS TABLE OF VARCHAR2(100);
lv_samp T_SAMP;
lv_geno T_SAMP;
BEGIN
FOR l1 IN c1 LOOP
v_tbl := l1.table_name;
FOR l2 IN c2 LOOP
v_samp := l2.SAMPLE;
OPEN r_1 FOR 'Select sample_id, type from '
|| v_tbl
|| ' where sample_id = :id ' USING v_samp
||
' and rownum < 10';
LOOP
FETCH r_1 BULK COLLECT INTO lv_samp, lv_geno LIMIT 10000;
FOR indx IN 1 .. lv_samp.COUNT LOOP
v1 := v1
|| Lv_geno(indx);
END LOOP;
EXIT WHEN lv_samp.COUNT = 0;
END LOOP;
END LOOP;
END LOOP;
INSERT INTO x
VALUES (v_samp
|| v1);
COMMIT;
END;
* Formatted by BlackSwan for readability *
[Updated on: Sun, 11 July 2010 11:39] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Passing value to ref cursor [message #464959 is a reply to message #464958] |
Sun, 11 July 2010 12:17 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
For every sample in table all_f if it exists in table t1
then the output should be the concatenated value of type
for the corresponding sample. Let me know if I need to do anything else..There are million types for every sample.
The output should be
'AB35652626-G8' 1 1 1 1 1 1 1 1 1 1 0 0
since only this id exists in c3 (table t1).
This is just a sample...I have huge number of rows to be concatenated for multiple id's.
So that is the reason why I don't want to do any processing in the loop except concatenation.
create table all_f (sample varchar2(20), typeo varchar2(20));
insert into all_f (SAMPLE, typeo)
values ('AB35053903-C10', '2');
insert into all_f (SAMPLE, typeo)
values ('AB35053995-A10', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35054283-C3', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35054582-A7', '2');
insert into all_f (SAMPLE, typeo)
values ('AB35055053-H12', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35055158-B2', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35500856-F4', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35501332-G11', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35501428-B9', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35504972-F11', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35505551-H7', '2');
insert into all_f (SAMPLE, typeo)
values ('AB35506138-G5', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35507097-C11', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35507190-G9', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35561723-H10', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35651275-E6', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35896175-C8', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35897805-A3', '2');
insert into all_f (SAMPLE, typeo)
values ('AB35899249-H8', '1');
insert into all_f (SAMPLE, typeo)
values ('AB35899918-H6', '1');
insert into t1 (SAMPLE, TYPEO)
values ('AB35652626-G8', '1');
commit;
create table t1 (sample_id varchar2(20), type varchar2(20));
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '1');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '0');
insert into t1 (SAMPLE_ID, TYPE)
values ('AB35652626-G8', '0');
F
Thanks
|
|
|
|
Re: Passing value to ref cursor [message #464962 is a reply to message #464961] |
Sun, 11 July 2010 12:26 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Hey,,
I do apologize if the formatting was not good.. I did that using tool. Reg previous topics..I had to change that approach to this one... Please do reply for this requirement. Waiting eagerly for your reply...
Thanks
|
|
|
Re: clob aggregation [message #464963 is a reply to message #464769] |
Sun, 11 July 2010 12:28 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Sorry guys...This clob aggregation works only if you have less
number of rows..(I mean to say lot of time and lot of memory).
Its better if we follow traditional approach
of concatenation.
|
|
|
|
|
|
Re: Passing value to ref cursor [message #464971 is a reply to message #464968] |
Sun, 11 July 2010 13:03 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Now if we add a new row:
SQL> insert into all_f (SAMPLE, TYPEO) values ('AB35652626-G8', '1');
1 row created.
SQL> col types format a50
SQL> select f.sample, wm_concat(t.type) types
2 from all_f f, t1 t
3 where t.sample_id = f.sample
4 group by f.sample
5 /
SAMPLE TYPES
-------------------- --------------------------------------------------
AB35652626-G8 0,0,1,1,1,1,1,1,1,1,1,1
1 row selected.
Regards
Michel
|
|
|
Re: Passing value to ref cursor [message #464972 is a reply to message #464971] |
Sun, 11 July 2010 13:18 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Hey Mike,
I could not use this code...as I have got to concatenate more than billion rows..I tried using this approach. That's the reason why I am using pl/sql procedure to do it. I know its a pretty strange requirement as the concatenated o/p which we get needs to be passed to a tool.
Apologizing beforehand for thi....
Can you modify my query at this stage
" OPEN r_1 FOR 'Select sample_id, type from '
|| v_tbl
|| ' where sample_id = :id ' USING v_samp
||
' and rownum < 10';
LOOP
FETCH r_1 BULK COLLECT INTO lv_samp, lv_geno LIMIT 10000;
"
to pass the values that I get from C2 into R1 and the same into bulk collect.
The concatenated function does not work for clob.
So I created clob_agg user defined function which takes hours and hours but does not complete as there are billion rows (*strange requirement).
So can u modify the code..I did using static way where I am using the parameterized cursors..Attaching it..Just to make it clear..
Thanks
|
|
|
Re: Passing value to ref cursor [message #464973 is a reply to message #464972] |
Sun, 11 July 2010 13:28 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
In the following, after correcting your insert statement, I have fixed just the portion of the code that you have pointed out enough to make the code return your desired results. You need to select the rows in an inner sub-query, then limit them using rownum in an outer query. Also, if you only limit it to 10, then you will miss two of your desired output. I just fixed it enough to make it run without analyzing whether or not the code is efficient. There are various string aggregation techniques. The latest, in 11g, is the Oracle supplied listagg function, which I have demonstrated after your fixed code.
-- data after fixing insert statement:
SCOTT@orcl_11gR2> select * from all_f
2 /
SAMPLE TYPEO
-------------------- --------------------
AB35053903-C10 2
AB35053995-A10 1
AB35054283-C3 1
AB35054582-A7 2
AB35055053-H12 1
AB35055158-B2 1
AB35500856-F4 1
AB35501332-G11 1
AB35501428-B9 1
AB35504972-F11 1
AB35505551-H7 2
AB35506138-G5 1
AB35507097-C11 1
AB35507190-G9 1
AB35561723-H10 1
AB35651275-E6 1
AB35896175-C8 1
AB35897805-A3 2
AB35899249-H8 1
AB35899918-H6 1
AB35652626-G8 1
21 rows selected.
SCOTT@orcl_11gR2> select * from t1
2 /
SAMPLE_ID TYPE
-------------------- --------------------
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 1
AB35652626-G8 0
AB35652626-G8 0
12 rows selected.
-- your code with minimal corrections:
SCOTT@orcl_11gR2> create table x
2 (result clob)
3 /
Table created.
SCOTT@orcl_11gR2> DECLARE
2 v_sql VARCHAR2(32767);
3 v1 CLOB;
4 v_tbl VARCHAR2(200);
5 v_samp VARCHAR2(200);
6 CURSOR c1 IS
7 SELECT table_name,
8 ROWNUM r_num
9 FROM user_tables
10 WHERE table_name LIKE 'T%'
11 ORDER BY table_name;
12 CURSOR c2 IS
13 SELECT SAMPLE
14 FROM all_f;
15 TYPE ref1 IS REF CURSOR;
16 r_1 REF1;
17 TYPE t_samp
18 IS TABLE OF VARCHAR2(100);
19 lv_samp T_SAMP;
20 lv_geno T_SAMP;
21 BEGIN
22 FOR l1 IN c1 LOOP
23 v_tbl := l1.table_name;
24
25 FOR l2 IN c2 LOOP
26 v_samp := l2.SAMPLE;
27
28 -- SELECT THE ROWS IN AN INNER SUBQUERY,
29 -- THEN LIMIT THEM USING ROWNUM IN AN OUTER QUERY:
30 OPEN r_1 FOR
31 'select *
32 from (Select sample_id, type
33 from ' || v_tbl || '
34 where sample_id = :id)
35 where rownum < 13'
36 USING v_samp;
37
38 LOOP
39 FETCH r_1 BULK COLLECT INTO lv_samp, lv_geno LIMIT 10000;
40
41 FOR indx IN 1 .. lv_samp.COUNT LOOP
42 v1 := v1
43 || ' ' || Lv_geno(indx);
44 END LOOP;
45
46 EXIT WHEN lv_samp.COUNT = 0;
47 END LOOP;
48 END LOOP;
49 END LOOP;
50
51 INSERT INTO x
52 VALUES (v_samp
53 || v1);
54
55 COMMIT;
56 END;
57 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select * from x
2 /
RESULT
--------------------------------------------------------------------------------
AB35652626-G8 1 1 1 1 1 1 1 1 1 1 0 0
-- a simpler way in 11g:
SCOTT@orcl_11gR2> truncate table x
2 /
Table truncated.
SCOTT@orcl_11gR2> insert into x
2 select all_f.sample || ' '
3 || listagg (t1.type, ' ')
4 within group (order by t1.type desc)
5 from all_f, t1
6 where all_f.sample = t1.sample_id
7 group by all_f.sample
8 /
1 row created.
SCOTT@orcl_11gR2> select * from x
2 /
RESULT
--------------------------------------------------------------------------------
AB35652626-G8 1 1 1 1 1 1 1 1 1 1 0 0
SCOTT@orcl_11gR2>
|
|
|
Re: Passing value to ref cursor [message #464974 is a reply to message #464973] |
Sun, 11 July 2010 14:05 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Hey Barbara,
I am using 10g. So I cannot use listagg. Eventhough i can i can't because it can handle only 32767 but i have more than million rows. So with my procedure i have written it takes 3 minutes for a row to generate million samples. What can we do in my code to improve performance..
|
|
|
|
|
Re: clob aggregation [message #464978 is a reply to message #464966] |
Sun, 11 July 2010 14:37 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Just loop through all the rows and concatenate...One more thing to remember here is no processing inside the loop like compare/any other condition...The sample code corrected by barabara is in the below post ..
http://www.orafaq.com/forum/t/159628/149291/
--- Sun, 11 July 2010 13:28
LOOP
FETCH r_1 BULK COLLECT INTO lv_samp, lv_geno LIMIT 10000;
FOR indx IN 1 .. lv_samp.COUNT
LOOP
v1 := v1|| ' ' || Lv_geno(indx);
END LOOP;
EXIT WHEN lv_samp.COUNT = 0;
END LOOP;
Here V1 is clob field handling million row concatenated output.
This takes lesser time compared to using user defined clobagg function..(U cannot use parallel hint in user defined function).
Thanks
|
|
|
Re: clob aggregation [message #464979 is a reply to message #464978] |
Sun, 11 July 2010 14:40 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Even though the user defined function does the same thing as we do in the above mentioned traditional approach..i still wonder why
I am unable to get the output in lesser time...
"MAY BE IS IT DUE TO AVOIDING PARALLEL CLAUSE AS USER DEFINED FUNCTION DOES NOT ALLOW YOU TO USE PARALLEL CLAUSE" ///
STILL WONDERING WHATS THE REASON..
|
|
|
Re: clob aggregation [message #464980 is a reply to message #464979] |
Sun, 11 July 2010 14:46 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ Do not post in UPPER case
2/ Once more read the forum guide
3/ Try to do it in parallel
Quote:STILL WONDERING WHATS THE REASON..
And what did you try to answer this question? Did you investigate the points I mentioned?
Regards
Michel
[Updated on: Sun, 11 July 2010 14:48] Report message to a moderator
|
|
|
|
Re: clob aggregation [message #464982 is a reply to message #464981] |
Sun, 11 July 2010 14:50 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
So you did it wrong but as you didn't post your SQL*Plus session as requested in the forum guide we can't answer.
Silly me, you can't know you have to post your session as you refuse to read and follow the forum guide.
Regards
Michel
[Updated on: Sun, 11 July 2010 14:52] Report message to a moderator
|
|
|
Re: clob aggregation [message #464983 is a reply to message #464982] |
Sun, 11 July 2010 15:49 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Looking at your other post, I think you were on the right track with modifying the user-defined aggregate to return a clob and selecting from one table. However, I think you should make the input varchar2 and remove the deterministic, then use code something like what I have demonstrated below.
SCOTT@orcl_11gR2> create or replace
2 type clobagg_type as object(
3 text clob,
4 static function ODCIAggregateInitialize(
5 sctx in out clobagg_type
6 )
7 return number,
8 member function ODCIAggregateIterate(
9 self in out clobagg_type,
10 value in varchar2
11 )
12 return number,
13 member function ODCIAggregateTerminate(
14 self in clobagg_type,
15 returnvalue out clob,
16 flags in number
17 )
18 return number,
19 member function ODCIAggregateMerge(
20 self in out clobagg_type,
21 ctx2 in clobagg_type
22 )
23 return number
24 );
25 /
Type created.
SCOTT@orcl_11gR2> create or replace
2 type body clobagg_type
3 is
4 static function ODCIAggregateInitialize(
5 sctx in out clobagg_type
6 )
7 return number
8 is
9 begin
10 sctx := clobagg_type(null) ;
11 return ODCIConst.Success ;
12 end;
13 member function ODCIAggregateIterate(
14 self in out clobagg_type,
15 value in varchar2
16 )
17 return number
18 is
19 begin
20 self.text := self.text || ' ' || value ;
21 return ODCIConst.Success;
22 end;
23 member function ODCIAggregateTerminate(
24 self in clobagg_type,
25 returnvalue out clob,
26 flags in number
27 )
28 return number
29 is
30 begin
31 returnValue := self.text;
32 return ODCIConst.Success;
33 end;
34 member function ODCIAggregateMerge(
35 self in out clobagg_type ,
36 ctx2 in clobagg_type
37 )
38 return number
39 is
40 begin
41 self.text := self.text || ctx2.text;
42 return ODCIConst.Success;
43 end;
44 end;
45 /
Type body created.
SCOTT@orcl_11gR2> create or replace
2 function clobagg(
3 input varchar2
4 )
5 return clob
6 parallel_enable
7 aggregate using clobagg_type;
8 /
Function created.
SCOTT@orcl_11gR2> DECLARE
2 CURSOR c1 IS
3 SELECT table_name
4 FROM user_tables
5 WHERE table_name LIKE 'T%'
6 ORDER BY table_name;
7 BEGIN
8 FOR l1 IN c1 LOOP
9 EXECUTE IMMEDIATE
10 'insert into x
11 select sample_id || clobagg (t1.type)
12 from ' || l1.table_name || '
13 group by sample_id';
14 END LOOP;
15 COMMIT;
16 END;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select * from x
2 /
RESULT
--------------------------------------------------------------------------------
AB35652626-G8 1 0 0 1 1 1 1 1 1 1 1 1
SCOTT@orcl_11gR2>
[Updated on: Sun, 11 July 2010 15:52] Report message to a moderator
|
|
|
|
|
Re: clob aggregation (merged) [message #608783 is a reply to message #608782] |
Mon, 24 February 2014 17:18 |
|
subrat_pinku
Messages: 5 Registered: February 2014 Location: Paris
|
Junior Member |
|
|
I know that Sawan..
I searched a lot in this forum.. but couldn't get a satisfactory answer..
The string which the SP is creating is of 10 MB around..
And my need is to do that max by 10 mins..
I am processing 100,000 rows of a temp table and concatenating it...
Concatenation of one clob with another too takes time which is a different problem..
Could you please have a look on my SP and help me please..
I mean show do I process such huge records in most efficient ways without using clobagg?
[Updated on: Mon, 24 February 2014 17:23] Report message to a moderator
|
|
|
Re: clob aggregation (merged) [message #608784 is a reply to message #608783] |
Mon, 24 February 2014 17:38 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>Could you please have a look on my SP and help me please..
We don't have your tables.
We don't have your data.
So we can not run, test, debug or improve posted code.
CREATE OR replace PROCEDURE C_ws01_outbound_waveinfo(wave_number IN VARCHAR2,
result_string OUT CLOB)
AS
v_qty_expected alloc_invn_dtl.qty_alloc%TYPE;
v_wave_desc ship_wave_parm.wave_desc%TYPE;
v_spcl_insrt_1 ship_wave_parm.spl_instr_code_1%TYPE;
v_spcl_insrt_2 ship_wave_parm.spl_instr_code_2%TYPE;
prevfacility VARCHAR2(16);
v_errmsg msg_log.msg%TYPE;
v_buffer_string CLOB;
BEGIN
result_string := NULL;
v_buffer_string := NULL;
prevfacility := 'EMPTYSTRING';
--WCS_SORTING_WAVE PART
v_buffer_string := 'wave|'
||wave_number;
--dbms_output.put_line('Flag 1 '||sysdate);
SELECT SUM(order_qty)
INTO v_qty_expected
FROM order_line_item
WHERE wave_nbr = wave_number
AND reference_order_id IS NOT NULL
AND reference_line_item_id IS NOT NULL;
v_buffer_string := v_buffer_string
||'^'
||'quantityExpected|'
||v_qty_expected;
SELECT DISTINCT wave_desc
INTO v_wave_desc
FROM ship_wave_parm
WHERE ship_wave_nbr = wave_number;
SELECT DISTINCT spl_instr_code_1,
spl_instr_code_2
INTO v_spcl_insrt_1, v_spcl_insrt_2
FROM wave_parm
WHERE wave_nbr = wave_number;
v_buffer_string := v_buffer_string
||'^'
||'Description|'
||v_wave_desc;
v_buffer_string := v_buffer_string
||'^'
||'waveType|'
||v_spcl_insrt_2;
v_buffer_string := v_buffer_string
||'^'
||'idPlanTri|'
||v_spcl_insrt_1;
--dbms_output.put_line('Flag 2 '||sysdate);
BEGIN
INSERT INTO c_temp_wm07_wave_data
(temp_order_qty,
temp_item_id,
temp_parent_order_id,
temp_item_name,
temp_d_facility_id,
temp_d_facility_alias_id,
temp_facility_name,
temp_wave_nbr)
SELECT SUM(OLIT.order_qty),
OLIT.item_id,
ORDE.parent_order_id,
ICBO.item_name,
ORDE.d_facility_id,
ORDE.d_facility_alias_id,
FACALIAS.facility_name,
wave_number
FROM orders ORDE,
order_line_item OLIT,
item_cbo ICBO,
facility_alias FACALIAS
WHERE ORDE.order_id = OLIT.order_id
AND OLIT.wave_nbr = wave_number
AND ORDE.is_original_order = '1'
AND OLIT.reference_order_id IS NOT NULL
AND OLIT.reference_line_item_id IS NOT NULL
AND ICBO.item_id = OLIT.item_id
AND ORDE.d_facility_alias_id = FACALIAS.facility_alias_id
GROUP BY OLIT.item_id,
ORDE.parent_order_id,
ICBO.item_name,
ORDE.d_facility_id,
ORDE.d_facility_alias_id,
FACALIAS.facility_name;
EXCEPTION
WHEN no_data_found THEN
result_string := result_string
||'^'
||'';
END;
dbms_output.Put_line('Before of Loop '
||SYSDATE);
BEGIN
SELECT Clobagg('^'
|| 'identifier1|'
|| temp_d_facility_alias_id
|| '^'
|| 'description|'
|| temp_facility_name
|| per_facility_order)
INTO result_string
FROM (SELECT temp_facility_name,
temp_d_facility_alias_id,
Clobagg('^'
||'wmsNum|'
||temp_wave_nbr
||temp_d_facility_id
||temp_item_name
|| '^'
|| 'sortingIdentifier1|'
||temp_item_name
||'^'
|| 'idProduct|'
||temp_item_name
||'^'
||'quantity|'
||temp_order_qty) PER_FACILITY_ORDER
FROM c_temp_wm07_wave_data
WHERE temp_wave_nbr = wave_number
GROUP BY temp_facility_name,
temp_d_facility_alias_id,
temp_item_name);
EXCEPTION
WHEN no_data_found THEN
result_string := result_string
||'^'
||'';
END;
result_string := v_buffer_string
||result_string;
DELETE FROM c_temp_wm07_wave_data;
dbms_output.Put_line('Out of Loop '
||SYSDATE);
EXCEPTION
WHEN OTHERS THEN
-- p_rc := 1;
ROLLBACK;
v_errmsg := 'Step'
|| ' - error during MM16: '
|| dbms_utility.format_error_backtrace;
Raise_application_error(-20010, 'ERAM_MOD Failed '
|| Chr(10)
|| v_errmsg
|| SQLERRM);
END;
/
|
|
|
Re: clob aggregation (merged) [message #608785 is a reply to message #608784] |
Mon, 24 February 2014 17:53 |
|
subrat_pinku
Messages: 5 Registered: February 2014 Location: Paris
|
Junior Member |
|
|
Thanks for your help Sawan..
Let me walk you through the SELECT part..
Functionally just to tell you this table contains stores(temp_d_facility_alias_id, temp_facility_name ) and items(temp_item_name) to supply them.
So I want the list to be something like..
Store1 Item1 5Qty ....
Store1 Item2 5Qty ....
Store2 Item1 5Qty ....
.
.
Now The inside(2nd) clobagg selects all infos as mentioned above and the upper(1st clobagg) aggregates those all and put them into one string.
Loops I felt and tested are most ineffecient..
I used bulk collect in loop still it has performance problems..
Now I use clobagg for fetching these millions of records to be put into one string..
But clobagg also sucks..
Am I clear enough..
[Updated on: Mon, 24 February 2014 18:04] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 07:16:07 CDT 2024
|