Home » SQL & PL/SQL » SQL & PL/SQL » clob aggregation (merged)
clob aggregation (merged) [message #464767] Fri, 09 July 2010 05:42 Go to next message
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

Re: clob aggregation [message #464769 is a reply to message #464767] Fri, 09 July 2010 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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, 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.

There is no faster way.

Do it on less rows and increase it then you will know the performances of your query.
Maybe you should have a look at OS statistics and try to execute the query in parallel if you are not IO bound.

Regards
Michel
Passing value to ref cursor [message #464950 is a reply to message #464767] Sun, 11 July 2010 10:49 Go to previous messageGo to next message
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 #464951 is a reply to message #464950] Sun, 11 July 2010 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Passing value to ref cursor [message #464953 is a reply to message #464950] Sun, 11 July 2010 11:13 Go to previous messageGo to next message
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:10
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, 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 Go to previous messageGo to next message
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 #464955 is a reply to message #464954] Sun, 11 July 2010 11:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Passing value to ref cursor [message #464956 is a reply to message #464955] Sun, 11 July 2010 12:03 Go to previous messageGo to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Here goes ddl and insert statements.
small description at the end..
Let me know if u need anything...
  • Attachment: Untitled3.sql
    (Size: 2.58KB, Downloaded 1246 times)
Re: Passing value to ref cursor [message #464957 is a reply to message #464954] Sun, 11 July 2010 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not read the code for us, we are able to do it, tell us what is the purpose of it: what is the input, what is the ouput from it, from what we can data to transform input to output.

And we are still waiting for your feedback in your other topics.

Regards
Michel
Re: Passing value to ref cursor [message #464958 is a reply to message #464956] Sun, 11 July 2010 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download files, so post them inline and post them formatted.

And we are still waiting for your feedback in your other topics.

Regards
Michel
Re: Passing value to ref cursor [message #464959 is a reply to message #464958] Sun, 11 July 2010 12:17 Go to previous messageGo to next message
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 #464961 is a reply to message #464959] Sun, 11 July 2010 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted.
Still no feedback in previous topics.
Waiting for them... answer follows.

Regards
Michel
Re: Passing value to ref cursor [message #464962 is a reply to message #464961] Sun, 11 July 2010 12:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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: clob aggregation [message #464966 is a reply to message #464963] Sun, 11 July 2010 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the "traditional approach of concatenation"?

Regards
Michel
Re: Passing value to ref cursor [message #464968 is a reply to message #464962] Sun, 11 July 2010 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I did that using tool

But you didn't re'ad the forum guide as requested several times.

The result of your test case is: "no row" as there is no SAMPLE_ID in t1 with a SAMPLE in all_f.

Regards
Michel

[Updated on: Sun, 11 July 2010 13:01]

Report message to a moderator

Re: Passing value to ref cursor [message #464970 is a reply to message #464968] Sun, 11 July 2010 13:02 Go to previous messageGo to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Slightly tensed of...with the code..
After this I will definitely read the guide..


AB35652626-G8 ....this sample is in all_f as well as in t1.. Sad
Re: Passing value to ref cursor [message #464971 is a reply to message #464968] Sun, 11 July 2010 13:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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: Passing value to ref cursor [message #464975 is a reply to message #464974] Sun, 11 July 2010 14:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rajivn786 wrote on Sun, 11 July 2010 12:05

I am using 10g. So I cannot use listagg.



From Posting Guidelines!

Practice


    * Post Operating System (OS) name & version for DB server system.
    * Post results for Oracle RDBMS by invoking following SQL
      SELECT * from v$version
    * By doing so, you'll avoid reactions using features you can't use.
Re: Passing value to ref cursor [message #464976 is a reply to message #464974] Sun, 11 July 2010 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajivn786 wrote on Sun, 11 July 2010 21:05
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..



You still did not feedback to the topic:
http://www.orafaq.com/forum/t/159592/102589/

It is too bad as it is the solution of your problem but you said it is not. As I said you cannot find a better solution, if you think you can then tell us why.

Regards
Michel

Re: clob aggregation [message #464978 is a reply to message #464966] Sun, 11 July 2010 14:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #464981 is a reply to message #464980] Sun, 11 July 2010 14:48 Go to previous messageGo to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Mike,


Did do it initially using parallel. Got an error saying that
u cannot use parallel hint in user defined function.


Thanks
Re: clob aggregation [message #464982 is a reply to message #464981] Sun, 11 July 2010 14:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #608781 is a reply to message #464767] Mon, 24 February 2014 16:39 Go to previous messageGo to next message
subrat_pinku
Messages: 5
Registered: February 2014
Location: Paris
Junior Member
Guys, This is a nice topic..
I am facing the same problem and have performance issues because of bulk records I have to fetch into a string..
I used clobagg as mentioned by some of you above..
I developed a SP which combines some rows together of a temp table..
So now I have multiple rows in in one select..
I have to return but one row to the java code..
So I fire a nested select query..
Something like this::
SELECT clobagg('^' || '^' || 'description|' || TEMP_FACILITY_NAME || PER_FACILITY_ORDER) INTO result_string
FROM(
SELECT TEMP_D_FACILITY_ALIAS_ID,clobagg('sortingIdentifier1|'||TEMP_ITEM_NAME||'^'||'idProduct|'....

I have checked listagg for both but had to use clob agg because of huge size..
For the first clobagg I have 500 records.. and for the second 2000..
So totally I have 500*2000 records.. it can be even more..
I tried using loops here.. But that has performance limitation..
Guys really need you help..
Please help me out..
Re: clob aggregation (merged) [message #608782 is a reply to message #608781] Mon, 24 February 2014 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this Forum.


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Regardless of how hard you try, you will never teach a pig how to fly.
When you are with a design that does not scale, then you should expect performance problems when processing large amounts of data.
Re: clob aggregation (merged) [message #608783 is a reply to message #608782] Mon, 24 February 2014 17:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: clob aggregation (merged) [message #608786 is a reply to message #608785] Mon, 24 February 2014 18:11 Go to previous messageGo to next message
subrat_pinku
Messages: 5
Registered: February 2014
Location: Paris
Junior Member
Its 1:30 here in Paris Sawan.. I am going to sleep.. Hope I will resolve the problem I have tomorrow with your given solution..
Thanks for your responses..
Please do let me know your feedback..

[Updated on: Mon, 24 February 2014 18:12]

Report message to a moderator

Re: clob aggregation (merged) [message #608812 is a reply to message #608786] Tue, 25 February 2014 02:36 Go to previous message
subrat_pinku
Messages: 5
Registered: February 2014
Location: Paris
Junior Member
Team, Could you please suggest on the above request..
Previous Topic: Date Typecast Usage
Next Topic: Putting a WITH statement into PL/SQL
Goto Forum:
  


Current Time: Fri Mar 29 07:16:07 CDT 2024