Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: December 11, 2001 - 7:54 pm UTC

Last updated: December 17, 2012 - 3:45 pm UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

Hi Tom,

Is there anyway that I can just use SQL to do string
concatenation on a VARCHAR2 field much the same way
that a SUM() function is used on a NUMBER field.

For example,

SELECT SUM(revenue), department
FROM sales_table
GROUP BY department;

Here revenue is a NUMBER field.
I'd like to do the same thing when the column is
a VARCHAR2 field. But instead of SUM, I'd like to
do string concatenation (||).

Can this be done without using PL/SQL?

THanks

- peter

and Tom said...

Not in 8i, in 9i yes -- it is very very doable.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:229614022562 <code>
for 8i methods (not aggregates)

in 9i,


scott@ORA9I.WORLD> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 );
25 /

Type created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN string_agg_type,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := ltrim(self.total,',');
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT string_agg_type,
32 ctx2 IN string_agg_type)
33 return number
34 is
35 begin
36 self.total := self.total || ctx2.total;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /

Type body created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /

Function created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> select deptno, stragg(ename)
2 from emp
3 group by deptno
4 /

DEPTNO
----------
STRAGG(ENAME)
-----------------------------------------------------------------------------------------------------------------------------------
10
CLARK,KING,MILLER

20
SMITH,FORD,ADAMS,SCOTT,JONES

30
ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD


would be the approach.

Rating

  (170 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Wowsers ...

Mike Wilson, December 11, 2001 - 11:26 pm UTC

I just spent a few hours wrapping this logic up in a VB function for my db hitting Oracle 8i:
a) 9i seems to have a few hundred things I need. This would have completely simplified my logic (an put it into the database layer, security, speed, etc ...).
b) Oh, yeah, Tom rocks also ...

A reader, December 12, 2001 - 1:46 am UTC


And in case if you want to pivot it back...

Mikito harakiri, December 13, 2001 - 12:47 pm UTC

Given

create table COMMAGGR
(
NUM NUMBER,
AGGR VARCHAR2(2000)
);

insert into COMMAGGR values (1,'1071,12,11103,123404');
insert into COMMAGGR values (2,'15,11116,1,,118');
insert into COMMAGGR values (3,'6');
commit;

we want to parse it back.

1. Using 9i pipelined function to generate a "pseudotable" -- it is much faster than just pulling integers from the table that is "big enough":

CREATE or replace FUNCTION UNSAFE
RETURN IntSet PIPELINED IS
i INTEGER;
BEGIN
i := 0;
loop
PIPE ROW(i);
i:=i+1;
end loop;
END;
/

1a (Optional). Now we can generate a sequence of 1M integers like this:

select rownum from TABLE(UNSAFE) where rownum < 1000000;

Be aware about stop condition: if you remove the predicate, you'll hung up the client process. Note that just adding a parameter to UNSAFE function is inferior to "stop count" predicate -- the code is much more self-documenting in the latter case.

2. The query:

select NUM, substr( -- format
substr( aggr,
instr(aggr, ',', 1, i)+1,
instr(aggr, ',', 1, i+1)
-instr(aggr, ',', 1, i)-1 )
,1,20) -- /format
from ( select rownum i from TABLE(UNSAFE) where rownum < 1000 ),
( select NUM, ','||aggr||',' AGGR from COMMAGGR )
where instr(aggr, ',', 1, i)>0
and instr(aggr, ',', 1, i+1)>0

Alternative solution:

2. Enumerate all integer pairs:

select t-(xplusy+1)*xplusy/2 y,
xplusy-t+(xplusy+1)*xplusy/2 x from (
select FLOOR((SQRT(8*(rownum-1)+1)-1)/2) xplusy,
rownum-1 t
from TABLE(UNSAFE) where rownum <1000
)

3. The query:

select NUM, substr(aggr, beg, len)
from (
select t-(xplusy+1)*xplusy/2+1 beg,
xplusy-t+(xplusy+1)*xplusy/2+1 len from (
select FLOOR((SQRT(8*(rownum-1)+1)-1)/2) xplusy,
rownum-1 t
from TABLE(UNSAFE) where rownum <1000
)
) interval, COMMAGGR
where ( beg=1 or substr(aggr,beg-1,1)=',' )
-- a parsed string
--usually begins with comma
and ( substr(aggr,beg+len,1)=',' -- it
or beg-1+len = length(aggr) ) -- usually ends with comma as well

and instr(substr(aggr, beg, len), ',')=0 -- it has no comma in the middle
;


Also, Tom, how can I easily cut and paste your code without line numbers?




Tom Kyte
December 13, 2001 - 2:49 pm UTC

I cut and paste all of my examples from SQLPlus. It cuts way down on typos. I manually reuse my own code in here by using an editor...


I'll not be changing the format -- its too nice having the code/response/code/response. Just posting the script and then the response is a hassle and not as nice.

I use vi, I cut and paste the examples into vi and then a simple:

:1,$s/.*COM>/xxxx/
:1,$s/.....//

gets rid of all of the line numbers and sqlplus prompts. I just have to delete " created", "s inserted." and so on after that...



missed definition

Mikito harakiri, December 13, 2001 - 12:56 pm UTC

0.

CREATE TYPE IntSet AS TABLE OF Integer;
/


Another Oracle 8i workaround

msc, June 17, 2002 - 6:06 pm UTC

Here is another 8i workaround which is of course not perfect :-) but suitable for certain cases.


1. create package which collects strings into pl/sql table for each department.

create or replace package stragg as
  function result(i in integer) return varchar2;
  function collect(i in integer, s in varchar2) return integer;
end;
/

create or replace package body stragg as
  type TStringTable is table of long index by binary_integer;
  tab TStringTable;
  iNullIndex binary_integer := -1; 
  bCanEmpty boolean := False; 
  
  function result(i in integer) return varchar2 is
    itmp binary_integer := nvl(i,iNullIndex);
  begin
    bCanEmpty := True;
    if tab.exists(itmp) then
      return tab(itmp);
    else
      return NULL;
    end if; 
  end;
  
  function collect(i in integer, s in varchar2) return integer is
    itmp binary_integer := nvl(i,iNullIndex);
    stmp long := substr(s,1,4000);
  begin
    if bCanEmpty then -- after result(..) we can clean up here 
      tab.delete;
      bCanEmpty := False;
    end if;
    if tab.exists(itmp) then
      tab(itmp) := substr( tab(itmp)|| '.' || s, 1, 4000);  
    else        
      tab(itmp) := stmp;
    end if;
    return 1; 
  end;

end;
/

2. use in SQL:

SQL> col names for a50
SQL> select deptno, stragg.result(deptno) as NAMES
  2  from emp
  3  where stragg.collect( deptno, ename) = 1
  4  group by deptno
  5  /

    DEPTNO NAMES
---------- --------------------------------------------------
        10 CLARK.KING.MILLER
        20 SMITH.JONES.SCOTT.ADAMS.FORD
        30 ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES

SQL> 

----
This solutions has one (afaik) pitfall -- one must ensure that stragg.collect is executed.

Following query does *not* work as expected because Oracle optimizes collect() function out:

SQL> select deptno, stragg.result(deptno) as NAMES
  2  from (select deptno, stragg.collect( deptno, ename) as COL from EMP)
  3  group by deptno;

    DEPTNO NAMES
---------- --------------------------------------------------
        10
        20
        30

SQL> 

(btw if this query happens to show right results then they are from *previous* execution)

This subquey can be tricked to work with where clause:

SQL> select deptno, stragg.result(deptno) as NAMES
  2  from (select deptno, stragg.collect( deptno, ename) as COL from EMP)
  3  where COL = 1
  4  group by deptno;

    DEPTNO NAMES
---------- --------------------------------------------------
        10 CLARK.KING.MILLER
        20 SMITH.JONES.SCOTT.ADAMS.FORD
        30 ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES

SQL>  

 

Can you use a dynamic separator with the 9i solution?

Daryl, September 30, 2002 - 8:29 am UTC

Is it possible to use the 9i user aggregate solution with a dynamically specified string to use between the values? In the example, you used comma (","). Is there a way to specify colons (":") or conjunctions ("or ") without using separate types and function for all the different possibilities?

Tom Kyte
October 01, 2002 - 9:00 am UTC

You would have to sort of "prime the pump" on this one.

You could:

dbms_application_info.set_client_info( 'delim=|' )

before running the query and we can use that.  The changes would be:

create or replace type string_agg_type as object
(
   total varchar2(4000),
<b>   delim varchar2(1),</b>
.....



create or replace type body string_agg_type
is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is<b>
    l_delim  varchar2(1) default ',';
begin
    for x in ( select sys_context('userenv','client_info') client_info from dual )
    loop
        if ( x.client_info like 'delim=%' )
        then
            l_delim := substr( x.client_info, 7, 1 );
        end if;
    end loop;
    sctx := string_agg_type( null, l_delim );</b>
    return ODCIConst.Success;
end;

....


and then:

ops$tkyte@ORA9I.WORLD> column enames format a25
ops$tkyte@ORA9I.WORLD> select deptno, stragg(ename) enames
  2    from emp
  3   group by deptno
  4  /

    DEPTNO ENAMES
---------- -------------------------
        10 CLARK|KING|MILLER
        20 SMITH|FORD|ADAMS|SCOTT|JO
           NES

        30 ALLEN|BLAKE|MARTIN|TURNER
           |JAMES|WARD


3 rows selected.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec dbms_application_info.set_client_info( 'delim=|' )

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select deptno, stragg(ename) enames
  2    from emp
  3   group by deptno
  4  /

    DEPTNO ENAMES
---------- -------------------------
        10 CLARK|KING|MILLER
        20 SMITH|FORD|ADAMS|SCOTT|JO
           NES

        30 ALLEN|BLAKE|MARTIN|TURNER
           |JAMES|WARD


3 rows selected.



 

Exactly what I was looking for

John, January 07, 2003 - 11:57 am UTC

Thanks Tom!!  This is exactly what I was looking for.  

I did have one problem.  The type compiled fine but the function did not.    My version is  8.1.7.3.4 on Windows.

SQL> CREATE or replace
  2  FUNCTION stragg(input varchar2 )
  3  RETURN varchar2
  4  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
  5  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION STRAGG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/17     PLS-00103: Encountered the symbol "AGGREGATE" when expecting one
         of the following:
         ; is authid deterministic parallel_enable as



My guess is that it may be a parameter that I do not have set, but I have not been able to fine it anywhere.


 

Tom Kyte
January 08, 2003 - 2:36 pm UTC

Not in 8i, in 9i yes -- it is very very doable.

see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:229614022562 <code>

for 8i methods (not aggregates)

in 9i,

....

in 9i.....

Never Mind

John, January 07, 2003 - 12:47 pm UTC

I read you solution wrong. I don't 9i so I will have to find another way.

Thanks.



Linux vi

Jerry, January 18, 2003 - 7:21 am UTC

"
I use vi, I cut and paste the examples into vi and then a simple:
:1,$s/.*COM>/xxxx/
:1,$s/.....//
"


I suspect that you're a regex purist, but knowing that you use Linux, the Linux vi has the ability to do a columnar block delete.

You just hit CTRL-V, and "paint" the area you want to delete with the cursor.

Now, if I can just get the mouse to work with it, vi would be a GUI editor...:)

Tom Kyte
January 18, 2003 - 9:06 am UTC

well, there is gvim 

try

SQL> define editor="gvim -f"

 

Need an order by statement...

Damian Smith, February 05, 2003 - 9:52 am UTC

This is almost exactly what I want, except that I want to concatenate strings according to a third column called seq_num. The order of concatenation doesn't appear to be predictable.

My source table has deptno, seq_num, ename. I have tried an in-line view with an order by clause but it doesn't seem to work:

scott@ORA9I.WORLD> select deptno, stragg(ename)
2 from (
3 select deptno, seq_num, ename
4 from emp
5 order by deptno, seq_num)
3 group by deptno
4 /

Many thanks,
Damian


Tom Kyte
February 05, 2003 - 12:02 pm UTC

Just write a custom function instead of an aggregate. Aggregates are "SET" things and "sets" really don't have any concept of order.


select deptno, f(deptno) from dept;


where f(deptno) runs your query against EMP with an order by and does what you want....

Using only SQL (9i only though : ( )

Alan Patil, February 21, 2003 - 10:32 am UTC

You can do this with 9i just using SQL without any PLSQL

Query to generate a list of values from a child table (in CSV format) for every primary key value in a parent table.

i.e.

if parent table has

id
1
2
3

and the child table has

id value_id
1 a
1 b
1 c
2 d
3 e
3 f

this query will output

id list of value_ids
1 a,b,c
2 d
3 e,f


The only issue I can think of is what happens if the list is longer than the varchar2 limit of 4000 bytes.

Obviously this only works with 9i due to the SYS_CONNECT_BY_PATH : (.



-- get the longest csv list of values for each id
select
id,
SUBSTR(MAX(list) KEEP (DENSE_RANK FIRST ORDER BY (lev) desc),2) as lov
from
(
-- create a tree and use sys_connect_by_path to create a csv list of values
select id , SYS_CONNECT_BY_PATH(value_id, ',') list, level lev
from
(
-- create a result set with a linked list column for every id so that we can create a hierarchical tree with the next query
select r.id, i.value_id, rownum prow,
LAG(rownum, 1) OVER (PARTITION BY i.id ORDER BY value_id) as connect_id
from parent_table r, child_table i
where r.id = i.id
) r
START WITH connect_id is null
CONNECT BY PRIOR prow=connect_id
)
group by id
order by id





Tom Kyte
February 21, 2003 - 3:12 pm UTC

...
The only issue I can think of is what happens if the list is longer than the
varchar2 limit of 4000 bytes.
.....

use a clob then -- it works, i've done that.

don't understand the spurious reference to sys_connect_by_path??? stragg seems a bit easier?






A straight sql solution

Jack Mulhollan, February 21, 2003 - 5:39 pm UTC

If the max number of values within each group is finite, known, and relatively small, then straight sql will do.  For example, my hypothetical company has a maximum number of five employees per department:

SQL> ---------------------
SQL> -- View sample table.
SQL> ---------------------
SQL> 
SQL> select dept,
  2          emp_name
  3  from temp_01_tb
  4  order by 1,2
  5  ;

   DEPT  EMP_NAME
-------  --------------------
      1  Bob
      1  David
      1  Sally
      1  Sam
      2  Jane
      2  Jennifer
      2  Joe
SQL> 
SQL> -------------------------
SQL> -- Perform string concat.
SQL> -------------------------
SQL> 
SQL> select dept,
  2          max(decode(my_seq,1,emp_name)) || ' ' ||
  3          max(decode(my_seq,2,emp_name)) || ' ' ||
  4          max(decode(my_seq,3,emp_name)) || ' ' ||
  5          max(decode(my_seq,4,emp_name)) || ' ' ||
  6          max(decode(my_seq,5,emp_name)) emp_list
  7  from (
  8        select dept,
  9           emp_name,
 10           row_number() over
 11             (partition by dept order by emp_name) my_seq
 12        from temp_01_tb
 13        )
 14  group by dept
 15  order by 1
 16  ;

   DEPT  EMP_LIST
-------  --------------------------------------------
      1  Bob David Sally Sam
      2  Jane Jennifer Joe

 

Tom Kyte
February 21, 2003 - 6:51 pm UTC

Yes, that works as well -- very similar to the example I have in expert one on one Oracle to demonstrate pivots.

but it is funny -- once you install stragg -- it is "straight sql" as well.

analytic sql solutions are disgusting

Mikito Harakiri, February 21, 2003 - 8:09 pm UTC

db1>with ordemp as (select * from emp order by deptno),
2 seqemp as (select rownum rn, ordemp.* from ordemp),
3 startfinish as (select min(rn) low, max(rn) high, deptno from seqemp ee group by deptno),
4 paths as (
5 select sys_connect_by_path(ename,',') concat, deptno, rn
6 from seqemp o
7 connect by rn = prior rn + 1 and deptno = prior deptno
8 start with rn in (select low from startfinish s where s.deptno = o.deptno)
9 ) select concat, deptno from paths p where rn in (select high from startfinish s where s.deptno = p.deptno);

CONCAT
-------------------------------------------------------------------------------------------------------------------
DEPTNO
----------
,CLARK,KING,MILLER
10

,SMITH,ADAMS,FORD,SCOTT,JONES
20

,ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD
30


db1>

Tom Kyte
February 22, 2003 - 10:12 am UTC

You are amusing sometimes with your analysis and comments you know ;)

Analytic functions ROCK AND ROLL -- coolest thing to happen to SQL since the keyword select. I find AT LEAST 2 or 3 answers a day involve (gasp) analytics.

This will not be the first time I take one of your SQL only solutions and show has dastardly slow, hard to read, unfathomable in the real world they actually are.


I find queries that are

o really fast
o readable
o easy to maintain, develop

to be slightly less digusting then queries that

o are not any of the above ;)


Now, I cannot see how ANY index could be used with your psuedo procedural approach (it is so procedural as to be scary -- looks almost like a SAS program except it is using SELECT instead of proc print; to show the end result).

You sort emp by deptno (all of the rows).
You then assign a rownumber to those ordered rows...
you then find the mininum and maximum rownums by deptno and then procede to query...

So, lets benchmark shall we? Well use all three approaches. Then, you tell us which is "disgusting"



So, I'll start with:

drop table emp;

create table emp
as
select object_id empno, substr(object_name,1,10) ename, object_type job, data_object_id mgr,
created hiredate, object_id sal, object_id comm, mod(rownum,500) deptno
from all_objects
/

Just to generate some data. TKPROF says:

select deptno,
max(decode(my_seq, 1,ename)) || ' ' ||
max(decode(my_seq, 2,ename)) || ' ' ||
max(decode(my_seq, 3,ename)) || ' ' ||
...... 4..67 snipped
max(decode(my_seq,68,ename)) || ' ' ||
max(decode(my_seq,69,ename)) emp_list
from (
select deptno,
ename,
row_number() over
(partition by deptno order by ename) my_seq
from emp
)
group by deptno
order by 1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 35 1.12 1.13 0 237 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37 1.13 1.14 0 237 0 500

Rows Row Source Operation
------- ---------------------------------------------------
500 SORT GROUP BY
32498 VIEW
32498 WINDOW SORT
32498 TABLE ACCESS FULL OBJ#(42644)

so, analytics, while limited in this case as you have to sort of know the MAX number of elements, perform quite outstanding here -- don't they (especially as we move on)


********************************************************************************
select deptno, stragg(ename) from emp group by deptno order by deptno


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 35 4.70 4.93 0 240 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37 4.70 4.94 0 240 0 500

Rows Row Source Operation
------- ---------------------------------------------------
500 SORT GROUP BY
32498 TABLE ACCESS FULL EMP

by far the simpliest solution but a tad more cpu intensive on this set... but wait:


with ordemp as (select * from emp order by deptno),
seqemp as (select rownum rn, ordemp.* from ordemp),
startfinish as (select min(rn) low, max(rn) high, deptno from seqemp ee group by deptno),
paths as (
select sys_connect_by_path(ename,',') concat, deptno, rn
from seqemp o
connect by rn = prior rn + 1 and deptno = prior deptno
start with rn in (select low from startfinish s where s.deptno = o.deptno))
select concat, deptno
from paths p
where rn in (select high from startfinish s where s.deptno = p.deptno)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 6 0
Fetch 35 13.97 15.69 2 259435 1 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37 13.97 15.70 2 259435 7 500

Rows Row Source Operation
------- ---------------------------------------------------
500
500 FILTER
32498 VIEW
32498 CONNECT BY WITHOUT FILTERING
32498 COUNT
32498 VIEW
32498 TABLE ACCESS FULL SYS_TEMP_4254950912
500 VIEW
16124250 TABLE ACCESS FULL SYS_TEMP_4254950913
500 VIEW
16124250 TABLE ACCESS FULL SYS_TEMP_4254950913

that is a query I wouldn't like to see on my system -- slightly too many IO's for my taste and it really chomps up the CPU doesn't it?


So, here is what I would say if you need to do this:


o have a known finite number of elements? row_number() is looking pretty good
o have an unknown or just looking for the easiest query and you find the result sets you are going against perform more then well enought? stragg
o your psuedo procedural approach? I don't think so.....

But -- you are free to keep ignoring the existence of language features that can greatly increase the performance of your system all you want....




Arun Mathur, April 07, 2003 - 10:43 am UTC

Tom,

This is a great site. Would you mind explaining how the PARALLEL_ENABLE option works?

Regards,
Arun

Tom Kyte
April 07, 2003 - 2:15 pm UTC

It permits the user defined aggregate to be called from Parallel query.


</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_59a.htm#2075083 <code>




sys_connect_by_path

A reader, July 30, 2003 - 4:00 pm UTC

Tom Can you please explain the post by Alan Patil where he uses sys_connect_by_path ?

Tom Kyte
July 30, 2003 - 4:02 pm UTC

which part needs explaining? sys_connect_by_path is documented in the 9i doc set if that is it?

analytical sql is not intuitive

mikito harakiri, July 31, 2003 - 8:40 pm UTC

Why

select deptno,
ename,
sum(1) over
(partition by deptno order by deptno) my_seq
from emp

returns 3,3,3,5,5,5,5,5,6,6,6,6,6,6

while

select deptno,
ename,
sum(1) over
(partition by deptno order by empno) my_seq
from emp

returns

1,2,3,1,2,3,4,5,1,2,3,4,5,6

?

Tom Kyte
July 31, 2003 - 9:13 pm UTC

because you added "order by"

the order by makes things "the same". you order by deptno -- well, isn't everything with the same deptno the same? (rhetorical question -- YES)

when you order by empno -- they are NOT the same (empno happens to be unique) so the sum is a pure cumulative total.

using sum(1) is "not intuitive" -- i believe you truly meant to use the correct and proper "count(*)" syntax. here is an example:

scott@ORA920LAP> select deptno, ename,
2 count(*) over (partition by deptno order by deptno) cnt1,
3 count(*) over (partition by deptno order by deptno, ROWID) cnt2,
4 count(*) over (partition by deptno order by empno) cnt3,
5 count(*) over (partition by deptno order by job) cnt4
6 from emp
7 /

DEPTNO ENAME CNT1 CNT2 CNT3 CNT4
---------- ---------- ---------- ---------- ---------- ----------
10 CLARK 3 1 1 2
10 KING 3 2 2 3
10 MILLER 3 3 3 1

see with order by deptno -- cnt1 -- all of the rows are "the same". with order by deptno, rowid -- they are "all different" -- rowid makes them different.

order by empno - same thing since empno just happens to be unique.

order by job, boring in this case -- job was "unique"



20 SMITH 5 1 1 4
20 JONES 5 2 2 5
20 SCOTT 5 3 3 2
20 ADAMS 5 4 4 4
20 FORD 5 5 5 2

oh, now cnt 4, order by job, is interesting. apparently ford and scott have the same job, adams and smith have the same job, and jones is unique. since the jobs were the same -- the rows in the window defined by the order by are the SAME

30 ALLEN 6 1 1 6
30 WARD 6 2 2 6
30 MARTIN 6 3 3 6
30 BLAKE 6 4 4 2
30 TURNER 6 5 5 6
30 JAMES 6 6 6 1

14 rows selected.



sorry you don't find it "intuitive" -- but it is the way it has to work.

FUNCTION stragg in package

fan, August 14, 2003 - 3:38 pm UTC

>>scott@ORA9I.WORLD> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /


Tom, how do I properly put this function in a package ?
Thanks

Tom Kyte
August 14, 2003 - 3:44 pm UTC

don't believe you can, it is just a 'definition', no code really.

are analytical queries powerful?

Mikito Harakiri, August 22, 2003 - 8:22 pm UTC

I'm still evaluating analytical functions:-)

Here is an exersise. Given
table commissions
RN ENAME COMM
---------- ---------- ----------
1 ADAMS 0
2 ALLEN 300
3 BLAKE 0
4 CLARK 0
5 FORD 0
6 JAMES 0
7 JONES 0
8 KING 0
9 MARTIN 1400
10 MILLER 0
11 SCOTT 0
12 SMITH 0
13 TURNER 0
14 WARD 500

The output should be

begin end COMM
---------- ---------- ----------
1 1 0
2 2 300
3 8 0
9 9 1400
10 13 0
14 14 500

Clearly, commission should span continuous ranges only, but how would I specify that as a window condition?

I have no problems with the other SQL constructs: they scale. An subquery can be used at any place where expression is needed, which gives enormous expressiveness. On the other hand, window syntax looks like has been invented to solve one particular problem, and there little opportunity to use it outside this very specific context. You can refute this ramble with just one query, of course:-)

Tom Kyte
August 23, 2003 - 10:20 am UTC

"clearly" "clearly what???? continous ranges of what? I've no idea what your basic question is -- why should the output be that? what is the question you are trying to answer -- and more importantly where is the subquery query you thing is "better"



i give up. Mikito -- do what you will. Like what you like.




I'll write fast queries that perform and scale well. I'll be using analytics out the whazoo to do it. I'll drop down to relatively inefficient subqueries (in general, not always) when I have to.




how bout ODCITABLE ?

ant, November 19, 2003 - 10:13 pm UTC

Tom,
Stragg, using the ODCI, is very cool. I came across the ODCITABLE interface and thought it was interesting.
I noticed that stragg outperforms a "regular" plsql function that does pretty much the same operation
and was wondering if a function using ODCITABLE interface would outperform a pipelined function.
I did a search for ODCITABLEFETCH on metalink and it returned no results. I got all of 4 hits ( all to the oracle doc ) from google. Can a function using ODCITABLE be used to "stream" result sets like pipelined functions ?

The ODCI doc doesn't have a complete example using just pl/sql and the ODCI interface ( the examples use OCI and JAVA ).
Can you provide an example that uses the ODCITABLE interface and pl/sql ( no OCI or JAVA calls ) ?

thanks,
ant


An exception with sys views?

Alex Taylor, November 20, 2003 - 1:13 am UTC

All good, however it doesn't work on my 9.2.0.4 database for the this view:
select ADDRESS,stragg(sql_text)
from v$sqltext
where address = '0000000383E4FCA8'
group by ADDRESS;

The aggregate value includes only the last sql_text value of 6 values. Is there something funny with sys view?

Tom Kyte
November 21, 2003 - 3:37 pm UTC

don't know what you mean by "only the last sql_text value of 6 values"

give us the "example", show the unaggregated data and the aggregated data and point out what is "wrong"

Analytic Functions are VERY powerful - get used to it.

Glen Morris, November 20, 2003 - 8:04 pm UTC

I tried out the 'challenge' from Mikito above and used Analytic Functions. This may not be the most elegant method but since the requirement wasn't stated clearly I'll forgive myself. I've assumed the rown wasn't actually in the source table. If it turns out to be then the query is simpler and just need to replace all occurences of 'row_number() over (order by ename)' with 'rown'.

SQL> create table zz_comm
  2  (ename varchar2(15),comm number);

Table created.

SQL> 
SQL> insert into zz_comm values ('ADAMS',0);

1 row created.

SQL> insert into zz_comm values ('ALLEN',300);

1 row created.

SQL> insert into zz_comm values ('BLAKE',0);

1 row created.

SQL> insert into zz_comm values ('CLARK',0);

1 row created.

SQL> insert into zz_comm values ('FORD',0);

1 row created.

SQL> insert into zz_comm values ('JAMES',0);

1 row created.

SQL> insert into zz_comm values ('JONES',0);

1 row created.

SQL> insert into zz_comm values ('KING',0);

1 row created.

SQL> insert into zz_comm values ('MARTIN',1400);

1 row created.

SQL> insert into zz_comm values ('MILLER',0);

1 row created.

SQL> insert into zz_comm values ('SCOTT',0);

1 row created.

SQL> insert into zz_comm values ('SMITH',0);

1 row created.

SQL> insert into zz_comm values ('TURNER',0);

1 row created.

SQL> insert into zz_comm values ('WARD',500);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select start_range
  2        ,nvl(lead(start_range,1) over (order by rown) -1,start_range) end_range
  3        ,comm
  4  from (select row_number() over (order by ename) rown
  5              ,comm
  6              ,case when comm <> nvl(lag(comm,1) over (order by ename),-1)
  7                         then row_number() over (order by ename) else null end start_range
  8          from zz_comm
  9       )
 10  where start_range is not null;

START_RANGE  END_RANGE       COMM
----------- ---------- ----------
          1          1          0
          2          2        300
          3          8          0
          9          9       1400
         10         13          0
         14         14        500

6 rows selected.

SQL> spool off
 

Awsome STRAGG, how to explicitly order the result?

Antonio, January 09, 2004 - 4:15 pm UTC

Excellent, STRAGG simplified most everything.

Two questions though:

1. How to specify the order of the data returned by

select stragg(text_message) as "long_message" from messages;

long_message
-------------------------------------
message3,messag2,message4,message1

The table MESSAGES has columns ID, text_message, usage.
How can I have the results ordered by the ID column (or other column) to get something like

select stragg(text_message, "ordered by ID or group by ID or where something) from messages

long_message
------------------------------------
message1,message2,message3,message4



2. What is the best resource(s) for me to read to get up to speed on your example to "understand" what the ODCIAggregate* are and to use/apply on my own?


Tom Kyte
January 10, 2004 - 10:03 am UTC

1) you cannot -- that is one of the "issues" with aggregates. they work on unordered sets. Most likely the reason there is no such thing as "stragg" in the "real database" since the results are somewhat indepterminate as far as order goes.

ROW_NUMBER() is deterministic however since you assign the numbers after ordering.

2) the data cartridge developers guide and application developers guide, both available on otn.oracle.com

Handy function for sorting elements in a string

Gary, January 12, 2004 - 1:14 am UTC

In a pinch, the following may be useful. It splits a string by a delimiter, stores the elements in an associative array, then re-extracts them in a sorted order, described in the PL/SQL User's Guide as :

"For associative arrays with VARCHAR2 keys, these methods (NEXT and PRIOR) return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter."

Since Associative arrays are a 9i feature, this cannot be ported to 8i or earlier (unless you implement your own sort code)

create or replace function sort_string
(p_string in varchar2, p_delim in varchar2,
p_dup in varchar2 default 'I') return varchar2 is
type t_sort_tab is table of number(4) index by varchar2(4000);
t_sort t_sort_tab;
v_start number := 1;
v_end number := 0;
v_ext varchar2(4000);
v_out varchar2(4000);
begin
while v_end < length(p_string) loop
v_end := instr(p_string,p_delim,v_start);
if v_end = 0 then
v_end := length(p_string)+1;
end if;
v_ext := substr(p_string,v_start,v_end-v_start);
if t_sort.exists(v_ext) THEN
t_sort(v_ext) := t_sort(v_ext) + 1;
else
t_sort(v_ext) := 1;
end if;
v_start := v_end + length(p_delim);
end loop;
v_ext := t_sort.first;
WHILE v_ext is not null loop
IF p_dup = 'I' THEN
-- Option A - Ignore duplicates (treat as single occurrence)
v_out := v_out||v_ext||p_delim;
ELSIF p_dup = 'N' THEN
-- Option B - Add number of entries after value in brackates
v_end := t_sort(v_ext);
v_out := v_out||v_ext||'('||to_char(v_end)||')'||p_delim;
ELSIF p_dup = 'D' THEN
-- Option C - Repeat duplicated entries
v_end := t_sort(v_ext);
FOR i in 1..v_end loop
v_out := v_out||v_ext||p_delim;
END LOOP;
END IF;
v_ext := t_sort.next(v_ext);
END LOOP;
v_out := substr(v_out,1,instr(v_out,p_delim,-1)-1);
return v_out;
end;


Sorting the results

Noam Tene, January 13, 2004 - 9:12 am UTC

This is exactly what I have been trying to do for a while.

My problem was that (as Tom says) aggregates are by definition unsorted. It gets even worse because with parallel execution the order in which ODCIAggregateMerge happens is unspecified and may even be non-deterministic. I am not sure the results are even reproduceable.

In cases where the results is going to be grouped by the value returned by stragg, it is often desireable to treat
"CLARK,KING,MILLER" and "KING,CLARK,MILLER"
as the same combination and to do that it would be useful of the result were lexicographically sorted.

The sort_string function from Gary was the missing piece.
I wonder if there is some way to create the associative array in ODCIAggregateInitialize and generate the sorted result in ODCIAggregateTerminate.

That would make the solution much more elegant.



using sragg in Crystal report

A reader, February 06, 2004 - 9:04 pm UTC

I use view with stragg function in Crystal report.
And I had to work with this field, but Crystal report prompted: Can't use blob in formula.
I wonder why blob if return value varchar2?

Tom Kyte
February 07, 2004 - 2:26 pm UTC

I supposed you would have to call Crystal Reports and ask them why?

Comment to Glenn's solution

Mikito Harakiri, February 09, 2004 - 7:49 pm UTC

Yes, lag analytic function is critical. Or we can join with c1.rn=c2.rn-1 predicate. Analytical functions are for join-challenged folks!

More significant objection, however, is that analytic functions frequently work together with "group by". And in my example I intuitively expect "group by" accociated with those range buckets. Instead, you filtered out the rows with some predicate.




Tom Kyte
February 09, 2004 - 8:52 pm UTC

Analytical functions are for those who crave, desire or just sometimes NEED speed.



Instant Gratification

Kevin Meade, February 20, 2004 - 2:32 pm UTC

Thanks once more Tom. Had a guy call me minutes ago who wanted "a way in 9i to concatinate strings as an aggregate function, can this be done?".

Natrually being a very experienced IM guy I did what any experienced developer/dba/architect worth his salt would do; I stole from somebody else who is better at it than I am.

Damn if I don't look good. If you could have heard him: "wow, I have never seen anything like this before!, this is amazing!, where do you get this stuff?".

I plan on spending some time with him to make sure he verses himself with the technology before he does anything real with it. Can't have people using code they don't understand.

Did I tell you this is now my favorite site on the internet?

Kevin

10g goes one better...

Adrian Billington, May 11, 2004 - 5:15 am UTC

Tom

10g SQL now has the COLLECT function which, when combined with a table to string function, seems to outperform STRAGG quite significantly. See below for examples.

scott@test10g SQL> CREATE OR REPLACE TYPE ntt_varchar2 AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

scott@test10g SQL> 
scott@test10g SQL> CREATE OR REPLACE FUNCTION to_string (
  2              nt_in        IN ntt_varchar2,
  3              delimiter_in IN VARCHAR2 DEFAULT ','
  4              ) RETURN VARCHAR2 IS
  5  
  6     v_idx PLS_INTEGER;
  7     v_str VARCHAR2(32767);
  8     v_dlm VARCHAR2(10);
  9  
 10  BEGIN
 11  
 12     v_idx := nt_in.FIRST;
 13     WHILE v_idx IS NOT NULL LOOP
 14        v_str := v_str || v_dlm || nt_in(v_idx);
 15        v_dlm := delimiter_in;
 16        v_idx := nt_in.NEXT(v_idx);
 17     END LOOP;
 18  
 19     RETURN v_str;
 20  
 21  END to_string;
 22  /

Function created.

scott@test10g SQL> 
scott@test10g SQL> 
scott@test10g SQL> SELECT deptno
  2  ,      CAST( COLLECT( ename ) AS ntt_varchar2 ) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO EMPS
---------- ---------------------------------------------------------------------------
        10 NTT_VARCHAR2('CLARK', 'KING', 'MILLER')
        20 NTT_VARCHAR2('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 NTT_VARCHAR2('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')

scott@test10g SQL> 
scott@test10g SQL> SELECT deptno
  2  ,      TO_STRING( CAST( COLLECT( ename ) AS ntt_varchar2 ) ) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO EMPS
---------- ---------------------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

scott@test10g SQL> 
scott@test10g SQL> SELECT deptno
  2  ,      STRAGG( ename ) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO EMPS
---------- ---------------------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

scott@test10g SQL> 
scott@test10g SQL> CREATE TABLE t
  2  AS
  3     SELECT MOD( ROWNUM, 100 ) AS id
  4     ,      CAST( 'A' AS VARCHAR2(1) ) AS val
  5     FROM   dba_objects
  6     ,      TABLE( row_generator(4) );

Table created.

scott@test10g SQL> 
scott@test10g SQL> SELECT COUNT(*) FROM t;

  COUNT(*)
----------
    194236

scott@test10g SQL> 
scott@test10g SQL> ANALYZE TABLE t COMPUTE STATISTICS;

Table analyzed.

scott@test10g SQL> set autotrace traceonly
scott@test10g SQL> exec timer.snap();

PL/SQL procedure successfully completed.

scott@test10g SQL>
scott@test10g SQL> SELECT id
  2  ,      TO_STRING( CAST( COLLECT( val ) AS ntt_varchar2 )) AS vals
  3  FROM   t
  4  GROUP  BY
  5         id;

100 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=88 Card=100 Bytes=300)
   1    0   SORT (GROUP BY) (Cost=88 Card=100 Bytes=300)
   2    1     TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=73 Card=194272 Bytes=582816)




Statistics
----------------------------------------------------------
        263  recursive calls
        113  db block gets
        469  consistent gets
          7  physical reads
      21084  redo size
      12975  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed

scott@test10g SQL>
scott@test10g SQL> exec timer.show('COLLECT');
<COLLECT> 0.95 seconds

PL/SQL procedure successfully completed.

scott@test10g SQL> exec timer.snap();

PL/SQL procedure successfully completed.

scott@test10g SQL>
scott@test10g SQL> SELECT id
  2  ,      STRAGG( val ) AS vals
  3  FROM   t
  4  GROUP  BY
  5         id;

100 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=88 Card=100 Bytes=300)
   1    0   SORT (GROUP BY) (Cost=88 Card=100 Bytes=300)
   2    1     TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=73 Card=194272 Bytes=582816)




Statistics
----------------------------------------------------------
          5  recursive calls
          9  db block gets
        402  consistent gets
        387  physical reads
          0  redo size
      13033  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
        100  rows processed

scott@test10g SQL>
scott@test10g SQL> exec timer.show('STRAGG');
<STRAGG> 7.11 seconds

PL/SQL procedure successfully completed.

So that's quite an improvement, given that the COLLECT had to do some cleanout work as well !

Regards
Adrian 

Tom Kyte
May 11, 2004 - 8:46 am UTC

Cool, nice idea there.


anytime we can do something in "more pure sql" the better. what you've effectively done here is reduce the context switches from sql to plsql from 196k down to 100.

I like it.

How to change STRAGG to use Clob

Ajeet, June 11, 2004 - 6:58 am UTC

Hi Tom,

Stragg does not work if I have large amount of data --as earlier you told that in this case I should use CLOB...You have refered to an example at google for this.but I want to use the STRAGG for 9i...How can we change it to use CLOB..I have not worked much on Clobs so want to know from you.

Thanks

Is this ok

Ajeet, June 11, 2004 - 9:59 am UTC

Tom,
I change the Stagg to use clob..it is working too.but want to confirm that it is ok --please take a look..
have done nothing just -- renamed the type names and function as i was tesing and used Clob data type for all out and total varilable.



create or replace type rmd_agg_type as object
(
total clob,
static function
ODCIAggregateInitialize(sctx IN OUT rmd_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT rmd_agg_type ,value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN rmd_agg_type,
returnValue OUT clob,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT rmd_agg_type,
ctx2 IN rmd_agg_type)
return number
);
/

Type created.

create or replace type body rmd_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT rmd_agg_type)
return number
is
begin
sctx := rmd_agg_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT rmd_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN rmd_agg_type,
returnValue OUT clob,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT rmd_agg_type,
ctx2 IN rmd_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/

Type body created.


CREATE or replace
FUNCTION stragg2(input varchar2 )
RETURN clob
PARALLEL_ENABLE AGGREGATE USING rmd_agg_type;
/

Function created.

Thanks

Tom Kyte
June 11, 2004 - 4:22 pm UTC

the caller might need to close the clob after fetching it (it'll be a temporary clob). you'll want to check that out and be careful if so.

STRAGG fails if table has a parallel > 1

Ajeet, June 15, 2004 - 2:43 am UTC

Tom - It seems Stragg function gives an error if table has a parallel value > 1.
It gives me an error -- Like this.

select c.parameter_name,b.aircraft_id,a.fleet,d.engine_id,f.engine_position,
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [QerpxObjMd2], [], [], [], [], [],
[], []
--

I ran this query

select /*+ parallel (rdm_f_parameter ,8) */ c.parameter_name,b.aircraft_id,a.fleet,d.engine_id,f.engine_position,
stragg2(a.parameter_value) ,stragg2(a.flight_datetime)
from
rdm.rdm_f_parameter a,rdm.rdm_d_aircraft b,rdm.rdm_d_engine d,
rdm.rdm_d_flight_phase e,
rdm.rdm_d_eng_position f,
rdm.rdm_d_param c
where b.aircraft_seq_id = a.aircraft_seq_id
and d.engine_seq_id = a.engine_seq_id
and c.param_seq_id = a.param_seq_id
and a.engine_position_seq_id = f.engine_position_seq_id
and a.flight_phase_seq_id = e.flight_Phase_seq_id
and c.parameter_name IN ('DEGT','DEGT_SMOOTHED','DPOIL','DPOIL_SMOOTHED')
and a.fleet = 'AAL'
and b.aircraft_id = 'N3ANAA'
and e.flight_phase = 'CRUISE'
and d.engine_id = '875767'
and a.flight_datetime > to_Date('01-JAN-2003','dd-MON-yyyy')
group by c.parameter_name,b.aircraft_id,a.fleet,d.engine_id,f.engine_position
/

and before that I had

alter table rdm_f_parameter parallel 8 ;
---

then I changed

alter table rdm_f_parameter parallel 8 ;
and remove the paralled hint from above query and then this query worked again.

--
Did not know the reason -- so thought I should post here.
I have not done any further trouble shotting so I could be wrong very much.

Thanks
Ajeet


Tom Kyte
June 15, 2004 - 3:24 pm UTC

please contact support (for all internal errors)

Invokers rights

A reader, August 19, 2004 - 2:55 pm UTC

Should generic "utility" functions like stragg be defined as invokers rights, since they dont really access any data in the database? They just process whatever data the invoker throws at it.

Even from a security perspective, it would make sense to define this function either in a separate "utility" schema just for such stuff or just make it invokers rights so that the caller doesnt get to see any data that he shouldnt see.

So, if I defined the function itself as "authid current_user", am I forced to define the underlying object type as 'authid current_user' as well?

Comments?

Thanks

Tom Kyte
August 19, 2004 - 7:55 pm UTC

since they do no sql, and the invoke nothing that does sql -- it matters not that they are invoker or definers rights.

it would make sense to place it into a utility schema, yes. but not for security -- for maintenance.

I see no benefit from authid current_user here, no sql, no change.

Different delimitd

A reader, August 27, 2004 - 10:42 am UTC

In your response above to having a different delimiter other than ","...

1. I dont see how your change would do it. You just changed
ODCIAggregateInitialize. But ODCIAggregateIterate is the one where each additional "," is tacked on. How would that get the different delimiter?

2. Is there a way to have this defined in the 'stragg' function itself?

i.e.

CREATE or replace FUNCTION stragg(input varchar2,
delimiter varchar2 default ',')
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;

instead of using the client_info thing?

That would be really cool!

Thanks

Tom Kyte
August 27, 2004 - 10:48 am UTC

1) no i didn't, i changed the global variables in the type as well and set the value of one of them in the return statement.

2) not that i'm aware of. it is why i went with the "obscure" approach.

A reader, August 27, 2004 - 11:06 am UTC

Um, still dont get it

all you did was add the delimiter to the type and use that in the Initialize function.

But

12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total || ',' || value;
18 return ODCIConst.Success;
19 end;

still tacks on the ",", not l_delim

so how is it working?

Tom Kyte
August 27, 2004 - 11:11 am UTC

it was inferred you would make that change. sorry, could have made that more clear. I demostrated how to get the global variable set.

Straight SQL?

Kashif, September 09, 2004 - 10:58 am UTC

Hi Tom,

A few posts above you said:

<quote>
but it is funny -- once you install stragg -- it is "straight sql" as well.
</quote>

Not sure what you mean there, are you saying that there's no context switch involved in calling an aggregate function? Thanks.

Kashif

Tom Kyte
September 09, 2004 - 12:15 pm UTC

no, i mean it is "straight sql" from then on in.

it would be like saying "don't use max()"

after installation, how does stragg materially differ from max?

A reader, September 14, 2004 - 8:54 pm UTC

Just curious, do other databases out there have this user-defined aggregates feature?

How unique is the feature set of Oracle 10g or even 9i as compared to its peers?

Thanks

Tom Kyte
September 15, 2004 - 7:27 am UTC

a quick google search seems to suggest that

db2 has a "technique"
postgress has support

Parameterized stragg...

Padders, September 15, 2004 - 8:21 am UTC

You can pass object type in place of scalar parameter to aggregate functions and bypass restriction on passing in multiple values. Kind of worries me why restriction was there in the first place though...

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE TYPE concat_expr AS OBJECT (
  2    str VARCHAR2 (4000),
  3    del VARCHAR2 (4000));
  4  /

Type created.

SQL> CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (
  2    str VARCHAR2 (4000),
  3    del VARCHAR2 (4000),
  4  
  5    STATIC FUNCTION odciaggregateinitialize (
  6      sctx IN OUT concat_all_ot)
  7      RETURN NUMBER,
  8  
  9    MEMBER FUNCTION odciaggregateiterate (
 10      SELF IN OUT concat_all_ot,
 11      ctx IN concat_expr)
 12      RETURN NUMBER,
 13  
 14    MEMBER FUNCTION odciaggregateterminate (
 15      SELF IN concat_all_ot,
 16      returnvalue OUT VARCHAR2,
 17      flags IN NUMBER)
 18      RETURN NUMBER,
 19  
 20    MEMBER FUNCTION odciaggregatemerge (
 21      SELF IN OUT concat_all_ot,
 22      ctx2 concat_all_ot)
 23      RETURN NUMBER);
 24  /

Type created.

SQL> CREATE OR REPLACE TYPE BODY concat_all_ot
  2  AS
  3    STATIC FUNCTION odciaggregateinitialize (
  4      sctx IN OUT concat_all_ot)
  5      RETURN NUMBER
  6    IS
  7    BEGIN
  8      sctx := concat_all_ot (NULL, NULL);
  9      RETURN odciconst.success;
 10    END;
 11  
 12    MEMBER FUNCTION odciaggregateiterate (
 13      SELF IN OUT concat_all_ot,
 14      ctx IN concat_expr)
 15      RETURN NUMBER
 16    IS
 17    BEGIN
 18      IF SELF.str IS NOT NULL THEN
 19        SELF.str := SELF.str || ctx.del;
 20      END IF;
 21      SELF.str := SELF.str || ctx.str;
 22      RETURN odciconst.success;
 23    END;
 24  
 25    MEMBER FUNCTION odciaggregateterminate (
 26      SELF IN concat_all_ot,
 27      returnvalue OUT VARCHAR2,
 28      flags IN NUMBER)
 29      RETURN NUMBER
 30    IS
 31    BEGIN
 32      returnvalue := SELF.str;
 33      RETURN odciconst.success;
 34    END;
 35  
 36    MEMBER FUNCTION odciaggregatemerge (
 37      SELF IN OUT concat_all_ot,
 38      ctx2 IN concat_all_ot)
 39      RETURN NUMBER
 40    IS
 41    BEGIN
 42      IF SELF.str IS NOT NULL THEN
 43        SELF.str := SELF.str || SELF.del;
 44      END IF;
 45      SELF.str := SELF.str || ctx2.str;
 46      RETURN odciconst.success;
 47    END;
 48  END;
 49  /

Type body created.

SQL> CREATE OR REPLACE FUNCTION concat_all (
  2    ctx IN concat_expr)
  3    RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  4    AGGREGATE USING concat_all_ot;
  5  /

Function created.

SQL> SELECT concat_all (concat_expr (deptno, '|')) deptnos,
  2         concat_all (concat_expr (dname, ',')) dnames
  3  FROM   dept;

DEPTNOS       DNAMES
------------- ------------------------------------
10|20|30|40   ACCOUNTING,RESEARCH,SALES,OPERATIONS

SQL>  

Tom Kyte
September 15, 2004 - 9:57 am UTC

nice, sweet - i like that.




using distinct with parameterized stragg

Oliver, September 22, 2004 - 12:12 pm UTC

If you change the definition of concat_expr you even can concatenate distinct strings.
(Adopted from expert one-on-one)

CREATE OR REPLACE TYPE concat_expr AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000),
MAP MEMBER FUNCTION mapping_function RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY concat_expr AS
MAP MEMBER FUNCTION mapping_function
RETURN VARCHAR2
IS
BEGIN
RETURN str||del;
END mapping_function;
END;
/


A reader, October 19, 2004 - 6:41 am UTC


Creating index DDL

A reader, October 20, 2004 - 3:08 pm UTC

I am trying to play around with this stragg to create DDL for index creation. Something like

select 'create '||
case when uniqueness='UNIQUE' then 'unique' else null end||
' index '||index_name||' on '||table_name||
'('||(select stragg(column_name) over (order by column_position) from user_ind_columns b
where b.index_name=a.index_name and a.table_owner=b.table_owner
and a.table_name=b.table_name)
||')'
from user_indexes a
where table_owner=:1
and table_name=:2

Of course, this gives me "single-row subquery returns more than one row" for the stragg.

How can this be done?

Thanks

Tom Kyte
October 20, 2004 - 5:06 pm UTC

o dbms_metadata

o exp rows=n, imp indexfile=foo

o CONFIGURATION MANAGEMENT (you know, the fine art of actually having the ddl for your system)


all of those are inifintely better (especially the last one!) than do it yourself ddl extraction. DIY DDL extraction is so 1994, this is 2004 - there are much bigger fish to fry.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1464804639878#16619648888465 <code>

read that entire thread, but that particular piece shows how to use max(decode... if you continue down this "bad idea" path.

A reader, October 20, 2004 - 3:45 pm UTC

I came up with the following

select ddl from (
select
'create '||decode(uniqueness,'UNIQUE',uniqueness)||' index '||b.index_name||' on '||
'('||stragg(column_name) over (partition by b.index_name order by column_position)||')' ddl,
row_number() over (partition by b.index_name order by column_position desc) rn
from all_indexes a,all_ind_columns b
where a.owner=b.index_owner and a.index_name=b.index_name
and a.owner=:1
) where rn=1

Seems to work. Is it sound or was I just lucky?

Thanks

Tom Kyte
October 20, 2004 - 5:11 pm UTC

see above.

dbms_metadata

A reader, October 20, 2004 - 5:20 pm UTC

I would like to use dbms_metadata, but I dont want just the DDL, I want to add a leading column to all the indexes

i.e. if table T has 5 indexes, I want to get get DDL for creating those 5 indexes except that all the 5 indexes will have a additional leading column on them.

How can I do this with dbms_metadata?

Thanks

Tom Kyte
October 20, 2004 - 8:50 pm UTC

seems it would be really easy to write a stored procedure that got the DDL and stuffed the column name in there immediately after the opening ( ?

A reader, October 20, 2004 - 9:00 pm UTC

"seems it would be really easy to write a stored procedure that got the DDL and stuffed the column name in there immediately after the opening ( ? "

Hm, I guess. Also, I need to suppress the "owner." before the index name. You responded on another thread that "no, there is not currently any such filter. you would have to use the xml interface and develop a style sheet to do a custom transformation"

Developing a stylesheet for such a simple requirement seems like overkill? Or is this really easier than it sounds?

Or would you suggest using replace() to blank out the schema name?



Tom Kyte
October 20, 2004 - 9:22 pm UTC

if you go the procedure route -- getting the owner off is easy (the beginning of a create index is pretty predicable).

replace() would work -- if the schema name isn't used elsewhere in the create, replace('"SCHEMA".', '' )

would nuke them all

dbms_metadata

A reader, October 20, 2004 - 9:49 pm UTC

Since dbms_metadata returns a CLOB, I suppose I would need to get this into a PL/SQL LONG variable and then do all the substr(), replace() stuff? If dbms_metadata returns something more than 32767 bytes, I am out of luck, right? [DDL more than 32K is a extreme case, but just curious]

Tom Kyte
October 21, 2004 - 6:40 am UTC

not necessarily, you have instr, substr, etc on a clob. You could move from "clob to clob" using 32k or less pieces.

Excellent

Sasa, October 21, 2004 - 10:20 am UTC

This is really great about parameterized delimiter and someone provide a way to make a distinct csv list using MAP function.
Could you provide example for this as creating type with MAP function member didn't prevent appearing same values in csv list.
Thanks

Tom Kyte
October 21, 2004 - 2:45 pm UTC

stragg does this by default?

A reader, October 21, 2004 - 3:23 pm UTC

stragg doesnt elimate dupes by default? You would have to do
stragg(distinct col), right?

Also, what did the previous reader mean by "If you change the definition of concat_expr you even can concatenate distinct
strings"

He posted some code, but I couldnt figure out how to use it?

Thanks

Tom Kyte
October 22, 2004 - 2:58 pm UTC

give a go -- there are lots of examples on this page -- please, give a "good college try" as they say.

the way to understand is to do sometimes... it is how i've learned most of what I now know..

A reader, October 22, 2004 - 3:53 pm UTC

I did

SQL> SELECT concat_all (concat_expr (rownum,'|')) from all_objects where rownum<=10;

CONCAT_ALL(CONCAT_EXPR(ROWNUM,'|'))
--------------------------------------------------------------------------------
1|2|3|4|5|6|7|8|9|10

SQL> SELECT concat_all (concat_expr (1,'|')) from all_objects where rownum<=10;

CONCAT_ALL(CONCAT_EXPR(1,'|'))
--------------------------------------------------------------------------------
1|1|1|1|1|1|1|1|1|1

SQL> SELECT concat_all (distinct concat_expr (1,'|')) from all_objects where rownum<=10;

CONCAT_ALL(DISTINCTCONCAT_EXPR(1,'|'))
--------------------------------------------------------------------------------
1

I thought his modification would make it such that I dont need the distinct in there i.e. I thought it would make 2 and 3 above behave the same, but it didnt.

SO what exactly did that mapping member function do?

The docs say

MAP MEMBER

This clause lets you specify a member function (MAP method) that returns the relative position of a given instance in the ordering of all instances of the object. A MAP method is called implicitly and induces an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.

If the argument to the MAP method is null, the MAP method returns null and the method is not invoked.

An object specification can contain only one MAP method, which must be a function. The result type must be a predefined SQL scalar type, and the MAP method can have no arguments other than the implicit SELF argument.

But I really cant make sense of the above!

Thanks 

Tom Kyte
October 23, 2004 - 9:17 am UTC

the map method returned a string (a varchar2) which was the two elements concatented together -- eg:

1|

in your case. 1| = 1| so distinct would treat them the "same".

A reader, October 23, 2004 - 3:01 pm UTC

I still dont get it. So in what case would that map method really eliminate dupes?

Tom Kyte
October 23, 2004 - 4:49 pm UTC

the MAP doesn't eliminate anything.

the MAP permits "distinct" to "distinct", map returns a scalar (string, number, date) represenation of the complex object. DISTINCT removed the duplicates (based on their MAPPED value)

A reader, October 24, 2004 - 12:04 pm UTC

Hm, so then we are back where we started. Like I said earlier, just passing distinct to your original stragg works fine.

select stragg(1) from all_objects where rownum<=5;
will give
1,1,1,1,1

select stragg(distinct 1) from all_objects where rownum<=5;
will give 1

so what exactly is the 'map function' doing for us?

So what e



Tom Kyte
October 24, 2004 - 1:23 pm UTC

letting you pass in the delimiter quite simply.

to pass in the delimiter, you use a user defined type.
to distinct a user defined time, you need a map.

A reader, October 24, 2004 - 1:43 pm UTC

"letting you pass in the delimiter quite simply.
to pass in the delimiter, you use a user defined type"

Well, the userdefined scalar type let you pass the delimiter, but that was the original 'concat_expr/concat_all' posted by 'Padders fro UK'.

"to distinct a user defined time, you need a map"

To distinct, all one needs to do is
select concat_all(distinct concat_expr(str,delim)) from ...
This part is no different than using distinct with your original stragg.

Again, so why do we need the map function posted by 'oliver from germany'?

sorry for belaboring this, but I dont see why the map member function is required.

The map member function is not what lets you pass in the delimiter, the user-defined type does that.

The map member function is not what does the distinct, the DISTINCT operator does that.

So what does the 'map member function' do exactly?

Tom Kyte
October 24, 2004 - 2:29 pm UTC

ops$tkyte@ORA9IR2> SELECT concat_all ( DISTINCT concat_expr (deptno, '|')) deptnos,
  2         concat_all ( DISTINCT concat_expr (dname, ',')) dnames
  3  FROM   scott.dept;
       concat_all ( DISTINCT concat_expr (dname, ',')) dnames
                             *
ERROR at line 2:
ORA-22950: cannot ORDER objects without MAP or ORDER method


<b>to distinct these things, one needs a MAP or order method.</b>


That is what this <b>MAP method is doing...</b>  As stated:

"to distinct a user defined time, you need a map"

it cannot distinct these user defined types without it.

The DISTINCT cannot function without the MAP (or order) 

odciaggregatemerge

A reader, November 24, 2004 - 9:21 am UTC

I thought the odciaggregatemerge function was only required if I create the function as parallel_enable?

I created a type without the odciaggregatemerge function and a function without specifying the parallel_enable and when I ran it, it complained about the type not having odciaggregatemerge.

Isnt odciaggregatemerge required only when multiple parallel threads work on the aggregate and need to "sync" their work once in a while during the process? Stricly speaking, only the initialize, iterate and terminated are required?

Thanks

Tom Kyte
November 24, 2004 - 9:34 am UTC

seems you have proven your own premise "incorrect" no?

see the data cartridge developers guide on otn.oracle.com, it describes this entire process in great detail

distinct keyword within aggregate function parameter

Mikito harakiri, November 30, 2004 - 6:27 pm UTC

What possible benefit the keyword "distinct" allowed within aggregate function parameter gives besides creating more confusion? Just do distinct in the inner query, and apply user-defined aggregate on top of it.

Tom Kyte
November 30, 2004 - 8:35 pm UTC

Let's see:


you hate aggregates and the syntax (either that or you refuse to read the docs and actually understand how they are designed. We are back to looking at the screen and just thinking at it and having it come up with 42 for us all by itself)


you hate analytics

geez..... Ok, I want to know the number of distinct enames and the count of records.

Let's see:

select count(distinct ename), count(*) from emp;


yeah, that is much less clear than:

select count(*), sum(cnt)
from (select ename, count(*) cnt from emp group by emp);


I almost didn't understand that first query but the second -- a work of art, so much more understandable.


NOT

Let's see your totally intuitive approach to this please:

scott@ORA9IR2> select count(distinct job), count(distinct ename), count(distinct mgr) from emp;

COUNT(DISTINCTJOB) COUNT(DISTINCTENAME) COUNT(DISTINCTMGR)
------------------ -------------------- ------------------
5 14 6



I want it in one row, just like that. Please, share with us the intuitive answer you have without distinct inside the aggregate.




good example

mikito, November 30, 2004 - 9:55 pm UTC

I'm really surprised that among the zillion way of writing this query there seems nothing compared to straightforward

select count(distinct sal), count(*) from emp

which is just one FTS and SORT on top of it. How does it happen that ad-hock solution (distinct keyword inside an aggr function) work better?


Tom Kyte
December 01, 2004 - 8:03 am UTC

what do you mean by "How does it happen that ad-hock
solution (distinct keyword inside an aggr function) work better?"


what is ad-hoc about using SQL as documented?

why count distinct is a hack

mikito, December 01, 2004 - 6:39 pm UTC

Because aggregate-group-by is an extended projection operator. You project away all the columns that you no longer need and add aggregate expression instead. For example, in

select deptno, sum(sal) from emp
group by deptno

you eleiminate all the columns except deptno (projection) but add column expression sum(sal) instead. Count distinct doesn't fit into this idea.

Tom Kyte
December 01, 2004 - 8:02 pm UTC

so, please (begging here), show me a more intuitive, clear, performant way to do it.

you know, i read the documentation.
I understand what it says.
I use the tools.

I don't make the standards up. I don't even really care frankly. I myself want to know what I can and cannot do and how to do it and I do it. Period.

You can make up all kinds of fancy terms -- whatever..

count(distinct ... ) sure is nifty when you need it ain't it. SQL would be worse off without it.

tell you what -- ignore its existence (i won't but you can). I'll have faster code, with more answers -- and the "purity" thing doesn't bother me. I know what it does. I understand what it does. I respect what it does. Thats all folks.


Clearing the Temporaty CLOB

A reader from Australia, December 02, 2004 - 2:12 am UTC

I found a use for Ajeet from Bombay.india code (June 11,2004) using a clob and noted your response :
<quote>
the caller might need to close the clob after fetching it (it'll be a temporary 
clob).  you'll want to check that out and be careful if so. 
<quote>

When I run the code and check v$temporary_lobs I see a large number of cache_lobs.  (which grow with each subsequent execution of the code !).

For example :
SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS
---------- ---------- ------------
        92       1224            0

but I have had no luck clearing them programically.  (They go when I exit the session).

I would like to clean up after I am finished with them.  Can you please show me how to do this in the context of Ajeet's code as it uses the clob.   Many Thanks !  

Tom Kyte
December 02, 2004 - 7:40 am UTC

what is your client code written in. dbms_lob has an api to close/free open temporary clobs -- freetemporary.

Clearing the Temporaty CLOB

A reader, December 02, 2004 - 6:35 pm UTC

Yes, I studied the references to dbms_lob.
I basically want to output the data from stragg2 in a query that forms a ref cursor in a PL/sql package in 9iR2. I would appreciate some guidance and an example to be sure.

Tom Kyte
December 02, 2004 - 7:28 pm UTC

call dbms_lob.freetemporary in your code.

verify as you are now that they are freed (you have verfied now that they are not freed, make the change and check again)

Carefull w

Eugen, December 07, 2004 - 10:28 am UTC

If you're using PL/SQL you might experience, what is described in the Note 228479.1 (TEMPORARY LOBS are not freed up automatically after PL/SQL block execution).

Fix (as stated in the Note):
>>To workaround the problem, disconnect the database user session.
>>This behaviour does not reproduce in Oracle10, but is not a bug.

HtH

Dynamic concatenation string

A reader, December 07, 2004 - 9:23 pm UTC

Thanks Tom and Eugen.

I do not think this qualifies as a new question and Apoligise if I am mistaken.

I need to use CLOBs because of the amount of data being returned. I would also like to use a soft parameter as sometimes we want to use ',' and sometimes chr(10).

I note Padders from UK did this (15 Sep 2004) but with varchar2. Everything I have tried returns ORA-00932 inconsistent datatypes. I'd appreciate some assistance with this please.

Tom Kyte
December 08, 2004 - 10:14 am UTC

you need to bind a clob to a clob in general. sounds like you are binding a string to a clob.

in old releases -- you really had to do clob to clob, there was no implicit conversion as there is in current releases.

but you really don't give an example of how you were using it.

Dynamic concatenation string

A reader, December 09, 2004 - 2:11 am UTC

I had another look at my code and found the proverbial typo.

I am coding against 9iR1.

This works but I would appreciate any feedback.  Again it follows Padders from UK (15 Sep 2004) example.  The trick is to output a CLOB rather than VARCHAR2 in concat_all_ot
and the Member Function ODCIAggregateTerminate.  The Concat_all Function must also return a CLOB.  But of course you-all knew that already !

So here is a Clobberised version of Padders Code :

SQL> CREATE OR REPLACE TYPE concat_expr AS OBJECT (
  2      str VARCHAR2 (4000),
  3      del VARCHAR2 (4000));
  4  /

Type created.

Elapsed: 00:00:00.00
SQL> 
SQL> CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (
  2      str CLOB,
  3      del VARCHAR2 (4000),
  4    
  5      STATIC FUNCTION odciaggregateinitialize (
  6        sctx IN OUT concat_all_ot)
  7        RETURN NUMBER,
  8    
  9      MEMBER FUNCTION odciaggregateiterate (
 10        SELF IN OUT concat_all_ot,
 11        ctx IN concat_expr)
 12        RETURN NUMBER,
 13    
 14      MEMBER FUNCTION odciaggregateterminate (
 15        SELF IN concat_all_ot,
 16        returnvalue OUT CLOB,
 17        flags IN NUMBER)
 18        RETURN NUMBER,
 19    
 20      MEMBER FUNCTION odciaggregatemerge (
 21        SELF IN OUT concat_all_ot,
 22        ctx2 concat_all_ot)
 23        RETURN NUMBER);
 24  /

Type created.

Elapsed: 00:00:00.00
SQL> 
SQL> CREATE OR REPLACE TYPE BODY concat_all_ot
  2    AS
  3      STATIC FUNCTION odciaggregateinitialize (
  4        sctx IN OUT concat_all_ot)
  5        RETURN NUMBER
  6      IS
  7      BEGIN
  8        sctx := concat_all_ot (NULL, NULL);
  9        RETURN odciconst.success;
 10      END;
 11    
 12      MEMBER FUNCTION odciaggregateiterate (
 13        SELF IN OUT concat_all_ot,
 14        ctx IN concat_expr)
 15        RETURN NUMBER
 16      IS
 17      BEGIN
 18        IF SELF.str IS NOT NULL THEN
 19          SELF.str := SELF.str || ctx.del;
 20        END IF;
 21        SELF.str := SELF.str || ctx.str;
 22        RETURN odciconst.success;
 23      END;
 24    
 25      MEMBER FUNCTION odciaggregateterminate (
 26        SELF IN concat_all_ot,
 27        returnvalue OUT CLOB,
 28        flags IN NUMBER)
 29        RETURN NUMBER
 30      IS
 31      BEGIN
 32        returnvalue := SELF.str;
 33        RETURN odciconst.success;
 34      END;
 35    
 36      MEMBER FUNCTION odciaggregatemerge (
 37        SELF IN OUT concat_all_ot,
 38        ctx2 IN concat_all_ot)
 39        RETURN NUMBER
 40      IS
 41      BEGIN
 42        IF SELF.str IS NOT NULL THEN
 43          SELF.str := SELF.str || SELF.del;
 44        END IF;
 45        SELF.str := SELF.str || ctx2.str;
 46        RETURN odciconst.success;
 47      END;
 48    END;
 49  /

Type body created.

Elapsed: 00:00:00.00
SQL> 
SQL> CREATE OR REPLACE FUNCTION concat_all (
  2      ctx IN concat_expr)
  3      RETURN CLOB DETERMINISTIC PARALLEL_ENABLE
  4      AGGREGATE USING concat_all_ot;
  5  /

Function created.

Elapsed: 00:00:00.00
SQL> 

 

Thank you

Lam, December 09, 2004 - 11:00 am UTC

the page ist very good!!!

Another approach - Using Different Delimiters

JMattinson, January 05, 2005 - 2:17 pm UTC

Changing the body of the string_agg_type, you could change these two lines:
self.total := self.total || ',' || value;
returnValue := ltrim(self.total,',');

To be:

self.total := SUBSTR(self.total || value, 1, 4000); -- Fix the error that occurs when too many values appended.
returnValue := self.total;

Now you can delimit by anything:
=================================

select deptno, LTRIM(stragg('|' || ename), '|') enames
from emp
group by deptno;


DEPTNO ENAMES
---------- -----------------------------------------
10 CLARK|KING|MILLER
20 SMITH|JONES|SCOTT|FORD|ADAMS
30 ALLEN|MARTIN|JAMES|WARD|TURNER|BLAKE


select deptno, LTRIM(stragg(' + ' || ename), ' + ') enames
from emp
group by deptno;

DEPTNO ENAMES
---------- -----------------------------------------
10 CLARK + KING + MILLER
20 SMITH + JONES + SCOTT + FORD + ADAMS
30 ALLEN + MARTIN + JAMES + WARD + TURNER + BLAKE


Tom Kyte
January 05, 2005 - 7:40 pm UTC

I think that silently truncating data, rathering that raising an error is more along the lines of introducing a bug rather than fixing an error.

How Best to get the Manager Name for an Employee?

Mac, February 17, 2005 - 10:46 am UTC

Using the standard EMP table, what is the best way to get the MGR name for each EMP?

Tom Kyte
February 17, 2005 - 1:43 pm UTC

join

Lookup Function? Join? Something Else?

A reader, February 17, 2005 - 11:05 am UTC

To be more specific, which?

Tom Kyte
February 17, 2005 - 1:43 pm UTC

just join.

Want to make order Ename group by deptno

andrea, May 03, 2005 - 3:59 am UTC

that result is like that
deptno stragg(ename)
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

select deptno, stragg(ename)
from emp
group by deptno;

but that Stragg(ename) is not ordered

even though
SQL like that

select deptno, stragg(ename) from
(select deptno, ename from emp order by deptno, ename)
group by deptno;

i Can't expect ordered name like under result.

deptno stragg(ename)
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

how can i make this??










String Functions

sat, July 19, 2005 - 9:53 am UTC

Is there any function we can use to find the
number of occurances of particular word in a string

Say
STring is ->> David is elder than gary.
Serach word is 'David' , the function should return 1
David is elder than gary, david younger than smith.
Serach word is 'David' , the function should return 2

is there any way to find this ?


Tom Kyte
July 19, 2005 - 10:10 am UTC

ops$tkyte@ORA10G> variable x varchar2(20)
ops$tkyte@ORA10G> exec :x := 'david';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select str,
  2         (length(str)-length(replace(upper(str),upper(:x),'')))/length(:x) cnt
  3    from t;
 
STR                                                            CNT
------------------------------------------------------- ----------
david is older than gary                                         1
david is older than gary, david is younger than smith            2
bob is older than gary, bob is younger than smith                0
 
 

nvl

Gabe, July 19, 2005 - 10:27 am UTC

Need to nvl the length(replace()) call.

Tom Kyte
July 19, 2005 - 10:40 am UTC

true, if the string is just daviddaviddavid ....

nvl( length(), 0 )

One more ..

A reader, July 19, 2005 - 10:51 am UTC

Could you please explain me this logic. I didn't followed
Why did you use division,length and replace



A reader, July 19, 2005 - 10:52 am UTC

Is there any function we can use to find the
number of occurances of particular word in a string

Say
STring is ->> David is elder than gary.
Serach word is 'David' , the function should return 1
David is elder than gary, david younger than smith.
Serach word is 'David' , the function should return 2

is there any way to find this ?



Followup:
ops$tkyte@ORA10G> variable x varchar2(20)
ops$tkyte@ORA10G> exec :x := 'david';

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select str,
2 (length(str)-length(replace(upper(str),upper(:x),'')))/length(:x)
cnt
3 from t;

STR CNT
------------------------------------------------------- ----------
david is older than gary 1
david is older than gary, david is younger than smith 2
bob is older than gary, bob is younger than smith 0




Could you please explain me this logic. I didn't followed
Why did you use division,length and replace

Tom Kyte
July 19, 2005 - 12:00 pm UTC

see above

The question of ordering the values we are concatenating.

Jack Douglas, August 25, 2005 - 10:33 am UTC

Some time ago, in response to a question about how to the order of the data returned by stragg, you said:
  "you cannot -- that is one of the "issues" with aggregates.  they work on unordered sets.  Most likely the reason there is no such thing as "stragg" in the 'real database' since the results are somewhat indepterminate as far as order goes."

If you are prepared to take the additional performance hit, you can build up the array inside the type and calculate and return the ordered concatenation in ODCIAggregateTerminate.

SQL> create or replace type table_of_varchar as table of varchar(4000);
  2  /

Type created.

Elapsed: 00:00:00.00
SQL>
SQL> create or replace type agg_concat_ord as object
  2  (
  3    vals table_of_varchar,
  4    static function ODCIAggregateInitialize (init_context IN OUT agg_concat_ord) return number,
  5    member function ODCIAggregateIterate (self IN OUT agg_concat_ord, this_value IN varchar) return number,
  6    member function ODCIAggregateTerminate (self IN agg_concat_ord, result OUT varchar, flags IN number) return number,
  7    member function ODCIAggregateMerge (self IN OUT agg_concat_ord, merge_context IN agg_concat_ord) return number
  8  );
  9  /

Type created.

Elapsed: 00:00:00.01
SQL>
SQL> create or replace type body agg_concat_ord is
  2    static function ODCIAggregateInitialize (init_context in out agg_concat_ord) return number is
  3    begin
  4      init_context := agg_concat_ord (null);
  5      init_context.vals := table_of_varchar();
  6      return ODCIConst.Success;
  7    end;
  8    member function ODCIAggregateIterate (self in out agg_concat_ord, this_value in varchar) return number is
  9      begin
 10        self.vals.extend;
 11        self.vals (self.vals.last) := this_value;
 12        return ODCIConst.Success;
 13      end;
 14    member function ODCIAggregateTerminate (self IN agg_concat_ord, result OUT varchar, flags IN number ) return number is
 15      begin
 16        for r in (select column_value from table (self.vals) order by 1) loop
 17          result := result || r.column_value;
 18        end loop;
 19        return ODCIConst.Success;
 20      end;
 21    member function ODCIAggregateMerge (self IN OUT agg_concat_ord, merge_context IN agg_concat_ord) return number is
 22      i integer;
 23      begin
 24        i := merge_context.vals.first;
 25        while i is not null loop
 26          self.vals.extend;
 27          self.vals (self.vals.last) := merge_context.vals (i);
 28          i := merge_context.vals.next (i);
 29        end loop;
 30        return ODCIConst.Success;
 31      end;
 32  end;
 33  /

Type body created.

Elapsed: 00:00:00.00
SQL>
SQL> create or replace function aggregate_concat_ord (input varchar) return varchar parallel_enable aggregate using agg_concat_ord;
  2  /

Function created.

Elapsed: 00:00:00.00
SQL>
SQL> create table temp as select 'KING' as name from dual union select 'CLARK' from dual union select 'MILLER' from dual;

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> select substr (aggregate_concat_ord (',' || name), 2) from temp;


SUBSTR(AGGREGATE_CONCAT_ORD(','||NAME),2)
--------------------------------------------------------------------------------------------------------------------------------------
CLARK,KING,MILLER 

Tom Kyte
August 25, 2005 - 2:22 pm UTC

<quote>
If you are prepared to take the additional performance hit, you can build up the
array inside the type and calculate and return the ordered concatenation in
ODCIAggregateTerminate.
</quote>

I've got that somewhere here too ;)

Maybe not on this page, but somewhere - I remember writing it once upon a time ago .

but yes, you are very much correct.

Built in functionality

Bob B, August 25, 2005 - 5:31 pm UTC

Ordered aggregation is also available through analytics, but you have to apply across the entire window and then aggregate/distinct it.

SELECT PART, NUMS
FROM (
SELECT MOD( ROWNUM, 2 ) PART, STRAGG( ROWNUM ) OVER ( PARTITION BY MOD( ROWNUM, 2 ) ORDER BY ROWNUM ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) NUMS
FROM DUAL
CONNECT BY LEVEL <= 100
)
GROUP BY PART, NUMS

Top-N and first rows type queries may suffer on large data sets as this method may require materializing of the entire data set before it returns the first row.

Tom Kyte
August 25, 2005 - 6:52 pm UTC

yup, we've done that too :)

(with the same caveats.... precisely the same caveats)

as always, your help is most useful

jeff, December 05, 2005 - 7:07 pm UTC

Thanks Tom! This has given us the blueprint to help solve a data warehouse denormalization 'sticky wicket'. Not only that, we can apply the solution to a number of different scenarios - can you say "Reusable Objects"?

How to make COLLECT( ename ) run parallel?

Charlie Zhu, December 19, 2005 - 5:35 pm UTC

Please help.

It looks like the COLLECT function prevent PARALLEL SQL exec plan.

alter table scott.emp parallel;

SELECT /*+ parallel(e) */ deptno, COLLECT( ename ) emps
FROM scott.emp e
GROUP BY deptno;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 27 | 2 (50)| 00:00:01 |
| 1 | SORT GROUP BY | | 3 | 27 | 2 (50)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------


Tom Kyte
December 19, 2005 - 5:47 pm UTC

It does look like a limitation of collect - not sure you can change that behaviour without an enhancement request.


we can write a parallel enabled version, would that work for you?

Please! make COLLECT( ename ) run parallel.

Charlie Zhu, December 19, 2005 - 6:21 pm UTC

Thanks, Please.

The FUNCTION stringAgg support PARALLEL SQL by default
PARALLEL_ENABLE AGGREGATE.

But StrAgg even runs parallel, can not beat the COLLECT().

05.73 vs. 4.51 Seconds

(1) StrAgg, noparallel
select listingsid,stringAgg(bsacode) bsacode
from scott.lstrsrch group by listingsid;

86078 rows selected.
Elapsed: 00:00:12.06

(2) collect, noparallel
select listingsid,TO_STRING( CAST( COLLECT( to_char(bsacode) ) AS ntt_varchar2 ) ) AS bsacode
from scott.lstrsrch
group by listingsid;

86078 rows selected.

Elapsed: 00:00:04.51

(3) StrAgg, Parallel
select /*+ parallel(lstrsrch) */ listingsid,stringAgg(bsacode) bsacode from scott.lstrsrch group by listingsid;

86078 rows selected.

Elapsed: 00:00:05.73

Execution Plan
----------------------------------------------------------
Plan hash value: 2469243229

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86078 | 840K| | 22 (14)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 86078 | 840K| | 22 (14)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | SORT GROUP BY | | 86078 | 840K| 5456K| 22 (14)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 190K| 1857K| | 19 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 190K| 1857K| | 19 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 190K| 1857K| | 19 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| LSTRSRCH | 190K| 1857K| | 19 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------


Tom Kyte
December 20, 2005 - 8:24 am UTC

with such a small set, I would not even be considering parallel - likely you are spending more time setting up parallel execution than you are actually running the query.

Parallel is good for things that take minutes, anything running as fast as a few seconds, I would not even *consider* it.

So, unless you have millions of big records - parallel isn't something you want to consider in this case.

90 Millions rows master table Join 5 Child tables

Charlie Zhu, December 20, 2005 - 1:41 pm UTC

Hi Tom,

I guess you will reject my last question,
I have to test it with small portion of real data.

Let me show you the context and background.

M = Millions

We have a books inventory(Listings) table, 91 M (Million) rows, 14GB
and 5 Child table, 1 Fat, 4 Thin

Fat table (ListDocs) got a VARCHAR2(4000) column, avg_row_len is 700 Bytes,110GB table size;
About(99.99%) 1:1 relationship to Master books table.

Thin tables get 50M to 200M rows, avg_row_len is 30 Bytes.
3GB to 7GB table size.

2 of them need to do StringAgg to pivot.
1 of them just need to get Min(Child table PK id).

Here is my SQL,

with e as (select listingsid, Min(VENDCATSID) VENDCATSID from abelisting.Listsvendcats group by listingsid ),
c as (select listingsid,stringAgg( to_char(bsacode) ) AS bsacode
from abelisting.Listsbyrsrchattrs group by listingsid ),
d as (select listingsid,stringAgg( to_char(ABECATEGORIESGROUPID) ) AS ABECATEGORIESGROUPID
from abelisting.Listingsabecats group by listingsid )
SELECT /*+ parallel(c) parallel(d) parallel(e) use_hash */ l.clientid||chr(9)||l.listingsid||chr(9)||
l.picturecount||chr(9)||l.price||chr(9)||l.quantity||chr(9)||l.quantitylimit||chr(9)||
l.rowdf||chr(9)||l.languageisocode3||chr(9)||
l.rowadddt||chr(9)||l.vendorlistingid||chr(9)||
Substr(ld.xmldocument,1,2000)||chr(9)||
c.bsacode||chr(9)||
d.ABECATEGORIESGROUPID||chr(9)||
e.VENDCATSID||chr(9)||
f.XBNIDENTIFIER||chr(9)
FROM abelisting.listings l, abelisting.listdocs ld , c, d, e, abelisting.lstisbn f
WHERE l.listingsid = ld.listingsid
and l.listingsid = c.listingsid (+)
and l.listingsid = d.listingsid (+)
and l.listingsid = e.listingsid (+)
and l.listingsid = f.listingsid (+);

Exec plan:
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89M| 402G| | 4839K (1)| 14:47:18 | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10007 | 89M| 402G| | 4839K (1)| 14:47:18 | Q1,07 | P->S | QC (RAND)
|* 3 | HASH JOIN RIGHT OUTER | | 89M| 402G| 6946M| 4839K (1)| 14:47:18 | Q1,07 | PCWP |
| 4 | VIEW | | 28M| 53G| | 41715 (2)| 00:07:39 | Q1,07 | PCWP |
| 5 | SORT GROUP BY | | 28M| 301M| 1370M| 41715 (2)| 00:07:39 | Q1,07 | PCWP |
| 6 | PX RECEIVE | | 42M| 445M| | 8909 (1)| 00:01:38 | Q1,07 | PCWP |
| 7 | PX SEND HASH | :TQ10001 | 42M| 445M| | 8909 (1)| 00:01:38 | Q1,01 | P->P | HASH
| 8 | PX BLOCK ITERATOR | | 42M| 445M| | 8909 (1)| 00:01:38 | Q1,01 | PCWC |
| 9 | TABLE ACCESS FULL | LISTINGSABECATS | 42M| 445M| | 8909 (1)| 00:01:38 | Q1,01 | PCWP |
|* 10 | HASH JOIN OUTER | | 89M| 234G| 8645M| 2797K (1)| 08:32:50 | Q1,07 | PCWP |
|* 11 | HASH JOIN | | 89M| 66G| 1321M| 1102K (1)| 03:22:05 | Q1,07 | PCWP |
|* 12 | HASH JOIN RIGHT OUTER | | 89M| 9551M| 233M| 215K (1)| 00:39:26 | Q1,07 | PCWP |
| 13 | PX RECEIVE | | 45M| 1348M| | 6945 (1)| 00:01:17 | Q1,07 | PCWP |
| 14 | PX SEND HASH | :TQ10002 | 45M| 1348M| | 6945 (1)| 00:01:17 | Q1,02 | P->P | HASH
| 15 | PX BLOCK ITERATOR | | 45M| 1348M| | 6945 (1)| 00:01:17 | Q1,02 | PCWC |
| 16 | TABLE ACCESS FULL | LSTISBN | 45M| 1348M| | 6945 (1)| 00:01:17 | Q1,02 | PCWP |
|* 17 | HASH JOIN RIGHT OUTER | | 89M| 6907M| 158M| 141K (1)| 00:26:00 | Q1,07 | PCWP |
| 18 | PX RECEIVE | | 34M| 865M| | 43854 (1)| 00:08:03 | Q1,07 | PCWP |
| 19 | PX SEND HASH | :TQ10003 | 34M| 865M| | 43854 (1)| 00:08:03 | Q1,03 | P->P | HASH
| 20 | VIEW | | 34M| 865M| | 43854 (1)| 00:08:03 | Q1,03 | PCWP |
| 21 | SORT GROUP BY | | 34M| 432M| 1624M| 43854 (1)| 00:08:03 | Q1,03 | PCWP |
| 22 | PX RECEIVE | | 35M| 438M| | 4823 (1)| 00:00:54 | Q1,03 | PCWP |
| 23 | PX SEND HASH | :TQ10000 | 35M| 438M| | 4823 (1)| 00:00:54 | Q1,00 | P->P | HASH
| 24 | PX BLOCK ITERATOR | | 35M| 438M| | 4823 (1)| 00:00:54 | Q1,00 | PCWC |
| 25 | TABLE ACCESS FULL| LISTSVENDCATS | 35M| 438M| | 4823 (1)| 00:00:54 | Q1,00 | PCWP |
| 26 | PX RECEIVE | | 89M| 4690M| | 50618 (1)| 00:09:17 | Q1,07 | PCWP |
| 27 | PX SEND HASH | :TQ10004 | 89M| 4690M| | 50618 (1)| 00:09:17 | Q1,04 | P->P | HASH
| 28 | PX BLOCK ITERATOR | | 89M| 4690M| | 50618 (1)| 00:09:17 | Q1,04 | PCWC |
| 29 | TABLE ACCESS FULL | LISTINGS | 89M| 4690M| | 50618 (1)| 00:09:17 | Q1,04 | PCWP |
| 30 | PX RECEIVE | | 89M| 57G| | 414K (1)| 01:15:59 | Q1,07 | PCWP |
| 31 | PX SEND HASH | :TQ10005 | 89M| 57G| | 414K (1)| 01:15:59 | Q1,05 | P->P | HASH
| 32 | PX BLOCK ITERATOR | | 89M| 57G| | 414K (1)| 01:15:59 | Q1,05 | PCWC |
| 33 | TABLE ACCESS FULL | LISTDOCS | 89M| 57G| | 414K (1)| 01:15:59 | Q1,05 | PCWP |
| 34 | VIEW | | 85M| 160G| | 114K (2)| 00:21:02 | Q1,07 | PCWP |
| 35 | SORT GROUP BY | | 85M| 814M| 4320M| 114K (2)| 00:21:02 | Q1,07 | PCWP |
| 36 | PX RECEIVE | | 139M| 1326M| | 22642 (1)| 00:04:10 | Q1,07 | PCWP |
| 37 | PX SEND HASH | :TQ10006 | 139M| 1326M| | 22642 (1)| 00:04:10 | Q1,06 | P->P | HASH
| 38 | PX BLOCK ITERATOR | | 139M| 1326M| | 22642 (1)| 00:04:10 | Q1,06 | PCWC |
| 39 | TABLE ACCESS FULL | LISTSBYRSRCHATTRS | 139M| 1326M| | 22642 (1)| 00:04:10 | Q1,06 | PCWP |
----------------------------------------------------------------------------------------------------------------------------------------

My questions are:
1) WITH, (Subquery) or Temporary table to hold interim results for StringAgg pre process.

You mentioned in your 3rd book(Expert Oracle Database Architecture), Let Oracle SQL handle the big query, no need to use Temp table to hold the interim result. I don't know if it apply to my situation, I did not test the (SubQuery) and temporary table yet.

2) Parallel degree, Temporary tablespace usage and PGA

I set the parallel_max_servers = 8, it used 66GB temp space.
I set the parallel_max_servers = 16, it used 130GB temp space.

Max PGA used: 2.5G, pga_aggregate_target = 8GB.
I want to set "_pga_max_size" and "_smm_max_size" to use more PGA up to 6GB for this SQL.


3) Parallel COLLECT
What's your Parallel COLLECT or others method?

I'm so happy to know that I can write my own Aggregate Function by read this Q&A Sunday night;
I'm more than a Server guy, you're a developer guy.

Thanks a lot


Tom Kyte
December 20, 2005 - 2:10 pm UTC

2) won't happen. we can possibly push the pga workarea to 4gig (maybe) but that's it.



3) we would have to *write it*, it would be variation on the existing stragg function.




The table join order, make small or big table as driven table?

Charlie Zhu, December 20, 2005 - 2:56 pm UTC

Hi Tom,

You answer is too fast and simple this time. :)

About the Table read sequence,
should I put the big table ListDocs to the end and hash join and read it at last ? or is OK read it in the middle?

I guess the Temporary tablespace used effected by this.

I use SubQuery to put it to the last read:
( test it now, it'll take 4 to 10 hours in the test server, 12G memory, but disk I/O band is 1/10 of PROD server disk array)

select --+ ordered
qin.*, Substr(ld.xmldocument,1,2000)
from (
with e as (select listingsid, Min(VENDCATSID) VENDCATSID from scott.lstvendcat group by listingsid ),
...
SELECT ...
FROM scott.lst l, c, d, e, scott.lstisbn f
WHERE l.listingsid = c.listingsid (+)
...)
qin, scott.lstdoc ld
where qin.listingsid = ld.listingsid ;


Tom Kyte
December 20, 2005 - 2:59 pm UTC

well the question (in a review/followup section) was big and long - so it made up for it :)

I read these things *fast* (have to, well over 1,000 a month....)

I'd rather be getting some new questions...


put the tables in any order, it is the job of the CBO to reorder them (in my opinion)

COLLECT( ename ) and read-only physical standby database

Charlie Zhu, December 21, 2005 - 12:47 pm UTC

When I execute SQL,

SELECT deptno, COLLECT( ename ) AS emps
FROM scott.emp
GROUP BY deptno;

I got ERROR at line 1:
ORA-16000: database open for read-only access

stringAgg(ename) and concat_all (concat_expr (ename, ',')) works.

Is it possible to fix it?

Where is your lastest version of StrAgg?

By the way, if you don't have time to go throught the question, just leave it.
I can wait 1 week, 2 weeks, 1 month ...,
it's better than this kind of anwswer, I almost got nothing,
Just a suggestion. ;)

Thanks 10k.


Tom Kyte
December 21, 2005 - 7:52 pm UTC

that is interesting, if you enable sql_trace first - what do you see in the trace file?

COLLECT( ename ) and read-only physical standby database

Charlie Zhu, December 22, 2005 - 1:55 pm UTC

How to upload the trace file?
I only can read the tkprof report, don't know how to read the raw trace file.

Do I need to contact Oracle Support?

Our whole system department are going to see movie this afternoon, so wonderful here!

Happy new year.

Tom Kyte
December 22, 2005 - 5:19 pm UTC

just edit the trace file (if you have expert one on one Oracle - I describe the contents in depth). We'd be looking for the statement that "failed" - search for "err"

The err in trace file

Charlie Zhu, December 23, 2005 - 2:18 pm UTC

=====================
PARSING IN CURSOR #18 len=331 dep=2 uid=0 oct=6 lid=0 tim=1108668952834875 hv=2997034431 ad='dad810d0'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #18:c=30000,e=21941,p=3,cr=57,cu=0,mis=1,r=0,dep=2,og=4,tim=1108668952834867
EXEC #18:c=0,e=8941,p=1,cr=4,cu=2,mis=1,r=0,dep=2,og=4,tim=1108668952844030
ERROR #18:err=16000 tim=447072081
EXEC #17:c=70000,e=143569,p=17,cr=253,cu=3,mis=0,r=0,dep=1,og=4,tim=1108668952844384
ERROR #17:err=604 tim=447072082
STAT #18 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=0 pr=0 pw=0 time=25 us)'
STAT #18 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=55 us)'
=====================
Sorry, I did not read Expert one on one yet.
I'm a little addict to "new" Tech.


Tom Kyte
December 23, 2005 - 2:26 pm UTC

ok, easy to see now - it is recursive sql to create a type to collect into - a "fake" type.

You will solve this by creating a REAL type to be used:

create or replace type ename_type as table of varchar2(30)
/

select deptno, cast( collect(ename) as ename_type ) enames
from emp
group by deptno
/


You'll create the real type in production - wait for dataguard (assuming that is what you are using) to propagate it over to the standby and when you open it read only - that'll not attempt to do any recursive sql to create a new type.

Error message

Charlie Zhu, December 23, 2005 - 2:19 pm UTC

oerr ora 604
00604, 00000, "error occurred at recursive SQL level %s"
// *Cause: An error occurred while processing a recursive SQL statement
// (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.


@abelabs-em64t-32bit /home/oracle/admin/lab/maint>oerr ora

16000
16000, 00000, "database open for read-only access"
// *Cause: The database has been opened for read-only access. Attempts to
// modify the database using inappropriate DML or DDL statements
// generate this error.
// *Action: In order to modify the database, it must first be shut down and
// re-opened for read-write access.


CAST COLLECT( )

Charlie Zhu, December 23, 2005 - 3:50 pm UTC

Hi Tom,

I got same err again.

Sorry to bother you before Christmas,
I can run it in PROD too if not possible in Standby db.

abelisting@vicrpt_vicdb02> SELECT deptno, CAST( COLLECT( ename ) AS ntt_varchar2) AS emps
  2  FROM   scott.emp
  3  GROUP  BY deptno;
SELECT deptno, CAST( COLLECT( ename ) AS ntt_varchar2) AS emps
*
ERROR at line 1:
ORA-16000: database open for read-only access

SQL> desc abelisting.ntt_varchar2
 abelisting.ntt_varchar2 TABLE OF VARCHAR2(4000)

=====================
PARSING IN CURSOR #7 len=68 dep=1 uid=0 oct=77 lid=0 tim=1108760204948184 hv=0 ad='f6409e08'
CREATE TYPE "SYSTPCJ4IWk5JOVzgQAoKKQgB5A==" AS TABLE OF VARCHAR2(10)
END OF STMT
PARSE #7:c=0,e=217,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1108760204948173
=====================
PARSING IN CURSOR #8 len=198 dep=2 uid=0 oct=3 lid=0 tim=1108760204949632 hv=4125641360 ad='db236204'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #8:c=10000,e=593,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1108760204949621
EXEC #8:c=0,e=1543,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1108760204951416
FETCH #8:c=0,e=68,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=4,tim=1108760204951571
EXEC #8:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1108760204952304
FETCH #8:c=0,e=245,p=0,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=1108760204952606
=====================
PARSING IN CURSOR #9 len=331 dep=2 uid=0 oct=6 lid=0 tim=1108760204953429 hv=2997034431 ad='db395e4c'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #9:c=0,e=692,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1108760204953418
EXEC #9:c=10000,e=5665,p=1,cr=4,cu=2,mis=1,r=0,dep=2,og=4,tim=1108760204959336
ERROR #9:err=16000 tim=456415970
EXEC #7:c=20000,e=11387,p=1,cr=15,cu=3,mis=0,r=0,dep=1,og=4,tim=1108760204959742
ERROR #7:err=604 tim=456415970
STAT #9 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  (cr=0 pr=0 pw=0 time=24 us)'
STAT #9 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=54 us)'
=====================

Here is wishing you all a Merry Christmas and a New Year bright with joy and success. 

Tom Kyte
December 23, 2005 - 4:33 pm UTC

guess I should have tested that in a read only database - i was relying on the trace only. It is trying to create the "hidden" type after the database startup.



COLLECT( ) and PGA limit

Charlie Zhu, December 28, 2005 - 2:32 pm UTC

Where I set rownum < 500,000, it's OK.
I enlarged to rownum < 1,000,000, err jump out.

Oracle PGA: 1G

I need to process 100 million rows, it's a thin table.

Here is the SQL and err message:

select listingsid,TO_STRING( CAST( COLLECT( to_char(bsacode) ) AS ntt_varchar2 ) ) AS bsacode
from int_tmp_sa
where rownum < 1000000
group by listingsid;
*
ERROR at line 1:
ORA-29400: data cartridge error
ORA-04030: out of process memory when trying to allocate 1036 bytes (koh dur heap d,kghsseg : kokbCollInitialize)

or

*
ERROR at line 1:
ORA-29400: data cartridge error
ORA-04030: out of process memory when trying to allocate 65548 bytes (koh dur heap d,kokbCollMergebuf1)

Is there any workaround?
For small scall data, COLLECT() is 10 times faster than StrAgg().


Tom Kyte
December 28, 2005 - 6:01 pm UTC

the pga_aggregate_target won't come into play here at all, that is used for sort/hash/bitmap workareas, it cannot control the overall heap

and that is what you ran out of here.

have you monitored the pga memory usage of the process? what is it using

what is to_string?

you can skip the cast and your own type (which is likely adding overhead) if you want.

COLLECT( ), StrAgg( ) and PGA limit

Charlie Zhu, December 30, 2005 - 3:41 pm UTC

The StringAgg is OK, no err for 100M rows table.
After I compiled StringAgg PL/SQL Code for Native Execution,
a little (0.5%) performance improved.

As you mentioned for performance tuning,
the best way to do it, is not to do it at all.

I'll create a staging table and maintain the data in data loading process,
(less than 0.3% daily data changed for the Invertory table and it's child tables)
and direct get the prepared concatenated string data,
this step will save 50% (3 hours) time of the whole daily data extract process.


For COLLECT and PGA,

alter system set pga_aggregate_target=10M;

The PGA increased to 950M, the err pop up. I monitor the PGA by Quest Spot*Light
and
select 'MB_PGA: '||Round(value/1048576) MB_PGA from v$pgastat
where name = 'total PGA inuse';

select 'MB_Temp TBS: '||sum(Round(blocks/256)) MB from V$TEMPSEG_USAGE;


abelisting@lab> select listingsid, COLLECT( to_char(bsacode) ) AS bsacode
from int_tmp_sa
where rownum < 1000000
group by listingsid;
ERROR:
ORA-29400: data cartridge error
ORA-04030: out of process memory when trying to allocate 65548 bytes (koh dur heap d,kokbCollMergebuf1)


alter system set pga_aggregate_target=500M;

Exectue the SQL again, no err this time.

When PGA used increased to 400M, start returning data,
the PGA used keep increasing to 500M, 600M, 700M...
then I cancelled the execution in SQL*Plus.

But the Min() will not ask for more PGA, about 10M it used.

select listingsid, Min( to_char(bsacode) ) AS bsacode
from int_tmp_sa
where rownum < 2000000
group by listingsid;
.

If someday in the future, the COLLECT() can compete with Min() and parallel run,
I'll trun back to COLLECT.

set arraysize 2000
86078 rows processed

3.43 Seconds
SELECT listingsid, COLLECT(c.bsacode)
FROM scott.lstrsrch c group by listingsid;

0.27 Seconds
SELECT listingsid, Min(c.bsacode)
FROM scott.lstrsrch c group by listingsid;

Happy new year! half work today and going home.


A reader, January 03, 2006 - 3:46 pm UTC

hi tom

I want to extract firstname , middlename , last name , suffix from column oldname of a table and insert into another table.


i have written script like
--------------------------------------------------------

declare
ls_old varchar2(36);
ls_new varchar2(39);
ls_middle varchar2(30);
ls_first varchar2(60);
ls_last varchar2(60);
ls_suffix varchar2(99);
ls_instby varchar2(60);
i number (9);

ll_len number(9);
ll_pos1 number (9);
ll_pos2 number (9);
ll_pos3 number (9);
ll_pos4 number (9);
ll_pos5 number (9);
ll_pos number (9);
ld_instdt date;

BEGIN



for cur_indiv in (select individualid, ssn from lwgnindividual
)
loop
for cur_indiv_namchg in (select RECKEY, SSNUM, OLDNAME, NEWNAME, INSERTBY, INSERTDT
from tlog_ChangeIndivName where ssnum = cur_indiv.ssn
order by nvl(to_date(substr(insertdt,1,11),'yyyy/mon/dd'), to_date('19010101','yyyymmdd')) desc
)
loop

-- first run following on source data
--update tlog_changeindivname set oldname=trim(oldname);
--update tlog_changeindivname set oldname=replace(oldname,' ',' ');
--update tlog_changeindivname set oldname=replace(oldname,' ',' ');
--update tlog_changeindivname set oldname=replace(oldname,' ',' ');
--update tlog_changeindivname set oldname=replace(oldname,' ',' ');


select nvl(to_date(substr(cur_indiv_namchg.insertdt,1,11),'yyyy/mon/dd'), to_date('19010101','yyyymmdd'))
into ld_instdt from dual;
--select cur_indiv_namchg.insertby into ls_instby from dual;

select replace(cur_indiv_namchg.oldname,' ',' ') into ls_old from dual;

ls_suffix := null;



if ls_old like '% AIC' then ls_suffix := 'AIC'; end if;
if ls_old like '% CLU' then ls_suffix := 'CLU'; end if;
if ls_old like '% ESQ' then ls_suffix := 'ESQ'; end if;
if ls_old like '% GUY' then ls_suffix := 'GUY'; end if;
if ls_old like '% H' then ls_suffix := 'H'; end if;
if ls_old like '% IV' then ls_suffix := 'IV'; end if;
if ls_old like '% J' then ls_suffix := 'J'; end if;
if ls_old like '% JR' then ls_suffix := 'JR'; end if;
if ls_old like '% JR.' then ls_suffix := 'JR.'; end if;
if ls_old like '% M.' then ls_suffix := 'M.'; end if;
if ls_old like '% RI' then ls_suffix := 'RI'; end if;
if ls_old like '% SR' then ls_suffix := 'SR'; end if;
if ls_old like '% SR.' then ls_suffix := 'SR.'; end if;
if ls_old like '% V' then ls_suffix := 'V'; end if;
if ls_old like '% VII' then ls_suffix := 'VII'; end if;
if ls_old like '% ST' then ls_suffix := 'ST'; end if;
if ls_old like '% ST.' then ls_suffix := 'ST.'; end if;

if length(ls_suffix) > 0 then
ls_old := rtrim(ls_old, ' ' || ls_suffix);
end if;

/* ll_pos1:=instr(ls_old,' ',1,1);
ll_pos2:=instr(ls_old,' ',1,2);
ll_pos3:=instr(ls_old,' ',1,3);
ll_pos4:=instr(ls_old,' ',1,4);
ll_pos5:=instr(ls_old,' ',1,5);



if ll_pos5 > 0 then
ls_first:= substr(ls_old,1,ll_pos1-1);
ls_last:= substr(ls_old,ll_pos2+1,ll_pos4-ll_pos2-1);
ls_middle:= substr(ls_old,ll_pos1+1);
elsif ll_pos4 > 0 then
ls_first:= substr(ls_old,1,ll_pos1-1);
ls_last:= substr(ls_old,ll_pos2+1,ll_pos3-ll_pos2-1);
ls_middle:= substr(ls_old,ll_pos3+1);
elsif ll_pos3 > 0 then
ls_first:= substr(ls_old,1,ll_pos1-1);
ls_last:= substr(ls_old,ll_pos2+1,ll_pos3-ll_pos2-1);
ls_middle:= substr(ls_old,ll_pos3+1);
elsif ll_pos2 > 0 then
ls_first:= substr(ls_old,1,ll_pos1-1);
ls_last:= substr(ls_old,ll_pos1+1,ll_pos2-ll_pos1-1);
ls_middle:= substr(ls_old,ll_pos2+1);
elsif ll_pos1 > 0 then
ls_first:= substr(ls_old,1,ll_pos1-1);
ls_middle:= '';
ls_last:= substr(ls_old,ll_pos1+1);
else
ls_first:= ls_old;
ls_middle:= '';
ls_first:= '';
end if;

*/
ll_len:=length(ls_old);
ll_pos:=instr(ls_old,' ',1,1);
ls_first:=substr(ls_old,1,ll_pos);

ll_pos1:=instr(ls_old,' ',-1,2);
ls_last:=substr(ls_old,ll_pos1,ll_len);

IF INSTR ( LS_LAST,' ',2)=0 THEN
LS_MIDDLE:=SUBSTR(LS_LAST,1,2);
LS_LAST:=LTRIM(LS_middle||LS_last);
end if;

ll_pos2:=(ll_len-ll_pos)-(ll_len-ll_pos1);
ls_middle:=substr(ls_old,ll_pos,ll_pos2);
if length(ls_middle)>2 then
ls_first:=ls_first||ls_middle;
ls_middle:=NULL;
end if;


--dbms_output.put_line(ls_old||'--'||ls_first||'--'||ls_middle||'--'||ls_last);


insert into lwgnindividualhistory_t
(historyid, individualid, ssn, lastname,
firstname, middlename, TITLE, SUFFIX,
DOB, GENDER, USERID, PASSWORD,
FPRCNO, TAXPAYERID, PHONETICCODE, CRDNUMBER,
AFFILIATION, ISCITIZEN, TRADENAME, BUSINESSNAME,
BCARDCOUNT, VETDISABLEDFLAG, FLAG, performeddate,
performedby, indexno, oldnamename)

select sq_lwgnindividualhistory.nextval, individualid, ssn, ls_last,
ls_first, ls_middle, title, ls_suffix,
dob, gender, USERID, PASSWORD,
FPRCNO, TAXPAYERID, PHONETICCODE, CRDNUMBER,
AFFILIATION, ISCITIZEN, TRADENAME, BUSINESSNAME,
BCARDCOUNT, VETDISABLEDFLAG, FLAG, ld_instdt,
ls_instby, indexno,ls_old
from lwgnindividual
where individualid=cur_indiv.individualid
and ssn=cur_indiv_namchg.ssnum ;

end loop;
end loop;
end;

---- select oldnamename ,suffix,firstname,middlename,lastname from lwgnindividualhistory_t;


----------------------------------------------------------

sometimes the result is not coming as expected like middle name if more than 4 space s are there.




Help

A reader, March 27, 2006 - 9:50 pm UTC

Hi Tom,
I want to split a sentence with each word being printed in
each line.
How to give the exit condition with in the
LOOP
...
end loop;
Please do reply.

Tom Kyte
March 28, 2006 - 7:47 am UTC

just type:

exit;


or 

exit when (condition);



but it is unlikely you want to use "exit" when a better way exists:

ops$tkyte@ORA9IR2> declare
  2          l_string varchar2(4000) := 'how   now brown      cow';
  3  begin
  4          while (l_string is NOT null)
  5          loop
  6                  dbms_output.put_line
                     ( substr( l_string, 1, instr(l_string||' ',' ') ) );
  7                  l_string := substr( l_string, instr(l_string||' ',' ')+1 );
  8          end loop;
  9  end;
 10  /
how
now
brown
cow
 
PL/SQL procedure successfully completed.

 

ok

A reader, March 28, 2006 - 7:58 am UTC

Hi Tom,
Excellent and Thanks.
Previously you used a
LOOP
..
END LOOP;

with ' ' appended to the actual string passed.

condition checking was in the loop itself and no while was
there.

That was nice to see and I don't remember where it stays
in this huge log.
Any way thanks for your time.
Bye!

A reader, April 20, 2006 - 1:20 pm UTC


stupid, April 21, 2006 - 1:06 am UTC

-- 1
Insert into test (ID, STATUS, GRP) Values (1, 'B', 'x');
Insert into test (ID, STATUS, GRP) Values (2, 'B', 'x');
Insert into test (ID, STATUS, GRP) Values (3, 'A', 'x');
Insert into test (ID, STATUS, GRP) Values (4, 'A', 'y');
COMMIT;

-- 2
select distinct z.grp,last_value (z.xconcat) over (partition by grp) zconcat
from
(
select x.grp ,sys_connect_by_path(status,'.') xconcat
from
(select id,lag(id,1) over (partition by grp order by grp,id) parent_id, status, grp
from (select grp, status, min(id) id from test group by grp, status) a
) x
connect by prior id=parent_id
start with parent_id is null
) z

-- 3
GRP ZCONCAT
----------------
x .B.A
y .A





A reader

Azeem, May 25, 2006 - 5:45 pm UTC

Hi,

It was the exact procedure what I was looking for.I spent around 3 days to solve this problem but using your solution I could do it in 2 min.

Thanks to you.

Suppressing buffer Errors -- StringAggType

Anindya Mitra, May 29, 2006 - 9:52 am UTC

I used stringagg for one implementation where the user wanted the function not to "break" with string buffer exception when the list is very long, but should some way indicate the list overflow in output.
I modified StringAggType to result in 'List,...' for a longer list that could not be accomodated; and also a space between two list entries.

"CREATE OR REPLACE
type body StringAggType
is

static function ODCIAggregateInitialize(sctx IN OUT nocopy StringAggType)
return number
is
begin
sctx := StringAggType( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT nocopy StringAggType,
value IN varchar2 )
return number
is
begin
if length (self.theString || ', ' || value) <=4000 and substr(nvl(rpad(self.theString,4,'#'),'####'),-4) != ',...' then
self.theString := self.theString || ', ' || value;
return ODCIConst.Success;
elsif substr(self.theString,-4) != ',...' then
self.theString := substr(self.theString,1,3996) || ',...';
return ODCIConst.Success;
else
return ODCIConst.Success;
end if;
end;

member function ODCIAggregateTerminate(self IN StringAggType,
returnValue OUT nocopy varchar2,
flags IN number)
return number
is
begin
returnValue := rtrim( ltrim( self.theString, ', ' ), ', ' );
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT nocopy StringAggType,
ctx2 IN StringAggType)
return number
is
begin
self.theString := self.theString || ', ' || ctx2.theString;
return ODCIConst.Success;
end;

end;
/"

Also the 10g compiler issued a few warnings (suggestions basically) to use "nocopy" which I used in type and type body after that.

1) Tom, is this fine to use nocopy here? Any caveats?
2) My sqlplus did not show any warning. I only got to know when I used Oracle Sql Developer to compile the same.
Is there any simple sqlplus command for warnings just like "show errors'? Also sqlplus does that job automatically after any compilation call. Is there any similar facility available for warnings in sqlplus?

Thanks

Tom Kyte
May 30, 2006 - 8:26 am UTC

1) probably is OK, do you know what it does, what it implies? (those are the caveats, if you answer "no", that is the caveat - you don't understand what the implications are :) - search for nocopy on this site, we've talked about it before.

2) </code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_warn.htm#sthref9481 <code>



nocopy in aggregate function

Anindya, May 31, 2006 - 9:17 am UTC

Thanks for your reply Tom.
I could set the warning on for sqlplus; though sqlplus still represents a warning as error. Also I found the documentation link for DBMS_WARNING gives "DBMS_WARNING.GET_WARNING_SETTING_NUM (
warning_number IN NUMBER)
RETURN warning_value;" ... I did not find the declaration of the return type "warning_value" here.
Regarding "Nocopy" hint - I am not fully sure whether there is any anomaly impact for aggregate functions; which are definitely called from SQLs only and the input parameter is normally table column, not any pl/sql variable.
Assuming I have not done any exception handling within function code that suppresses any raised exception, is there really any risk? Do I corrupt any SQL return value? Only the statement fails I believe.

Tom Kyte
May 31, 2006 - 10:31 am UTC

As long as you don't care that the OUT values of a procedure will be "messed up upon hitting an unhandled exception" you may use it.

You own the code - you make that call. Seems pretty "safe" in this case since we would discard the result set upon hitting some error anyway it would seem.

String Concatenation

Purav Shah, June 22, 2006 - 10:32 am UTC

Can you do the same thing in Microsoft SQL server 2000....

Tom Kyte
June 22, 2006 - 2:37 pm UTC

that would be a good question for askbill@ms.com....

string

mal, June 28, 2006 - 5:50 pm UTC

Tom,

I just need file name without location then How can I do this?
select file_name from dba_data_files;
e,g
I just need daqdata_04.dbf
/export/u02/oradata/A/daqdata_04.dbf

Tom Kyte
June 28, 2006 - 6:40 pm UTC

ops$tkyte@ORA10GR2> select substr( file_name, instr( file_name, '/', -1 )+1 ) from dba_data_files;

SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',-1)+1)
-------------------------------------------------------------------------------
users01.dbf
sysaux01.dbf
undotbs01.dbf
system01.dbf
 

You are the man...

mal, June 28, 2006 - 7:03 pm UTC


For windows

Mahmood Lebbai, June 28, 2006 - 7:50 pm UTC

And for windows it is forward slah(/) to be replaced with backward slash(\)

The query...
select substr( file_name, instr( file_name, '\', -1 )+1 )
from dba_data_files



Nice use of COLLECT but can we prevent hidden type creation?

Aaron Valdes, June 29, 2006 - 5:42 pm UTC

Its seems to create a type for every table when the length gets bigger:


SQL> create or replace type ntt_varchar2 as table of varchar2(4000);
  2  /

Type created.

SQL> 
SQL> CREATE TABLE names (NAME VARCHAR2(40));

Table created.

SQL> CREATE TABLE names2 (NAME VARCHAR2(60));

Table created.

SQL> 
SQL> INSERT INTO names VALUES ('rick');

1 row created.

SQL> INSERT INTO names VALUES ('bob');

1 row created.

SQL> INSERT INTO names VALUES ('tom');

1 row created.

SQL> 
SQL> INSERT INTO names2 VALUES ('rick');

1 row created.

SQL> INSERT INTO names2 VALUES ('bob');

1 row created.

SQL> INSERT INTO names2 VALUES ('tom');

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TYPE';

OBJECT_NAME
--------------------------------------------------------------------------------
NTT_VARCHAR2

1 row selected.

SQL> 
SQL> SELECT CAST( COLLECT (NAME) AS ntt_varchar2  ) 
  2  FROM names;

CAST(COLLECT(NAME)ASNTT_VARCHAR2)
--------------------------------------------------------------------------------
NTT_VARCHAR2('rick', 'bob', 'tom')

SQL> 
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TYPE';

OBJECT_NAME
--------------------------------------------------------------------------------
NTT_VARCHAR2
SYSTPF2Ofw9UPTkbgQCUK7ZV+YQ==

2 rows selected.

SQL> 
SQL> SELECT CAST( COLLECT (NAME) AS ntt_varchar2  ) 
  2  FROM names2;

CAST(COLLECT(NAME)ASNTT_VARCHAR2)
--------------------------------------------------------------------------------
NTT_VARCHAR2('rick', 'bob', 'tom')

SQL> 
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TYPE';

OBJECT_NAME
--------------------------------------------------------------------------------
NTT_VARCHAR2
SYSTPF2Ofw9UPTkbgQCUK7ZV+YQ==
SYSTPF2Ofw9UUTkbgQCUK7ZV+YQ==

3 rows selected.

SQL> 


Kinda annoying since these objects show up in schema compares, etc.

thanks 

number of occurances

A reader, July 12, 2006 - 6:32 pm UTC

Hi Tom

You posted a query to find the number of occurances of a string

select str, (length(str)-length(replace(upper(str),upper(:x),'')))/length(:x)
cnt
from t;

Not sure how this works, may you give a little explanation?



Tom Kyte
July 12, 2006 - 6:42 pm UTC

a = length(str) = length of the original string
b = length(replace(upper(str),upper(:x),'')) = length of string WITHOUT :x in it
c = length(:x) = length of the :x string, the thing you want to count


so....

a-b = how many characters were removed from STR by the replace()

a-b
--- = how many times we removed :x from A
 c



eg:

str = 'hello mellow yellow'
:x = ll


  1  select length(:str),
  2         length(replace(upper(:str),upper(:x),'')),
  3         length(:x)
  4*   from dual
ops$tkyte@ORA10GR2> /

LENGTH(:STR) LENGTH(REPLACE(UPPER(:STR),UPPER(:X),'')) LENGTH(:X)
------------ ----------------------------------------- ----------
          19                                        13          2


So, str-replace() = 19-13 = 6.  We removed six characters by replacing 'll' with nothing.

6/2 = 3, we must have removed 3 occurences of 'll' from :str...



 

allowing only charcters

ram, August 04, 2006 - 3:57 pm UTC

is there a way we can allow only charcters while storing in table for example first name and last name can contain the Alhpa charcters ( no numneric or special charcters)


Thanks for the help

Tom Kyte
August 04, 2006 - 4:19 pm UTC

ops$tkyte%ORA10GR2> create table t
  2  ( x varchar2(30)
  3      constraint alpha_check
  4      check ( replace(
  5                translate(
  6                    upper(x),' ABCDEFGHIJKLMNOPQRSTUVWXYZ',rpad('X',27,'X')),
  7                'X','') is null )
  8  )
  9  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'Tom Kyte' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'tom_kyte' );
insert into t values ( 'tom_kyte' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.ALPHA_CHECK) violated



the translate turns all valid characters (space plus alphabet) into 'X'
the replace turns all 'X's into nothing
if the replaced, translated, upper case string is now NULL - it is OK.

 

ORA-600

Dierk, August 09, 2006 - 1:12 pm UTC

Hi Tom,

I read the article "SQL Build Custom Aggregate Functions" of the oracle magazine. Therefore I merge that with the concat_all_ot example. By using the object concat_expr with the MAP MEMBER FUNCTION I get an ORA-600. Can you help me?
DROP TABLE car_rental;
CREATE TABLE car_rental (
car_id NUMBER,
cust_id NUMBER,
rental_state VARCHAR2(2),
rental_out TIMESTAMP,
rental_in TIMESTAMP
);
INSERT INTO car_rental VALUES (1, 101, 'MI', TIMESTAMP '2006-03-05 08:00:00.00', TIMESTAMP '2006-03-11 14:00:00.00');
INSERT INTO car_rental VALUES (1, 102, 'MI', TIMESTAMP '2006-03-12 08:34:24.00', TIMESTAMP '2006-03-17 19:23:19.00');
INSERT INTO car_rental VALUES (1, 103, 'MI', TIMESTAMP '2006-03-17 23:19:00.00', TIMESTAMP '2006-03-26 06:00:00.00');
INSERT INTO car_rental VALUES (1, 104, 'MI', TIMESTAMP '2006-03-28 09:45:15.00', TIMESTAMP '2006-03-30 15:27:13.00');
INSERT INTO car_rental VALUES (4, 105, 'MI', TIMESTAMP '2006-03-05 15:23:00.00', TIMESTAMP '2006-03-11 06:00:00.00');
INSERT INTO car_rental VALUES (4, 106, 'MI', TIMESTAMP '2006-03-12 17:34:24.00', TIMESTAMP '2006-03-17 18:23:19.00');
INSERT INTO car_rental VALUES (4, 107, 'MI', TIMESTAMP '2006-03-17 05:19:00.00', TIMESTAMP '2006-03-26 18:00:00.00');
INSERT INTO car_rental VALUES (4, 108, 'MI', TIMESTAMP '2006-03-28 12:45:15.00', TIMESTAMP '2006-03-30 03:27:13.00');
INSERT INTO car_rental VALUES (7, 109, 'MI', TIMESTAMP '2006-03-05 08:27:00.00', TIMESTAMP '2006-03-11 14:23:00.00');
INSERT INTO car_rental VALUES (7, 110, 'MI', TIMESTAMP '2006-03-12 08:19:24.00', TIMESTAMP '2006-03-17 19:56:19.00');
INSERT INTO car_rental VALUES (7, 111, 'MI', TIMESTAMP '2006-03-17 23:58:00.00', TIMESTAMP '2006-03-26 06:39:00.00');
INSERT INTO car_rental VALUES (7, 112, 'MI', TIMESTAMP '2006-03-28 09:02:15.00', TIMESTAMP '2006-03-30 15:01:13.00');
INSERT INTO car_rental VALUES (2, 113, 'WI', TIMESTAMP '2006-03-06 08:00:00.00', TIMESTAMP '2006-03-11 14:00:00.00');
INSERT INTO car_rental VALUES (2, 114, 'WI', TIMESTAMP '2006-03-13 08:34:24.00', TIMESTAMP '2006-03-17 19:23:19.00');
INSERT INTO car_rental VALUES (2, 115, 'WI', TIMESTAMP '2006-03-18 23:19:00.00', TIMESTAMP '2006-03-26 06:00:00.00');
INSERT INTO car_rental VALUES (2, 116, 'WI', TIMESTAMP '2006-03-29 09:45:15.00', TIMESTAMP '2006-03-30 15:27:13.00');
INSERT INTO car_rental VALUES (5, 117, 'WI', TIMESTAMP '2006-03-06 15:23:00.00', TIMESTAMP '2006-03-11 06:00:00.00');
INSERT INTO car_rental VALUES (5, 118, 'WI', TIMESTAMP '2006-03-13 17:34:24.00', TIMESTAMP '2006-03-17 18:23:19.00');
INSERT INTO car_rental VALUES (5, 119, 'WI', TIMESTAMP '2006-03-18 05:19:00.00', TIMESTAMP '2006-03-26 18:00:00.00');
INSERT INTO car_rental VALUES (5, 120, 'WI', TIMESTAMP '2006-03-29 12:45:15.00', TIMESTAMP '2006-03-30 03:27:13.00');
INSERT INTO car_rental VALUES (8, 121, 'WI', TIMESTAMP '2006-03-06 08:27:00.00', TIMESTAMP '2006-03-11 14:23:00.00');
INSERT INTO car_rental VALUES (8, 122, 'WI', TIMESTAMP '2006-03-13 08:19:24.00', TIMESTAMP '2006-03-17 19:56:19.00');
INSERT INTO car_rental VALUES (8, 123, 'WI', TIMESTAMP '2006-03-18 23:58:00.00', TIMESTAMP '2006-03-26 06:39:00.00');
INSERT INTO car_rental VALUES (8, 124, 'WI', TIMESTAMP '2006-03-29 09:02:15.00', TIMESTAMP '2006-03-30 15:01:13.00');
INSERT INTO car_rental VALUES (3, 125, 'MN', TIMESTAMP '2006-03-06 15:23:00.00', TIMESTAMP '2006-03-10 06:00:00.00');
INSERT INTO car_rental VALUES (3, 126, 'MN', TIMESTAMP '2006-03-13 17:34:24.00', TIMESTAMP '2006-03-16 18:23:19.00');
INSERT INTO car_rental VALUES (3, 127, 'MN', TIMESTAMP '2006-03-18 05:19:00.00', TIMESTAMP '2006-03-25 18:00:00.00');
INSERT INTO car_rental VALUES (3, 128, 'MN', TIMESTAMP '2006-03-29 12:45:15.00', TIMESTAMP '2006-03-29 03:27:13.00');
INSERT INTO car_rental VALUES (6, 129, 'MN', TIMESTAMP '2006-03-06 08:27:00.00', TIMESTAMP '2006-03-10 14:23:00.00');
INSERT INTO car_rental VALUES (6, 130, 'MN', TIMESTAMP '2006-03-13 08:19:24.00', TIMESTAMP '2006-03-16 19:56:19.00');
INSERT INTO car_rental VALUES (6, 131, 'MN', TIMESTAMP '2006-03-18 23:58:00.00', TIMESTAMP '2006-03-25 06:39:00.00');
INSERT INTO car_rental VALUES (6, 132, 'MN', TIMESTAMP '2006-03-29 09:02:15.00', TIMESTAMP '2006-03-29 15:01:13.00');
COMMIT;
CREATE OR REPLACE TYPE concat_expr AS OBJECT
(
str VARCHAR2 (4000)
, del VARCHAR2 (4000)

, MAP MEMBER FUNCTION mapping_function RETURN VARCHAR2

);
/

CREATE OR REPLACE TYPE BODY concat_expr
AS
MAP MEMBER FUNCTION mapping_function
RETURN VARCHAR2
IS
BEGIN
RETURN str || del;

END mapping_function;

END;
/

-- ########################################################################## --

CREATE OR REPLACE TYPE concat_all_ot AS OBJECT
(
str VARCHAR2 (4000)
, del VARCHAR2 (4000)

, STATIC FUNCTION odciaggregateinitialize( sctx IN OUT concat_all_ot)
RETURN NUMBER

, MEMBER FUNCTION odciaggregateiterate( SELF IN OUT concat_all_ot
, ctx IN concat_expr
)
RETURN NUMBER

, MEMBER FUNCTION odciaggregateterminate( SELF IN concat_all_ot
, returnvalue OUT VARCHAR2
, flags IN NUMBER
)
RETURN NUMBER

, MEMBER FUNCTION odciaggregatemerge( SELF IN OUT concat_all_ot
, ctx2 in concat_all_ot
)
RETURN NUMBER

, MEMBER FUNCTION ODCIAggregateDelete( self IN OUT concat_all_ot
, val IN concat_expr
)
RETURN NUMBER

);
/

-- ########################################################################## --

CREATE OR REPLACE TYPE BODY concat_all_ot
AS

--------------------------------------------------------------------------------

STATIC FUNCTION odciaggregateinitialize( sctx IN OUT concat_all_ot)
RETURN NUMBER
IS
BEGIN
if sctx is null
then
sctx := concat_all_ot( NULL, NULL);

else
sctx.str := null;
sctx.del := null;

end if;

RETURN odciconst.success;

END odciaggregateinitialize;

--------------------------------------------------------------------------------

MEMBER FUNCTION odciaggregateiterate( SELF IN OUT concat_all_ot
, ctx IN concat_expr
)
RETURN NUMBER
IS
BEGIN
IF SELF.str IS NOT NULL
THEN
SELF.str := SELF.str || ctx.del;

END IF;

SELF.str := SELF.str || ctx.str;

RETURN odciconst.success;

END odciaggregateiterate;

--------------------------------------------------------------------------------

MEMBER FUNCTION odciaggregateterminate( SELF IN concat_all_ot
, returnvalue OUT VARCHAR2
, flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := SELF.str;

RETURN odciconst.success;

END odciaggregateterminate;

--------------------------------------------------------------------------------

MEMBER FUNCTION odciaggregatemerge( SELF IN OUT concat_all_ot
, ctx2 IN concat_all_ot
)
RETURN NUMBER
IS
BEGIN
IF SELF.str IS NOT NULL
THEN
SELF.str := SELF.str || SELF.del;

END IF;

SELF.str := SELF.str || ctx2.str;

RETURN odciconst.success;

END odciaggregatemerge;

--------------------------------------------------------------------------------

MEMBER FUNCTION ODCIAggregateDelete( self IN OUT concat_all_ot
, val IN concat_expr
)
RETURN NUMBER
IS
v_pos_val_suf number;
v_pos_val_pref number;
v_pos_del_from number;
v_pos_del_to number;
v_new_str varchar2(4000);

BEGIN
IF SELF.str IS NOT NULL
THEN
SELF.str := substr( SELF.str, length( val.str || val.del) + 1);

END IF;

RETURN ODCIConst.Success;

END ODCIAggregateDelete;

--------------------------------------------------------------------------------

END;
/


CREATE OR REPLACE FUNCTION concat_all( ctx IN concat_expr)
RETURN VARCHAR2
DETERMINISTIC PARALLEL_ENABLE
AGGREGATE USING concat_all_ot;
/

The following Statements works:

column car_id_per_rental_state format a30
column car_id_per_rental_state_moved format a30
column car_id_per_rental_state_dis format a30
column sum_car_id format 999
column sum_car_id_moved format 999

select rental_state rental_state
, car_id
, concat_all( concat_expr (CAR_ID, '|'))
over( partition by rental_state) car_id_per_rental_state
, concat_all( concat_expr (CAR_ID, '|'))
over( partition by rental_state
order by rental_out
rows between 1 preceding and 1 following
) car_id_per_rental_state_moved
, sum( car_id) over( partition by rental_state) sum_car_id
, sum( car_id) over( partition by rental_state
order by rental_out
rows between 1 preceding and 1 following
) sum_car_id_moved
from car_rental
order by rental_out
;

select rental_state
, concat_all( distinct concat_expr (CAR_ID, '|')) car_id_per_rental_state_dis
from car_rental
group by rental_state
;

This Statement throw an ORA-600:
select rental_state rental_state
, car_id
, concat_all( distinct concat_expr (CAR_ID, '|'))
over( partition by rental_state) car_id_per_rental_state_dis
, concat_all( concat_expr (CAR_ID, '|'))
over( partition by rental_state) car_id_per_rental_state
, concat_all( concat_expr (CAR_ID, '|'))
over( partition by rental_state
order by rental_out
rows between 1 preceding and 1 following
) car_id_per_rental_state_moved
, sum( car_id) over( partition by rental_state) sum_car_id
, sum( car_id) over( partition by rental_state
order by rental_out
rows between 1 preceding and 1 following
) sum_car_id_moved
from car_rental
order by rental_out
;

ORA-00600: internal error code, arguments: [kopuigpfx1], [13], [], [], [], [], [], []

I try it with the version 9.2.0.6.0 and 10.2.0.1.0.

I don't find an advice in MetaLink.

Thanks

Dierk

Tom Kyte
August 09, 2006 - 4:46 pm UTC

ora-600 = please utilize support. You have metalink access, that is the place for this.

Getting ORA-03113 error: Am I missing something ?

Santosh Kompella, August 11, 2006 - 4:27 pm UTC

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 11 16:01:14 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> create or replace type string_agg_type as object
  2  (
  3       total varchar2(4000),
  4    
  5       static function
  6            ODCIAggregateInitialize(sctx IN OUT string_agg_type )
  7             return number,
  8     
  9        member function
 10             ODCIAggregateIterate(self IN OUT string_agg_type ,
 11                                  value IN varchar2 )
 12             return number,
 13     
 14        member function
 15             ODCIAggregateMerge(self IN OUT string_agg_type,
 16                                ctx2 IN string_agg_type)
 17             return number,
 18     
 19        member function
 20             ODCIAggregateTerminate(self IN string_agg_type,
 21                                    returnValue OUT  varchar2,
 22                                    flags IN number)
 23             return number
 24   );
 25  /
create or replace type string_agg_type as object
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 

Tom Kyte
August 11, 2006 - 6:09 pm UTC

please contact support.

check constraint

sat, August 23, 2006 - 1:34 pm UTC

How do we handle check constraint for the phone number

Say format should be XXX-XXXX otherwise it should raise an error.

sorry for the posting here

Tom Kyte
August 27, 2006 - 5:42 pm UTC

one way is:

check ( substr( col, 4,1 ) = '-' and translate(col,'0123456789','----------') = '--------' )

ops$tkyte%ORA10GR2> create table t
  2  (
  3  col varchar2(8)
  4  constraint check_phone check
  5     ( substr( col, 4,1 ) = '-'
  6       and
  7       translate(col,'0123456789','----------') = '--------'
  8     )
  9  )
 10  /

Table created.

ops$tkyte%ORA10GR2> insert into t values ( '123-4567' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( '12304567' );
insert into t values ( '12304567' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated


ops$tkyte%ORA10GR2> insert into t values ( '123-456' );
insert into t values ( '123-456' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated


ops$tkyte%ORA10GR2> insert into t values ( 'x23-4567' );
insert into t values ( 'x23-4567' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated


ops$tkyte%ORA10GR2> insert into t values ( ' 23-4567' );
insert into t values ( ' 23-4567' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated

 

problem in 10g

Rahul, January 08, 2007 - 3:26 pm UTC

nor i can use sys_connect_by_path
or stragg in the function

CREATE OR REPLACE FUNCTION APPS.PEL_NUM_CHARS(INSTRING VARCHAR2)
RETURN VARCHAR2
IS
l_output VARCHAR2(100);
BEGIN
select apps.stragg(ch)
--LTRIM(MAX(SYS_CONNECT_BY_PATH (ch , ',')),',')
into l_output
FROM (
SELECT SUBSTR(IN_STR, LEVEL , 1) ch , ROWNUM rn
FROM (SELECT INSTRING AS IN_STR FROM dual)
CONNECT BY LEVEL <= LENGTH(IN_STR)
)
-- START WITH rn = 1
-- CONNECT BY PRIOR rn = rn -1
;
return l_output;

exception when others then
return null;
END;
/

if the string is '11011' i want the output to be '1,1,0,1,1'

Multiple columns

Tom Fox, February 22, 2007 - 3:46 pm UTC

Tom,

Say I have a table:

ID  TYPE  NAME
1   AA1   Bob
1   AA2   Henry
1   AA3   Jack
1   AA4   Paul
2   AA1   Yo Yo Ma
2   AA5   Pete
2   AA9   Derek


I need to group by ID, and aggregate the type and name columns (unknown number per ID) into the following format:

1|AA1|Bob|AA2|Henry|AA3|Jack|AA4|Paul
2|AA1|Yo Yo Ma|AA5|Pete|AA9|Derek

I've been up and down and all around this answer, but not getting what I need. I know I could group by several other columns to get what I need then run Perl or sed and chop it up, but I'm trying to keep this contained to the database (while picking up some education). :)

Do you know a slick way to do this?
Tom Kyte
February 22, 2007 - 7:48 pm UTC

search this site for stragg

then you can

select id, stragg( type || '|' || name ) from t group by id

Tom Fox, February 22, 2007 - 7:54 pm UTC

Thanks! I saw stragg, but obviously I overlooked something. Have a great evening.

Concatenate Varchar2 in a single SQL without using Functions

Jaidev Thakur, February 25, 2007 - 11:26 pm UTC

SQL> select * from temp_temp

KEY            ERRORS
--------- ----------
S01992            631
S01992            632
S01992            634
S01994            701
S01994            705



select key,max(substr(sys_connect_by_path(errors,','),2)) errors
from   (select key, errors,  row_number() over ( partition by key order by
errors ) curr,
row_number() over ( partition by key order by errors ) - 1 prev  from
temp_temp )
start  with curr = 1
connect by prior curr = prev
and prior key= key
group by key
/

 

KEY    ERRORS
-----  -------

S01992 631,632,634
S01994 701,705


STRAGG - Reverse

patty, July 09, 2007 - 3:50 pm UTC

DEPTNO NAMES
---------- -------------------------------------------------
10 CLARK.KING.MILLER
20 SMITH.JONES.SCOTT.ADAMS.FORD
30 ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES

How to create this result:

10 CLARK
10 KING
10 MILLER
20 SMITH
20 JONES
20 SCOTT
20 ADAMS
20 FORD
30 ALLEN
30 WARD
.
.
.


Tom Kyte
July 09, 2007 - 8:54 pm UTC

ops$tkyte%ORA10GR2> create table your_data
  2  as
  3  select deptno, stragg( ename ) names
  4    from scott.emp
  5  group by deptno
  6  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with
  2  data
  3  as
  4  (
  5  select level r
  6    from dual
  7  connect by level <= 4000
  8  )
  9  select deptno, r, names,
 10         substr( names,
 11                 instr( names, ',', 1, r )+1,
 12                 instr( names, ',', 1, r+1 ) - instr( names, ',', 1, r ) - 1
 13                 ) nm
 14    from (select deptno, ','||names||',' names,
 15                 length(names)-length(replace(names,',',''))+1 elements
 16            from your_data
 17         ) your_data, data
 18   where data.r <= your_data.elements
 19   order by deptno, r
 20  /

    DEPTNO          R NAMES                                    NM
---------- ---------- ---------------------------------------- ----------------------------------------
        10          1 ,clark,king,miller,                      clark
        10          2 ,clark,king,miller,                      king
        10          3 ,clark,king,miller,                      miller
        20          1 ,smith,ford,adams,scott,jones,           smith
        20          2 ,smith,ford,adams,scott,jones,           ford
        20          3 ,smith,ford,adams,scott,jones,           adams
        20          4 ,smith,ford,adams,scott,jones,           scott
        20          5 ,smith,ford,adams,scott,jones,           jones
        30          1 ,allen,blake,martin,turner,james,ward,   allen
        30          2 ,allen,blake,martin,turner,james,ward,   blake
        30          3 ,allen,blake,martin,turner,james,ward,   martin
        30          4 ,allen,blake,martin,turner,james,ward,   turner
        30          5 ,allen,blake,martin,turner,james,ward,   james
        30          6 ,allen,blake,martin,turner,james,ward,   ward

14 rows selected.


RE: STRAGG - Reverse

Frank Zhou, July 10, 2007 - 10:51 am UTC


Patty,

Here are two more alternative SQL solutions for " Reverse the STRAGG"

Frank


http://www.jlcomp.demon.co.uk/faq/Split_Strings.html


SQL> set serveroutput on
SQL> Create table ts as
2 select 10 as id , 'CLARK.KING.MILLER' as str from dual
3 union all
4 select 20 as id , 'SMITH.JONES.SCOTT.ADAMS.FORD' as str from dual
5 union all
6 select 30 as id , 'ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES ' as str from dual
7 ;

Table created.

SQL> select * from ts;

ID STR
---------- -------------------------------------
10 CLARK.KING.MILLER
20 SMITH.JONES.SCOTT.ADAMS.FORD
30 ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES


SQL> COLUMN name FORMAT A12

SQL> select id as deptno, name_str.extract('/X/text()').getstringVal() name
2 from ts, table(xmlSequence(
3 extract(XMLType('<DOC><X>'||replace(str,'.','</X><X>')||'</X></DOC>'),'/DOC/X'))) name_str;

DEPTNO NAME
---------- ------------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 JONES
20 SCOTT
20 ADAMS
20 FORD
30 ALLEN
30 WARD
30 MARTIN

DEPTNO NAME
---------- ------------
30 BLAKE
30 TURNER
30 JAMES

14 rows selected.

SQL> spool off;

Reverse STRAGG

patty, July 10, 2007 - 11:30 am UTC

Much appreciated! This works beautifully. My data has an element of complexity in that it looks like this:

ID DATA (3 fields separated by columns, and pipes to separate each group of fields)
-- -----
10 3,,|5,x,|
20 5,,|
30 7,a,c|2,x,|10,,|
.
.

I'll work on splitting out the other two fields as well, but you've enabled me to get such a head start with this code you provided.

Many thanks.

Reverse Stragg

Patty, July 10, 2007 - 11:52 am UTC

Thank you to both of you!

RE: STRAGG - Reverse

Frank Zhou, July 10, 2007 - 12:15 pm UTC

Hi Patty,

How about the following query?

Is that answered your real question?

Frank

SQL> select * from ts2;

ID STR
---------- ----------------
10 3,,|5,x,|
20 5,,|
30 7,a,c|2,x,|10,,|

SQL> select id as deptno, name_str.extract('/X/text()').getstringVal() name
2 from
3 (select id, replace(rtrim(str, '|'), '|', ',') str from ts2),
4 table(xmlSequence(
5 extract(XMLType('<DOC><X>'||replace(str,',','</X><X>')||'</X></DOC>'),'/DOC/X'))) name_str
6 ;

DEPTNO NAME
---------- ------------
10 3
10
10
10 5
10 x
10
20 5
20
20
30 7
30 a
30 c
30 2
30 x
30
30 10
30
30

18 rows selected.

SQL>

reverse stragg

Patty, July 10, 2007 - 12:40 pm UTC

I should make myself clear.
Input:
ID STR
-------------------
10 3,,|5,x,|
20 5,,|
30 7,a,c|2,x,|10,,|

The output will end up like this:

ID STR CD1 CD2
-- --- --- ---
10 3
10 5 x
20 5
30 7 a c
30 2 x
30 10

RE: STRAGG - Reverse

Frank Zhou, July 10, 2007 - 2:43 pm UTC




SQL> select deptno, extractvalue(data, '//Z[1]') col_1,
2 extractvalue(data, '//Z[2]') col_2, extractvalue(data, '//Z[3]') col_3
3 from
4 (select deptno, XMLType('<DOC1><Z>'|| replace(name,',', '</Z><Z>')||'</Z></DOC1>') data
5 from
6 (select id as deptno, name_str.extract('/X/text()').getstringVal() name
7 from
8 (select id, rtrim(str, '|') str from ts2),
9 table(xmlSequence(
10 extract(XMLType('<DOC><X>'|| replace(str,'|', '</X><X>')||'</X></DOC>'),'/DOC/X'))
) name_str
11 )
12 );

DEPTNO COL COL COL
---------- --- --- ---
10 3
10 5 x
20 5
30 7 a c
30 2 x
30 10

6 rows selected.

Reverse STRAGG

Patty, July 10, 2007 - 4:30 pm UTC

Thank you, Frank.

I don't understand the code completely, but I was able to modify it to use my table and column names and got the results I need. Now - I need to attempt to figure out how it works!

Many thanks.

String concatenation

Tarun Sehgal, October 12, 2007 - 1:45 am UTC

Hi Tom,

I try to use your user defined aggregate function but while trying to use them in my query it gives me "end of file communication channel" error.

Plz suggest
Tom Kyte
October 14, 2007 - 9:19 pm UTC

if you can provide a test case from start to finish - create table, insert into table, run stragg, I'll take a look

short of that, please utilize support.

MODEL

Laurent Schneider, November 21, 2007 - 7:47 am UTC

I am aggreing with your remark about WM_CONCAT. I thought it was fun to post it on otn forums, but I should have specified more explicitely that it is undocumented. My bad.

Ok, I tried something else with model
select deptno, enames       
from emp   
model    
return updated rows     
partition by (deptno)         
dimension by (
   row_number() over (
      partition by deptno 
   order by ename) 
   rn)
measures (
   cast(ename as varchar2(4000)) enames)         
rules iterate (1000) until presentv(enames[ITERATION_NUMBER+3],1,2)=2 (
   enames[1]  = enames[1] || presentv(enames[2],',','') || enames[ITERATION_NUMBER+2]          
);
DEPTNO ENAMES
------ ------------------------------------
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    10 CLARK,KING,MILLER
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

it is then quite straightforward to change the order in the ORDER BY clause.

wm_concat

Barbara Boehmer, November 21, 2007 - 12:49 pm UTC

Hi Tom,

I saw wm_concat on a couple of forums (otn and orafaq), apparently a new, undocumented function as shown below.  Since it is undocumented, is it safe to use?

Regards,
Barbara

SCOTT@orcl_11g> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SCOTT@orcl_11g> describe wm_concat
FUNCTION wm_concat RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1                             VARCHAR2                IN

SCOTT@orcl_11g> column enames format a45 word_wrapped
SCOTT@orcl_11g> select deptno, wm_concat (ename) as enames from emp group by deptno
  2  /

    DEPTNO ENAMES
---------- ---------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

SCOTT@orcl_11g> 


   

Tom Kyte
November 20, 2007 - 2:05 pm UTC

my suggestion is going to be consistent....

Never use undocumented stuff, it is subject to change, removal, broken-ness without recourse.

either

a) use stragg
b) write your own
c) use the connect by trick.

stragg has sorting built in

Sean D. Stuber, January 02, 2008 - 3:43 pm UTC

I've seen several references to stragg not being able to sort. But I've been using it for a long time to do just that by using it as an analytic instead of an simple aggregate.

Is this unreliable?


SELECT DISTINCT deptno,
stragg(ename) OVER(PARTITION BY deptno ORDER BY ename DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM emp
ORDER BY deptno


Tom Kyte
January 02, 2008 - 3:59 pm UTC

that is using analytics and that has been covered first time about four or five years ago :)

http://asktom.oracle.com/pls/asktom/f?p=100:232:207529176231702::::P232_QUESTIONID:2196162600402#25879699852042
http://asktom.oracle.com/pls/asktom/f?p=100:232:207529176231702::::P232_QUESTIONID:2196162600402#7971282174613

Yes, that works, but isn't necessarily optimal - as it has to analytic - sort - then aggregate out the duplicates.

rather than aggregate+sort in one step.

An extension of concatenation

A reader, March 29, 2008 - 1:33 pm UTC

Am having a value like "40+50+60" for one of the columns, can i make that as 150 (i.e. total of 40,50 and 60) in sql itself.

For e.g

Table has

x y
- -------
A 40+50+60
B 239+34
C 12+22+45+11

I want the report to return as

A 150
B 273
C 90

Is it possible to get in sql itself, I can get that by creating a function which can accept "y" as parameter and can split em and sum it. Any better way?

Thank you
Mani
Tom Kyte
March 31, 2008 - 8:30 am UTC

ugh, that is not an extension of concatenation.

that is turning a database into a calculator.


You have to write a function

Summing concatenated numbers

A reader, March 30, 2008 - 9:32 am UTC

The follwing code was simpler i found to achive the total,

create or replace function calc_tot(x in varchar2) return number
as
str varchar2(4000):= 'Select '||trim(x)||' from dual';
temp number;

begin
execute immediate str into temp;
return temp;
exception
when others then
return -999999999;
end calc_tot;
/

function calc_tot(x Compiled.

select calc_tot('+50+30+343') from dual;

CALC_TOT(+50+30+343)
----------------------
423

1 rows selected

I know you will always give a simpler one, so waiting for that.

Thank you
Mani
Tom Kyte
March 31, 2008 - 9:20 am UTC

can you spell:

SQL INJECTION


google it, you are very much subject to it.

ouch:

when others then
return -999999999;
end calc_tot;
/

I'll never ever understand code like that... :( if you have an error, LET IT BE AN ERROR, don't hide it!

I can give you much more complex way using cast, multiset, lots of substrings - but I won't (you are doing this all wrong, this data should be evaluated AND THEN stored, not stored and evaluated, over and over and over and over and over again)

SQL Injection

A reader, April 01, 2008 - 12:20 am UTC

Its actually not my data, was just working on one of the questions in oracle forum http://forums.oracle.com/forums/message.jspa?messageID=2426622#2426622 . I understand the when others part mistake, could have simply raised it..just wanted to return some big -ve number to understand its an error.

Thanks
mani
Tom Kyte
April 01, 2008 - 9:21 am UTC

to understand it is an error - LET IT BE AN ERROR FOR GOODNESS SAKE.

What is more understandable:

return code = ORA-12345 Error happened!!!!!

or

return code = 0, everything is OK.


and that forum posting seems to have, well, absolutely nothing to do with this?

ref cursor

A reader, May 23, 2008 - 11:25 am UTC

should it work in procedure like this:
CREATE OR REPLACE procedure SOWK.sp_str(out_cursor out sys_refcursor) is
begin
open out_cursor for select stragg(distinct object_name) from all_objects;
end;
/

for me it returns error ORA-30482 "DISTINCT option not allowed for this function" during compilation on 9i and 10g. Is it my error or "natural" behavior of that, while
select stragg(distinct object_name) from all_objects
works nice.
Or maybe it's the same situation as the one in thread http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1058232381458#11384357177468

regards
Tom Kyte
May 23, 2008 - 6:13 pm UTC

it would be the same thing as the link

Karteek, May 29, 2008 - 11:49 am UTC

Tom,

Is there any constraint on sys_connect_by_path() like functions that they should used alone, but not together with another analytic function?

Below sql is not fetching any value in "str", but when I remove max() function it does.Can you help me why sys_connect_by_path is not working here?

select rn, max(rn) over(order by rn) dummy, sys_connect_by_path(rn,',') str 
from
(
select rownum rn from dual  connect by rownum < 5
)
start with rn = 1
connect by 
rn = prior rn+1



Thanks!
Tom Kyte
May 29, 2008 - 1:17 pm UTC

that would be a bug...

String Concatenation & storing the data into VARCHAR2(4000)

Hemal Deshmukh, June 27, 2008 - 1:56 pm UTC

Hello Tom,
I am having a PL/SQL nested having following type:-
-----
type char_table is table of varchar2(2000) index by binary_integer;

tbl_char_data char_table;
-----
tbl_char_data is having 3000 elements.
I want to concatenate the elements of this tbl_char_data as following tbl_char_data(1)||'|'||tbl_char_data(2)||'|'||tbl_char_data(3)...

and store it in a database table having a column size as VARCHAR2(4000).

Concatenated string comes to be more than 4000 characters.
Therefore I am not able to store it.
I am also not allowed to increase the column size or use any other data type.

Can you please advice how should we store this data.
Thanks and Best Regards
-Hemal

Tom Kyte
June 27, 2008 - 3:15 pm UTC

magic?

seriously - look at what you wrote, look at what you ask...


you will either

a) do something entirely different (i would suggest - umm - DO NOT STORE A CONCATENATED STRING - i hate that)

b) change your list of "we cannot do this" in order to make it possible to do

String Concatenation & storing the data into VARCHAR2(4000)

Hemal Deshmukh, June 28, 2008 - 4:56 am UTC

Hello Tom,
I am referring to the above thread "String Concatenation & storing the data into VARCHAR2(4000)" dated June 27, 2008 - 1pm US/Eastern

Client requires the concatenated string to be stored in VARCHAR2(4000) column.As mentioned in the above thread we are getting data through a PL/SQL table. We need to concatenate the elements and store the concatenated string in to the database table LONG_DATA_TABLE.

Following is the table structure

SQL> DESC LONG_DATA_TABLE;
Name Null? Type
-------------------- -------- ----------------------------
SEQ_GENERATED_NUM NUMBER
TEXT VARCHAR2(4000)



Client told me that suppose the concatenated data string is 8000 characters then store first 1 to 4000 characters
in one row and then store the remaining 4001 to 8000 characters in next row.

Client told me to write a stored procedure to concatenate the elements of the PL/SQL table and store the concatenated string into the LONG_DATA_TABLE.Incoming parameters will be the PL/SQL table.I need to loop through this PL/SQL table ,concatenate elements and store the data into the LONG_DATA_TABLE;

Can you please advice me as how should I write such a generic procedure.

Please help me.
Thanks and Best Regards
-Hemal Deshmukh

Tom Kyte
June 28, 2008 - 1:58 pm UTC

clients have software requirements

clients do not describe physical storage.


it would be silly to store this concatenated string in multiple rows to put together in client - when it would be the SAME CODE TO DO IT TO THE EXISTING DATA

do you get the point here - they either take some number of rows and in a client concatenate them (that varchar2(4000) approach) or they take some number of rows and in a client concatenate them (the leave the data as it is approach)

Now, which makes more sense.


(and if a developer could not do what was asked above - which I firmly believe should not be done - which is "build a string from an array - until adding to it would exceed 4000 characters, then reset string and start over - until it exceeds 4000 and so on".........................)

Parsing a string data into multiple columns data

Anan, September 19, 2008 - 11:39 pm UTC

Tom,

I have an external table data in Oracle 10g and needs help in parsing the data based on the following:

From FULLPATH data to break into ABC1 after /C- ABC2 after /C-O_
ABC3 after /A-A_
ABC4 after /R-R_
ABC5 after /R-M_


FULLPATH := '/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL';

ABC1 = 'TAG-MF-AVAIL'
ABC2 = 'APP_JA'
ABC3 = 'PROD-TAG-POOL'
ABC4 = 'AU00052355-PEOPLE'
ABC4 = 'AU00052355-SAL'

select SUBSTR('/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL',
INSTR('/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL','/',1,2-1)+3,
(INSTR('/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL','/',1,2)-3)-
INSTR('/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL','/',1,2-1)) from dual;

Thanks,
Tom Kyte
September 21, 2008 - 1:38 pm UTC

correct me if I'm wrong, but is the logic you tried to describe for parsing:

FULLPATH :=
'/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU000523
55-SAL';

ABC1 = 'TAG-MF-AVAIL'
ABC2 = 'APP_JA'
ABC3 = 'PROD-TAG-POOL'
ABC4 = 'AU00052355-PEOPLE'
ABC4 = 'AU00052355-SAL'


really:

a) look for /C- and take everything from there upto the next /
b) look for /C-O_ and take everything from there upto the next /

and so on? are the /<tags> FIXED, always c, c-o_ and so on.

Parsing a string data into multiple columns data

Anan, September 22, 2008 - 2:35 pm UTC

I am having problem in using REGEXP_SUBSTR to get

a) look for /C- and take everything from there upto the next /
b) look for /C-O_ and take everything from there upto the next /

and so on? are the /<tags> FIXED, always c, c-o_ and so on.


SELECT
REGEXP_SUBSTR('/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL',
'/C-([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------
/C-TAG

Thanks

Tom Kyte
September 23, 2008 - 12:55 pm UTC

so, I would not use regexp - you need a simple substr.

ops$tkyte%ORA10GR2> create table t ( x varchar2(100) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO T VALUES ( '/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select substr( c1, 1, instr(c1,'/')-1 ) c1,
  2         substr( c2, 1, instr(c2,'/')-1 ) c2,
  3         substr( c3, 1, instr(c3,'/')-1 ) c3,
  4         substr( c4, 1, instr(c4,'/')-1 ) c4,
  5         substr( c5, 1, instr(c5,'/')-1 ) c5
  6    from (
  7  select substr( x, instr( x, '/C-' )+4 ) c1,
  8         substr( x, instr( x, '/C-O_' ) +5 ) c2,
  9         substr( x, instr( x, '/A-A_' ) +5 ) c3,
 10         substr( x, instr( x, '/R-R_' ) +5 ) c4,
 11         substr( x, instr( x, '/R-M_' ) +5 ) ||'/' c5
 12    from t
 13         );

C1                   C2         C3                   C4                   C5
-------------------- ---------- -------------------- -------------------- --------------------
AG-MF-AVAIL          APP_JA     PROD-TAG-POOL        AU00052355-PEOPLE    AU00052355-SAL


Parameter as a column

A Reader, October 30, 2008 - 6:48 am UTC

Hi Tom,
My table is:

create table tablename(SNO NUMBER(38),NAME VARCHAR2(40),CMV BLOB,WVW BLOB,OLR BLOB,QPE BLOB,CTTI BLOB,GPS BLOB,SYS_DATE DATE);

My Procedure is this: But this procedure is working fine.

PROCEDURE SAT (
P_NAME VARCHAR2,
P_TYPE VARCHAR2,
P_RET_VAL OUT BLOB
)
AS

V_SYS_DATE1 DATE;
V_SYS_DATE2 DATE;

BEGIN

SELECT MAX(SYS_DATE) INTO V_SYS_DATE1 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3A';
SELECT MAX(SYS_DATE) INTO V_SYS_DATE2 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3B';



IF P_TYPE = 'CMV' THEN
SELECT CMV INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'WVW' THEN
SELECT WVW INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'OLR' THEN
SELECT OLR INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'QPE' THEN
SELECT QPE INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 ANDNAME = P_NAME;

ELSIF P_TYPE = 'CTTI' THEN
SELECT CTTI INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

ELSIF P_TYPE = 'GPS' THEN
SELECT GPS INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;

END IF;

END;





My requirement is this can we write a procedure like this if possible give me sample code.







PROCEDURE SAT (
P_NAME VARCHAR2,
P_TYPE VARCHAR2,
P_RET_VAL OUT BLOB
)
AS

V_SYS_DATE1 DATE;
V_SYS_DATE2 DATE;

BEGIN

SELECT MAX(SYS_DATE) INTO V_SYS_DATE1 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3A';
SELECT MAX(SYS_DATE) INTO V_SYS_DATE2 FROM INSAT_SAT_DET_MSTR WHERE TRIM(SAT_NAME) = 'INSAT-3B';




SELECT P_TYPE INTO P_RET_VAL FROM INSAT_SAT_DET_MSTR
WHERE SYS_DATE = V_SYS_DATE1 AND NAME = P_NAME;




Thanks in Advance
Tom Kyte
October 30, 2008 - 8:53 am UTC

the thing I hate more than IM speak is when people post the same exact thing over and over and over - like I'm going to pay more attention to it

I'm almost tempted to erase the answer at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696816290928#1281006500346829448

How to get the concatenated string SORTED ?

Philippe Gaudin, January 12, 2009 - 9:34 am UTC

Hi Tom,

I used the CAST(COLLECT()) as in the comment : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402#18313422264397
...but I need the concatenated string to be sorted.

I tried :

  SQL>   SELECT deptno,
  2           to_string (CAST (COLLECT (ename) AS ntt_varchar2)) AS emps
  3      FROM (  SELECT deptno,
  4                     ename
  5                FROM emp
  6            ORDER BY deptno,
  7                     ename)
  8  GROUP BY deptno;

    DEPTNO EMPS
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,SMITH,SCOTT,FORD,JONES
        30 ALLEN,JAMES,TURNER,WARD,MARTIN,BLAKE


...but, as you can see in DEPTNO 30 the list of employees is not ordered.

My question : how to get the list of employees sorted ?
Tom Kyte
January 12, 2009 - 9:15 pm UTC

did you ctl-f for ordered on this page and read the previous numerous conversations on this?

How to get the concatenated string SORTED ?

Philippe Gaudin, January 13, 2009 - 3:00 am UTC

I did CTRL-F ordered on the page... and could'nt find any solution to get the contatenated string sorted using the COLLECT technique.

There are solutions using the STRAGG aggregate function... but I wanted to know if it was possible using COLLECT (or something like that).

I wanted to know if it was possible to sort a NESTED TABLE.

What I could do is :
SQL> SELECT deptno,
       to_string(CAST (MULTISET (SELECT ename
                         FROM emp e2
                        WHERE e1.deptno = e2.deptno order by ename) as ntt_varchar2))
         AS tst
  FROM (SELECT DISTINCT deptno FROM emp) e1

    DEPTNO TST                                               
---------- --------------------------------------------------
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD              
        20 ADAMS,FORD,JONES,SCOTT,SMITH                      
        10 CLARK,KING,MILLER   

... but it has to go twice in the EMP table.

I think I will have to use the STRAGG function.

Tom Kyte
January 16, 2009 - 3:46 pm UTC

ops$tkyte%ORA9IR2> select deptno, substr( max( sys_connect_by_path(ename,',')), 2) enames
  2    from (
  3  select deptno, ename, row_number() over (partition by deptno order by ename) rn
  4    from emp
  5         )
  6   start with rn = 1
  7  connect by prior deptno = deptno and prior rn+1 = rn
  8  group by deptno
  9  order by deptno
 10  /

    DEPTNO ENAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


Concatinating a string with a complex parameters

Andrey, February 24, 2009 - 4:19 am UTC

Hi, Tom. I have a question.
In an implementing interface ODCIAggregate in
STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT str_concat_t)
we have a type context parameter. How can we pass it's value from outside? From an aggregate function, for example.
Or we can't.
Tom Kyte
February 24, 2009 - 6:03 am UTC

give a bit more context here, sketch out your requirement a bit better. the context parameter is a type in all of the examples here already?


More explanation about the problem

Andrey, February 24, 2009 - 7:11 am UTC

Ok. I have an object type like this:
CREATE OR REPLACE TYPE str_concat_t AS OBJECT
(
   /* Result string */
  ls_sum VARCHAR2(32767)
   /* Delimiter string */
, delim_f char(5) 
   /* Maximum length of a result string */
, maxStringLength_f number 
   /* Logical value, indicates should we add '...' to the
    *  end of the result string when the length of the 
    *  result is bigger then maxStringLength_f 
    *  1 - we should
    *  0 - we should not
    */
, dots_f number(1) 
   /* Logical value, which indicates should we throw an 
    *  Exception when the length of the result is bigger 
    *  then maxStringLength_f  */
, makeError_f number(1) 

, CONSTRUCTOR FUNCTION str_concat_t(delim char, max_length NUMBER, dots number, make_error number)
  RETURN SELF AS RESULT


, MEMBER FUNCTION get_separator RETURN VARCHAR2,
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT str_concat_t)
   RETURN NUMBER

, MEMBER FUNCTION ODCIAggregateIterate(self  IN OUT str_concat_t
                                      ,value IN VARCHAR2)
   RETURN NUMBER

, MEMBER FUNCTION ODCIAggregateMerge(self IN OUT str_concat_t
                                    ,ctx  IN strsum_t)
   RETURN NUMBER

, MEMBER FUNCTION ODCIAggregateTerminate(self  IN str_concat_t
                                        ,value OUT VARCHAR2
                                        ,flags IN NUMBER)
   RETURN NUMBER
)


Then we add a body. I think there's no need to put it here.
And make a aggregate function definition:
CREATE OR REPLACE FUNCTION str_concat (
   as_str_i      VARCHAR2
 )
 RETURN VARCHAR2
 PARALLEL_ENABLE  
  AGGREGATE USING 
  str_concat_t;


In the constructor we'll initialize the object's fields.
But, as i see we have no place to call this constructor in order to it'll be passed into an ODCIAggregateInitialize... Am i right?

If i am, then could we write some code into the aggregate function itself? For example code which will put necessary values into the sys_context.
Tom Kyte
February 24, 2009 - 4:41 pm UTC

... But, as i see we have no place to call this constructor in order to it'll be passed into an ODCIAggregateInitialize... Am i right? ...


it is called by us, we call it, you do what you want in it. Not sure what the issue is yet?

...
If i am, then could we write some code into the aggregate function itself? ...

you write all of the code, yes.

If the sys_context is to be used in the query itself, no, this will not be safe, if you need some "state", you can add more attributes to your object type - not sure what you would use sys context for - the query that invokes your code would already be "started", it would be too late to set context values for it.


Or do you mean "i'd like to write an aggregate that works like this:

select my_aggregate( a, b, c, d ) from t;

with many inputs"

More explanation about the problem 2

Andrey, February 25, 2009 - 5:42 am UTC

...it is called by us, we call it, you do what you want in it. Not sure what the issue is yet?...

I mean that we don't call ODCIAggregateInitialize explicitly, so we can't pass an object type parameter into it.

...you write all of the code, yes. ...

Nope. Oracle points an error here. We only can declare an aggregate function, but we can't implement it's body. Or just i don't know how.

...Or do you mean "i'd like to write an aggregate that works like this:

select my_aggregate( a, b, c, d ) from t;

with many inputs"...

Yep, that's a bit closer... Is it possible?
Tom Kyte
February 25, 2009 - 7:03 am UTC

I'm not following you at all here -
...
Nope. Oracle points an error here. We only can declare an aggregate function, but we can't implement it's body. Or just i don't know how.

..

you write the type body, we call it, you WRITE the aggregate.


If your goal is to be able to have more than one parameter to the aggregate, the only way I know is the approach already shown on this page.

ctl-f for:

"You would have to sort of "prime the pump" on this one."

to see it, it does involve a "context", you would set the parameters in the session context before invoking the aggregate, yes.




Converting multiple rows into single row

SuryaKiran, March 25, 2009 - 4:58 am UTC

create table test_asg(
Assignment_id number(15),
Title varchar2(50),
Emp_cat varchar2(2)
)

insert into test_asg values(100,'Comp info','AP')
insert into test_asg values(100,'Technician','CS')
insert into test_asg values(101,'Faculty','AP')
insert into test_asg values(102,'Teaching','CS')
insert into test_asg values(102,'Grad Assist','CS')
insert into test_asg values(102,'Secretary','AP')
insert into test_asg values(103,'Director','AP')

select assignment_id, substr(asg_title,2) JOB_TITLE, substr(asg_empcat,2) EMP_CATEGORY
from (
select assignment_id, max(sys_connect_by_path(title,';')) asg_title, max(sys_connect_by_path(emp_cat,';')) asg_empcat
from (
select assignment_id, title, emp_cat, row_number() over (partition by assignment_id order by assignment_id) rnum
from test_asg where assignment_id = 100
)
start with rnum = 1
connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
group by assignment_id
order by assignment_id
)
order by assignment_id

When we run the above query the result is :
assignment_id job_title emp_category
100 Comp info;Comp info AP;AP

It looks like the Job_title and emp_category was repeated which shouldn't be the case. They have to be different as seen below.


I just change the where clause from the inner select to the outer select.

select assignment_id, substr(asg_title,2) JOB_TITLE, substr(asg_empcat,2) EMP_CATEGORY
from (
select assignment_id, max(sys_connect_by_path(title,';')) asg_title, max(sys_connect_by_path(emp_cat,';')) asg_empcat
from (
select assignment_id, title, emp_cat, row_number() over (partition by assignment_id order by assignment_id) rnum
from test_asg
) where assignment_id = 100
start with rnum = 1
connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
group by assignment_id
order by assignment_id
)
order by assignment_id

Now by running the above query the result is :
assignment_id job_title emp_category
100 Comp info;Technician AP;CS

The second query result is correct one according to my requirement.Why is there a difference between these two queries and why in the first query did the titles repeat.
Can you please help me to resolve the issue ?
Tom Kyte
March 29, 2009 - 7:49 pm UTC

This is sufficient to demonstrate you have found a bug (I filed bug 8391928 for this)

ops$tkyte%ORA11GR1> create table test_asg(
  2  Assignment_id number(15),
  3  Title varchar2(50),
  4  Emp_cat varchar2(2)
  5  )    ;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into test_asg values(100,'Comp info','AP')    ;

1 row created.

ops$tkyte%ORA11GR1> insert into test_asg values(100,'Technician','CS')    ;

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select assignment_id, title, emp_cat,
  2         row_number() over (partition by assignment_id order by assignment_id) rnum
  3    from test_asg
  4   where assignment_id = 100
  5  /

ASSIGNMENT_ID TITLE                EMP_CAT                    RNUM
------------- -------------------- -------------------- ----------
          100 Comp info            AP                            1
          100 Technician           CS                            2

ops$tkyte%ORA11GR1> select assignment_id,
  2         sys_connect_by_path(title,';') asg_title,
  3         sys_connect_by_path(emp_cat,';') asg_empcat
  4    from (
  5  select assignment_id, title, emp_cat,
  6         row_number() over (partition by assignment_id order by assignment_id) rnum
  7    from test_asg
  8   where assignment_id = 100
  9         )
 10   start with rnum = 1
 11  connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
 12  /

ASSIGNMENT_ID ASG_TITLE             ASG_EMPCAT
------------- --------------------- --------------------
          100 ;Comp info            ;AP
          100 ;Comp info;Technician ;AP;CS

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select assignment_id,
  2         max( asg_title ) job_title,
  3         max( asg_empcat ) emp_category
  4    from (
  5  select assignment_id,
  6         sys_connect_by_path(title,';') asg_title,
  7         sys_connect_by_path(emp_cat,';') asg_empcat
  8    from (
  9  select assignment_id, title, emp_cat,
 10         row_number() over (partition by assignment_id order by assignment_id) rnum
 11    from test_asg
 12   where assignment_id = 100
 13         )
 14   start with rnum = 1
 15  connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
 16         )
 17   group by assignment_id
 18   order by assignment_id
 19  /

ASSIGNMENT_ID JOB_TITLE            EMP_CATEGORY
------------- -------------------- --------------------
          100 ;Comp info;Comp info ;AP;AP




I took query 1 and placed into query 2, query 2 shows the differing strings - we take query 2 and place it into query 3 with a MAX() and - well, data appears that doesn't exist.

Not repeat result

Serpini, April 14, 2009 - 11:50 am UTC

If you want not repeat result, like 'pepe,juan,pepe' you can rewrite this function

member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
position number;
begin
position:=INSTR(self.total,value);
DBMS_OUTPUT.PUT_LINE('Position:'|| position|| ' '||value);
IF(position is null) THEN
self.total := self.total || ',' || value;
elsif(position=0) then
self.total := self.total || ',' || value;
END IF;
return ODCIConst.Success;
end;
Tom Kyte
April 14, 2009 - 12:29 pm UTC

what happens if the strings are Pepe and Pepe Le Pew....


watch out for strings contained in other strings.

Calculating the value of a string with numbers and arithmetic operators

Logan Palanisamy, April 27, 2009 - 1:54 am UTC

Tom,

Is there way to calculate the value of an arithmetic expression stored as a string. I tried CAST, but it didn't work.


create table ts (f1 varchar2(100));
insert into ts values ('2+2+2+2');
insert into ts values ('1+100');
insert into ts values ('+1+10+2+30+5+7');
select * from ts;
select to_number(f1) from ts;
select to_number(2+2+2+2) from dual;
select to_number('2+2+2+2') from dual;





SQL>create table ts (f1 varchar2(100));

Table created.

SQL>insert into ts values ('2+2+2+2');

1 row created.

SQL>insert into ts values ('1+100');

1 row created.

SQL>insert into ts values ('+1+10+2+30+5+7');

1 row created.

SQL>select * from ts;

F1
-----------------------------------------------
2+2+2+2
1+100
+1+10+2+30+5+7

SQL>select to_number(f1) from ts;
select to_number(f1) from ts
       *
ERROR at line 1:
ORA-01722: invalid number


SQL>select to_number(2+2+2+2) from dual;

TO_NUMBER(2+2+2+2)
------------------
                 8

SQL>select to_number('2+2+2+2') from dual;
select to_number('2+2+2+2') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


Here is the output I want

select to_number(f1) value from ts;

Value
----------
8
101
55


Tom Kyte
April 27, 2009 - 2:18 pm UTC

think about it, how would '1+1' be 'castable' (change datatype) to a number?

It would have to be evaluated.



will it always be plus (addition) or in real is this a string of any degree of complexity (+, -, (), *, /, etc)

Evaluating an arithmetic string

Logan Palanisamy, April 27, 2009 - 3:08 pm UTC

Tom,

The strings contain either addition or mulitiplication, but not both. It can be of any lenght. No subtraction, division or parantheis or any other arithmetic operator.

e.g.

'+1+15+25+45'
'18*27*32'

The following is not a valid string
'8*10+15'


Tom Kyte
April 27, 2009 - 3:40 pm UTC

ops$tkyte%ORA10GR2> create table t ( x int, y varchar2(30) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1, '+1+15+25+45' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 2, '18*27*32' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select x, y, case when oper = '+' then sum(column_value)
  2                    else exp(sum(ln(case when oper='+' then 1 else column_value end)))
  3                            end result
  4    from (select x, y,
  5                 ','||translate( ltrim(y,'+'), '+*', ',,') ||',' newy,
  6                             case when instr( y, '*' ) > 0 then '*' else '+' end oper
  7                    from t ) t,
  8   TABLE(
  9   cast (
 10   multiset(
 11  select trim( substr(newy, instr (newy, ',', 1, level  ) + 1,
 12                  instr (newy, ',', 1, level+1) - instr (newy, ',', 1, level) -1 ) )
 13             from dual
 14           connect by level <= length(newy)-length(replace(newy,',',''))-1
 15  )
 16  as sys.odcinumberlist )
 17  )
 18  group by x, y
 19  /

         X Y                                  RESULT
---------- ------------------------------ ----------
         1 +1+15+25+45                            86
         2 18*27*32                            15552


Superb!!!

Logan Palanisamy, April 27, 2009 - 5:09 pm UTC


Not repeat result version 2

Serpini, April 28, 2009 - 7:26 am UTC

The solution of problem with Pepe and Pepe Le Pew... is split, I create a function for split like this:

----------
create or replace function fn_split (
p_cadena in varchar2,
p_simbolo in char,
p_ocurrencia in number default 1
) return varchar2 is
v_txt varchar2(200);
v_pos_ini integer := 0;
v_pos_fi integer;
begin
-- Función que separa cadenas de texto (p_cadena) por el símbolo p_simbolo
-- Devuelve la ocurrencia p_ocurrencia
-- Devuelve null si la ocurrencia no existe
-- Devuelve el símbolo p_simbolo si no hay ningún carácter entre dos
--ocurrencias de p_simbolo
if p_ocurrencia > 1 then
v_pos_ini := instr (
str1 => p_cadena,
str2 => p_simbolo,
pos => 1,
nth => p_ocurrencia - 1
);
end if;
v_pos_fi := instr (
str1 => p_cadena, -- test string
str2 => p_simbolo, -- string to locate
pos => 1, -- position
nth => p_ocurrencia -- occurrence number
);
if v_pos_ini = v_pos_fi then
if p_ocurrencia = 1 then
v_txt := substr (
str1 => p_cadena,
pos => 1
);
end if;
elsif v_pos_ini > v_pos_fi then
v_txt := substr (
str1 => p_cadena,
pos => v_pos_ini + 1
);
elsif v_pos_fi = v_pos_ini + 1 then
v_txt := p_simbolo;
else
v_txt := substr (
str1 => p_cadena,
pos => v_pos_ini + 1,
len => (v_pos_fi - v_pos_ini) - 1
);
end if;
--
return v_txt;
end fn_split;
-----------

And now member function ODCIAggregateIterate is:

-------------
member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
position number;
puntero number;
anterior varchar2(255);
begin
position:=INSTR(self.total,value);
IF(position is null) THEN
self.total := self.total || ',' || value;
elsif(position=0) then
self.total := self.total || ',' || value;
ELSE
puntero:=2;
anterior:=fn_split(self.total,',',puntero);
while((anterior is not null) and (anterior!=value)) loop
puntero:=puntero+1;
anterior:=fn_split(self.total,',',puntero);
end loop;
if((anterior!=value) OR (anterior is null)) then
self.total := self.total || ',' || value;
end if;
END IF;
return ODCIConst.Success;
end;
----------
Tom Kyte
April 28, 2009 - 10:15 am UTC

or, you could leave code "as is" and use distinct

ops$tkyte%ORA11GR1> select deptno, stragg(distinct job) a, stragg(job) b from emp group by deptno;

    DEPTNO A                              B
---------- ------------------------------ ------------------------------
        10 CLERK,MANAGER,PRESIDENT        MANAGER,CLERK,PRESIDENT
        20 ANALYST,CLERK,MANAGER          CLERK,CLERK,ANALYST,ANALYST,MA
                                          NAGER

        30 CLERK,MANAGER,SALESMAN         SALESMAN,SALESMAN,CLERK,SALESM
                                          AN,MANAGER,SALESMAN


varchar2 max size reduced to 8192

Michal Pravda, May 13, 2009 - 4:22 am UTC

Good morning Tom,

I encountered something I don't understand with (implicit) conversion from Clob to varchar2. There is some kind of "strange" size limitation of 8192 characters.

Piece of code is worth 1000 words, so here is an example of this behavior. Could you please tell me what am i doing wrong? I haven't found anything related in the documentation.

declare
cl clob;
str varchar2(15000 char);
begin
for idx in 1..1000 loop -- works fine
str := str || '0123456789';
dbms_output.put_line(idx);
end loop;

for idx in 1..1000 loop -- ends with "numeric or value error" when trying to
-- assign clob longer then 8192 to varchar2 variable
cl := cl || '0123456789';
dbms_output.put_line(idx);
str := cl;
end loop;

end;
/

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
Tom Kyte
May 13, 2009 - 1:51 pm UTC

what is your character set - any change the resulting STRING is using multibyte data (and your char(15000) is 15000 BYTES not characters...)


ops$tkyte%ORA10GR2> declare
  2  cl clob;
  3  str varchar2(15000 char);
  4  begin
  5    for idx in 1..1000 loop   -- works fine
  6      str := str || '0123456789';
  7      dbms_output.put_line(idx);
  8    end loop;
  9
 10    for idx in 1..1000 loop   -- ends with "numeric or value error" when trying to
 11                              -- assign clob longer then 8192 to varchar2 variable
 12      cl := cl || '0123456789';
 13      dbms_output.put_line(idx);
 14      str := cl;
 15    end loop;
 16
 17  end;
 18  /
1
2
3
4
5
6
7
...
992
993
994
995
996
997
998
999
1000

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>



ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> declare
  2  cl clob;
  3  str varchar2(15000 char);
  4  begin
  5    for idx in 1..1000 loop   -- works fine
  6      str := str || '0123456789';
  7    end loop;
  8    dbms_output.put_line( 'length = ' || length(str) );
  9
 10    for idx in 1..1000 loop   -- ends with "numeric or value error" when trying to
 11                              -- assign clob longer then 8192 to varchar2 variable
 12      cl := cl || '0123456789';
 13      str := cl;
 14    end loop;
 15    dbms_output.put_line( 'length = ' || length(str) );
 16  end;
 17  /
length = 10000
length = 10000

PL/SQL procedure successfully completed.



reduced string length

Michal Pravda, June 12, 2009 - 8:10 am UTC

Hello,
it does have something in common with charsets. At first I'd like to address your comment in parentheses.
[cite from Ora 9 plsql guide]
If you specify the maximum size in bytes rather than characters, a VARCHAR2(n)
variable might be too small to hold n multibyte characters. To avoid this possibility,
use the notation VARCHAR2(n CHAR)so that the variable can hold n characters in
the database character set, even if some of those characters contain multiple bytes.
When you specify the length in characters, the upper limit is still 32767 bytes. So for
double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many
characters as with a single-byte character set.
[/cite]
So I believed that varchar2(15000 char) would be sufficient.

The DB is utf8 but it shouldn't matter. I mean not at this boundary. I changed my example a bit to 32767 char variable (which equals 32767 byte variable, I believe). It should be sufficient even if every utf8 character was 3byte long. And I use ascii compatible digits for padding which are probably encoded using one byte per char

First DB - utf8, 10G
SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------------
NLS_LANGUAGE                   CZECH
NLS_TERRITORY                  CZECH REPUBLIC
NLS_CURRENCY                   Kc
NLS_ISO_CURRENCY               CZECH REPUBLIC
NLS_NUMERIC_CHARACTERS         ,.
NLS_CHARACTERSET               UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD.MM.RR
NLS_DATE_LANGUAGE              CZECH
NLS_SORT                       CZECH
NLS_TIME_FORMAT                HH24:MI:SSXFF

PARAMETER                      VALUE
------------------------------ ------------------------------------
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Kc
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           CHAR
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.4.0

20 rows selected.

--test
DECLARE
  cl CLOB;
  str VARCHAR2(32767 CHAR);
  tmp VARCHAR2(10);
BEGIN
  tmp := '0123456789';
  FOR idx IN 1 .. 1000 LOOP
    -- works fine
    str := str || tmp;
  END LOOP;
  dbms_output.put_line('length = ' || length(str));
  str := NULL;
  BEGIN
    FOR idx IN 1 .. 1000 LOOP
      -- ends with "numeric or value error" when trying to
      -- assign clob longer then 8192 to varchar2 variable
      cl  := cl || tmp;
      str := cl;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;
  dbms_output.put_line('clob2varchar length = ' || length(str));
  str := NULL;
  cl  := NULL;
  FOR idx IN 1 .. 1000 LOOP
    --works fine
    str := str || tmp;
    cl  := str;
  END LOOP;

  dbms_output.put_line('varchar2clob length = ' || length(str));
END;
/

SQL> /
length = 10000
ORA-06502: PL/SQL: numeric or value error
clob2varchar length = 8190
varchar2clob length = 10000


Second DB - Iso88592, 9i - works OK.

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CSMIG_SCHEMA_VERSION       2
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               EE8ISO8859P2
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              9.2.0.8.0

21 rows selected.

DECLARE
  cl CLOB;
  str VARCHAR2(32767 CHAR);
  tmp VARCHAR2(10);
BEGIN
  tmp := '0123456789';
  FOR idx IN 1 .. 1000 LOOP
    -- works fine
    str := str || tmp;
  END LOOP;
  dbms_output.put_line('length = ' || length(str));
  str := NULL;
  BEGIN
    FOR idx IN 1 .. 1000 LOOP
      --works fine
      cl  := cl || tmp;
      str := cl;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;
  dbms_output.put_line('clob2varchar length = ' || length(str));
  str := NULL;
  cl  := NULL;
  FOR idx IN 1 .. 1000 LOOP
    --works fine
    str := str || tmp;
    cl  := str;
  END LOOP;

  dbms_output.put_line('varchar2clob length = ' || length(str));
END;
/

length = 10000
clob2varchar length = 10000
varchar2clob length = 10000



Tom Kyte
June 12, 2009 - 11:27 am UTC

... So I believed that varchar2(15000 char) would be sufficient. ...

multibyte data is MULTI-byte (more than one, more than two even, or three), the longest string in plsql is 32k - no matter what.

so, that might not be sufficient.


in any case, why wouldn't you do a little debugging to see how big the string can actually get - don't just print out the error message, print out the length in bytes (lengthb) of the string as it was when it was last succcessfully appended to

reduced varchar2 size

Michal Pravda, June 15, 2009 - 5:11 am UTC

Hi,
I haven't done the "debugging" before because of something between
a) not very used to lengthB (and similar byte) function
and belief that
b) utf-8 uses up to 3 bytes so 8192 boundary has nothing in common with the problem. (which is wrong(originally utf-8 was up to 6 bytes, norm from 2003 reduced that to 4 (according to wikipaedia)))

Here's my revised script, it's results and my interpretation (ran on utf-8 10.2.04 DB with (NLS_NCHAR_CHARACTERSET AL16UTF16).

DECLARE
ncl NCLOB;
cl CLOB;
str VARCHAR2(32767);
tmp VARCHAR2(10);
BEGIN
-- tmp := chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980);
-- tmp := '0123456789';
tmp := '¿¿¿¿¿¿¿¿¿¿';

dbms_output.put_line('CLOB CLOB CLOB CLOB CLOB CLOB');
str := NULL;
BEGIN
FOR idx IN 1 .. 819 LOOP
-- ends with "numeric or value error" when trying to
-- assign clob longer then 8191 to varchar2 variable
cl := cl || tmp;
str := cl;
END LOOP;
cl := cl || '¿';
str := cl;
dbms_output.put_line('ok so far');
dbms_output.put_line('clob2varchar length = ' || length(str) || ', clob length ' || length(cl));
dbms_output.put_line('clob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(cl));
cl := cl || 'a';
str := cl;
dbms_output.put_line('ok so far');
dbms_output.put_line('clob2varchar length = ' || length(str) || ', clob length ' || length(cl));
dbms_output.put_line('clob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(cl));

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('clob2varchar length = ' || length(str) || ', clob length ' || length(cl));
dbms_output.put_line('clob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(cl));
END;

dbms_output.put_line('NCLOB NCLOB NCLOB NCLOB NCLOB NCLOB');
str := NULL;
BEGIN
FOR idx IN 1 .. 1638 LOOP
-- ends with "numeric or value error" when trying to
-- assign nclob longer then 16383 to varchar2 variable
ncl := ncl || tmp;
str := ncl;
END LOOP;
ncl := ncl || '¿¿¿';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' || length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
ncl := ncl || 'a';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' || length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' || length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
END;

dbms_output.put_line('NCLOB with kanji NCLOB with kanji NCLOB with kanji ');
str := NULL;
ncl := null;
tmp := chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980); BEGIN
FOR idx IN 1 .. 1092 LOOP
-- ends with "buffer too small" when trying to
-- assign nclob longer then 16383 to varchar2 variable
ncl := ncl || tmp;
str := ncl;
END LOOP;
ncl := ncl || '¿¿¿a';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' || length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
ncl := ncl || 'a';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' || length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' || length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
END;


END;
/

CLOB CLOB CLOB CLOB CLOB CLOB
ok so far
clob2varchar length = 8191, clob length 8191
clob2varchar lengthb= 16382, dbms_lob.getlength 8191
ORA-06502: PL/SQL: numeric or value error
clob2varchar length = 8191, clob length 8192
clob2varchar lengthb= 16382, dbms_lob.getlength 8192
NCLOB NCLOB NCLOB NCLOB NCLOB NCLOB
ok so far
nclob2varchar length = 16383, nclob length 16383
nclob2varchar lengthb= 32766, dbms_lob.getlength 16383
ORA-06502: PL/SQL: numeric or value error
nclob2varchar length = 16383, nclob length 16384
nclob2varchar lengthb= 32766, dbms_lob.getlength 16384
NCLOB with kanji NCLOB with kanji NCLOB with kanji
ok so far
nclob2varchar length = 10924, nclob length 10924
nclob2varchar lengthb= 32767, dbms_lob.getlength 10924
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
nclob2varchar length = 10924, nclob length 10925
nclob2varchar lengthb= 32767, dbms_lob.getlength 10925


Varchar2 max size is 32k -1 bytes. Utf-8 takes up to 4 chars so I think that Oracle takes the safe and fast way of raising an error when there is potential of buffer overflow regardless of actual data. So for this assignment there is a limit of trunc((32768 - 1) /4) = 8191 characters.

With NCLOB the situation is a bit different. I think that I am missing something, but don't know what.

Unicode with aforementioned charset (NLS_NCHAR_CHARACTERSET AL16UTF16) uses I think exactly two bytes per character. However you need 3 bytes to save some characters (codepoints) representable in this encoding when you want to reencode it in utf-8. So Oracle shouldn't allow (32767 /2) chars to be assigned to varchar2, because it can't guarantee that it will map all characters within NCLOB to utf-8 varchar2 with 2 or less bytes. Which is demonstrated in NCLOB with Kanji section.
Looks like Oracle takes the "fast and rather safe" way of allowing only (32768 - 1) / 2 chars to be assigned and relies on other mechanisms (buffer overflow check) to prevent the bad from happen.

OK

Kumar, July 09, 2009 - 9:08 am UTC

Hi Tom,
Can't a varchar2 value be added to a clob variable?
I dynamically construct a query from 3 clob variables and later I open a ref cursor like

open refc for select * from dyn_sql1||dyn_sql2||dyn_sql3;

But I am getting error as expression is of wrong type at the open statement.
Can you please advise?
Tom Kyte
July 14, 2009 - 2:30 pm UTC

... Can't a varchar2 value be added to a clob variable? ...

it could be pre-pended or appended - you don't really "add" strings.

and you still have the 32k limit - so it would not matter.

and the type of input to native dynamic sql in 10g and before is a varchar2, not a clob.


ops$tkyte%ORA10GR2> declare
  2          l_clob clob := 'select * from dual';
  3          c      sys_refcursor;
  4  begin
  5          open c for l_clob;
  6  end;
  7  /
        open c for l_clob;
                   *
ERROR at line 5:
ORA-06550: line 5, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored


ops$tkyte%ORA10GR2> declare
  2          l_clob varchar2(100) := 'select * from dual';
  3          c      sys_refcursor;
  4  begin
  5          open c for l_clob;
  6  end;
  7  /

PL/SQL procedure successfully completed.




and the result of clob || varchar2 is going to be a clob. you could to_char() it

ops$tkyte%ORA10GR2> declare
  2          l_clob clob := 'select * from dual';
  3          l_varchar2 varchar2(100) := ' xxx';
  4          c      sys_refcursor;
  5  begin
  6          open c for to_char(l_clob||l_varchar2);
  7  end;
  8  /

PL/SQL procedure successfully completed.



In 11g -clobs are OK with native dynamic sql.

A reader, November 23, 2009 - 6:57 am UTC

what u recommand about using sys.stragg. I 11G in Sql we can use Distinct with your function "Stragg" but when we use it in Procedure it gives compilation error.

Tom Kyte
November 23, 2009 - 4:35 pm UTC

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo



that is what I recommend - find this U guy and ask him??




can you use a view to hide the construct from plsql - and file a bug?


ops$tkyte%ORA11GR2> select deptno, stragg(distinct job), stragg(job) from scott.emp group by deptno  2  /

    DEPTNO
----------
STRAGG(DISTINCTJOB)
-------------------------------------------------------------------------------
STRAGG(JOB)
-------------------------------------------------------------------------------
        10
CLERK,MANAGER,PRESIDENT
MANAGER,CLERK,PRESIDENT

        20
ANALYST,CLERK,MANAGER
CLERK,ANALYST,CLERK,ANALYST,MANAGER

        30
CLERK,MANAGER,SALESMAN
SALESMAN,CLERK,SALESMAN,MANAGER,SALESMAN,SALESMAN


ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
  2  for x in (
  3  select deptno, stragg(distinct job) s1, stragg(job) s2 from scott.emp group by deptno
  4  )
  5  loop
  6          null;
  7  end loop;
  8  end;
  9  /
select deptno, stragg(distinct job) s1, stragg(job) s2 from scott.emp group by deptno
               *
ERROR at line 3:
ORA-06550: line 3, column 16:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored


ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace view v
  2  as
  3  select deptno, stragg(distinct job) s1, stragg(job) s2 from scott.emp group by deptno
  4  /

View created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
  2  for x in (
  3  select * from v
  4  )
  5  loop
  6          null;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

thanks

A reader, November 24, 2009 - 1:01 am UTC

Thanks a lot. High level of mistake took place "U" will be careful in future.

8i possibility

Sinan Topuz, March 10, 2010 - 11:18 am UTC

Hi Tom,

I know you wrote that "Not in 8i, in 9i yes" for this. I am able to compile the type specification and body, but cannot compile stragg standalone function in 8.1.7.4 Standard Edition. Is it because of the line "aggregate using stragg_type" in the function body? That was a new keyword in 9i, which 8i did not have? Nothing I can do about this in 8i?

Thanks,
Sinan
Tom Kyte
March 11, 2010 - 7:50 am UTC

Oracle 9i, about a decade ago, added the capability for users to construct their own "user defined aggregates". It was (about a decade ago) a new feature of Oracle 9i and above - it simply did not exist in software written in the last century...

Here is a way to overcome the 4000 characters

Amiel Davis, March 14, 2010 - 2:39 pm UTC

Here is a way to overcome the 4000 characters in an old an instance (9.2 and up)
using the built in function XmlAgg. Since the result is quite large, I will only show the result length:

SQL_92>select length(stragg(object_name)) as str_length
2 from all_objects
3 where owner = 'SYS' and rownum <=5000;

select stragg(object_name) as str
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error:
character string buffer too small
ORA-06512: at "AMI.STRAGG_TYPE", line 13

SQL_112>select length(listagg(object_name)
2 within group(order by object_id )) as str_length
3 from all_objects
4 where owner = 'SYS' and rownum <=5000;

*
ERROR at line 4:
ORA-01489: result of string concatenation is too long

SQL_92>select dbms_lob.getlength(
2 rtrim(
3 replace(
4 replace(
5 XmlAgg(
6 XmlElement("a", object_name)
7 order by
8 last_ddl_time desc nulls last)
9 .getClobVal(),
10 '<a>', ''),
11 '</a>',
12 ','),
13 ',')
14 ) as str_length
15 from all_objects
16 where owner = 'SYS' and rownum <=5000;

STR_LENGTH
----------
4301

Did not benchmark it though...
Amiel Davis


Column Values

A reader, March 27, 2010 - 5:04 pm UTC

Hi Tom,

I have a table with a column where data is available with ,. Maximum of three column values can be there, and I want to get data in seperate columns. Could you please let me know how can I get this?
create table test ( a varchar2(100));
insert into test values('1234567890,0123456789,1234,');
insert into test values('123456,654321,');
COMMIT;
SQL> select * from test;

A
------------------------------
1234567890,0123456789,1234,
123456,654321,

o/p required

Col1, Col2, col3
1st row-> 1234567890 0123456789 1234
2nd row-> 123456 654321 NULL

Thanks

Tom Kyte
April 05, 2010 - 9:09 am UTC

you don't show what would be there if there are only one and zero values :(

I'll guess

create table test ( a varchar2(100));
insert into test values('1234567890,0123456789,1234,');
insert into test values('123456,654321,');
insert into test values('123456,,');
insert into test values(',,');
COMMIT;

did I guess right? If not, you can fix it, I'll work with that assumption, show you the TECHNIQUE and you can apply that TECHNIQUE to your problem, adjusting it as necessary to make it work with your specific data:

ops$tkyte%ORA10GR2> select a,
  2         substr( a, 1, instr(a,',')-1 ) c1,
  3         substr( a, instr(a,',', 1, 1)+1, instr( a, ',', 1, 2 )-instr( a, ',', 1, 1)-1 ) c2,
  4         substr( a, instr(a,',', 1, 2)+1, instr( a, ',', 1, 3 )-instr( a, ',', 1, 2)-1 ) c3
  5   from test
  6  /

A                              C1         C2         C3
------------------------------ ---------- ---------- ----------
1234567890,0123456789,1234,    1234567890 0123456789 1234
123456,654321,                 123456     654321
123456,,                       123456
,,

Stragg function - issue

Ramesh, May 04, 2010 - 4:08 pm UTC

I am trying to use stragg function, but its keep giving following SQL error under 10gR2 environment.

ERROR at line 2:
ORA-00937: not a single-group group function

Unless I do group by with all the rest of the columns when stragg used, error keep coming.

I used following stragg version:

create or replace type aradmin.StringAggType as object
(
theString varchar2(100000),

static function
ODCIAggregateInitialize(sctx IN OUT StringAggType )
return number,

member function
ODCIAggregateIterate(self IN OUT StringAggType ,
value IN varchar2 )
return number,

member function
ODCIAggregateTerminate(self IN StringAggType,
returnValue OUT varchar2,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT StringAggType,

member function
ODCIAggregateMerge(self IN OUT StringAggType,
ctx2 IN StringAggType)
return number
);
/


create or replace type body aradmin.StringAggType
is

static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
return number
is
begin
sctx := StringAggType( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT StringAggType,
value IN varchar2 )
return number
is
begin
self.theString := self.theString || ', ' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN StringAggType,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := rtrim( ltrim( self.theString, ',' ), ',' );

return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT StringAggType,
ctx2 IN StringAggType)
return number
is
begin
self.theString := self.theString || ', ' || ctx2.theString;
return ODCIConst.Success;
end;


end;
/


CREATE or replace
FUNCTION aradmin.stringAgg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING StringAggType;
/

Do I need to copy any other newer version of stragg function code in order to execute without group by.
Thank You
Ramesh


Tom Kyte
May 06, 2010 - 1:39 pm UTC

geez, you don't think the QUERY you used would be relevant?

it is, because the query you wrote has a bug in it - and we'd need to see it to tell you what you did wrong.


stragg can be used as an aggregate function (with group by)

or stragg can be used as an analytic function (without group by - but using OVER( partition by... order by ... )

It doesn't work

Dimarrik, August 06, 2010 - 8:53 am UTC

Hi Tom,
Why does it query returning NULL ?

with t as
(select 1 id , 'c' nm from dual union all
select 1 id , 'a' from dual union all
select 1 id , 'b' from dual
)
select max(stragg(nm) over (order by nm)) from t

I would like to have sorted list of values (a,b,c)

Tom Kyte
August 06, 2010 - 10:33 am UTC

It is a bug - it should be raising an error:

ORA-30483: window  functions are not allowed here


ops$tkyte%ORA10GR2> !oerr ora 30483
30483, 00000, "window  functions are not allowed here"
// *Cause: Window functions are allowed only in the SELECT list of a query.
//         And, window function cannot be an argument to another window or group
//         function.
// *Action:


if we use an 'official' window function:

ops$tkyte%ORA10GR2> with t as
  2  (select 1 id , 'c' nm  from dual union all
  3  select 1 id , 'a'  from dual union all
  4  select 1 id , 'b'  from dual
  5   )
  6  select (x) from (
  7  select max(sum(id) over (order by nm)) x from t
  8  )
  9  /
select max(sum(id) over (order by nm)) x from t
           *
ERROR at line 7:
ORA-30483: window  functions are not allowed here



it see's it and aborts - for some reason, a user defined window function isn't being trapped, in any case:

ops$tkyte%ORA10GR2> with t as
  2  (select 1 id , 'c' nm  from dual union all
  3  select 1 id , 'a'  from dual union all
  4  select 1 id , 'b'  from dual
  5   )
  6  select max(x) from (
  7  select (stragg(nm) over (order by nm)) x from t
  8  )
  9  /

MAX(X)
-------------------------------------------------------------------------------
a,b,c


would be appropriate.

pl/sql

siri, January 02, 2011 - 11:41 pm UTC

hi tom,
i have the data in a table like this
1 siri
1 lakshmi
2 ram
2 rs
3 madhu
3 lavanya. i want to concatenate two strings which are having same no's.and if length of the concatenate string exceeds more than 4 characters remaining characters will display second line along with slno's.how can we write pl/sql code for this program.
Tom Kyte
January 03, 2011 - 8:57 am UTC

Look in the other place you posted THIS SAME EXACT QUESTION

and please only put them in one place from now on

String Concatenation

A reader, July 15, 2011 - 12:06 pm UTC

hi Tom,

I have a question regarding the string concatenation. Suppose we have multiple rows in the table. I have to make a concatenated result of the unique elements in the string. The string elements are not known.. Default is '|' and it can have any number of elements.

Lets say:

CREATE TABLE table_A(col_1 NUMBER, col_2 VARCHAR2(100));

INSERT INTO table_A values(1, '|X|Y|Z|');
INSERT INTO table_A values(1, '|X|Y|');
INSERT INTO table_A values(2, '|X|Y|A|');
INSERT INTO table_A values(2, '|A|B|Y|X|');


Now then requirement:

1. Select the elements that appear in all rows of col_2 irrespective of col_1 values. So the result should be '|X|Y|' as it appears in all rows.

2. Select the elements that appear in all rows of col_2 with respect to col1_values. So the result should be

COL_1 COL_2
1 |X|Y|
2 |X|Y|A|


3. Select the elements which appear more than 'n' time where n = 1,2,3.... in col_2 irrespective col_1

When n = 1: Result is '|X|Y|A|'

Could you please help.

Tom Kyte
July 18, 2011 - 10:08 am UTC

I can help by first pointing out that whoever designed this 'data model' should never be allowed to have CREATE TABLE granted to them again. This is horrific. Completely and totally. You applications probably spend half of their life stringing up and unstringing up this junk.

examples below with listagg are 11gr2 and above, examples with sys_connect_by_path are 10g and above.

If you are 9i - search this site for stragg and use it in place of listagg.

If you 8i or older, upgrade :)

I've left the last answer partially incomplete - I want you to write it to do the stringing up - just to make sure you know what is happening there....



ops$tkyte%ORA11GR2> CREATE TABLE t(col_1 NUMBER, col_2 VARCHAR2(100));

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> INSERT INTO t values(1, '|X|Y|Z|');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO t values(1, '|X|Y|');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO t values(2, '|X|Y|A|');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO t values(2, '|A|B|Y|X|');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select '|' || listagg(column_value,'|') within group (order by column_value) || '|'
  2    from (
  3  select column_value
  4    from (select count(*) over () cnt, col_1, col_2 from t),
  5         table(
  6         cast(
  7         multiset(
  8             select substr( col_2,
  9                         instr( col_2, '|', 1, level ) + 1,
 10                         instr( col_2, '|', 1, level+1 ) - instr( col_2, '|', 1, level ) - 1 )
 11            from dual
 12         connect by level <= length(col_2)-length(replace(col_2,'|','' )) -1
 13         )
 14         as sys.odcivarchar2List)
 15         )
 16   group by cnt , column_value
 17  having count(*) = cnt
 18         )
 19  /

'|'||LISTAGG(COLUMN_VALUE,'|')WITHINGROUP(ORDERBYCOLUMN_VALUE)||'|'
-------------------------------------------------------------------------------
|X|Y|

ops$tkyte%ORA11GR2> select max(sys_connect_by_path(column_value,'|')) || '|'
  2    from (
  3  select column_value, row_number() over (order by column_value) rn
  4    from (select count(*) over () cnt, col_1, col_2 from t),
  5         table(
  6         cast(
  7         multiset(
  8             select substr( col_2,
  9                         instr( col_2, '|', 1, level ) + 1,
 10                         instr( col_2, '|', 1, level+1 ) - instr( col_2, '|', 1, level ) - 1 )
 11            from dual
 12         connect by level <= length(col_2)-length(replace(col_2,'|','' )) -1
 13         )
 14         as sys.odcivarchar2List)
 15         )
 16   group by cnt , column_value
 17  having count(*) = cnt
 18         )
 19   start with rn = 1 connect by prior rn+1 = rn
 20  /

MAX(SYS_CONNECT_BY_PATH(COLUMN_VALUE,'|'))||'|'
-------------------------------------------------------------------------------
|X|Y|

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select col_1, '|'||listagg(column_value,'|') within group (order by column_value)||'|' str
  2   from (
  3  select col_1, column_value
  4    from (select count(*) over (partition by col_1) cnt, col_1, col_2 from t),
  5         table(
  6         cast(
  7         multiset(
  8             select substr( col_2,
  9                         instr( col_2, '|', 1, level ) + 1,
 10                         instr( col_2, '|', 1, level+1 ) - instr( col_2, '|', 1, level ) - 1 )
 11            from dual
 12         connect by level <= length(col_2)-length(replace(col_2,'|','' )) -1
 13         )
 14         as sys.odcivarchar2List)
 15         )
 16   group by cnt, col_1, column_value
 17  having count(*) = cnt
 18         )
 19  group by col_1
 20  order by col_1
 21  /

     COL_1 STR
---------- --------------------
         1 |X|Y|
         2 |A|X|Y|

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select col_1, max(sys_connect_by_path(column_value,'|')) || '|' str
  2   from (
  3  select col_1, column_value, row_number() over (partition by col_1 order by column_value) rn
  4    from (select count(*) over (partition by col_1) cnt, col_1, col_2 from t),
  5         table(
  6         cast(
  7         multiset(
  8             select substr( col_2,
  9                         instr( col_2, '|', 1, level ) + 1,
 10                         instr( col_2, '|', 1, level+1 ) - instr( col_2, '|', 1, level ) - 1 )
 11            from dual
 12         connect by level <= length(col_2)-length(replace(col_2,'|','' )) -1
 13         )
 14         as sys.odcivarchar2List)
 15         )
 16   group by cnt, col_1, column_value
 17  having count(*) = cnt
 18         )
 19  start with rn = 1 connect by prior col_1 = col_1 and prior rn+1 = rn
 20  group by col_1
 21  order by col_1
 22  /

     COL_1 STR
---------- --------------------
         1 |X|Y|
         2 |A|X|Y|

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable n number
ops$tkyte%ORA11GR2> exec :n := 1;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select column_value
  2    from (select col_1, col_2 from t),
  3         table(
  4         cast(
  5         multiset(
  6             select substr( col_2,
  7                         instr( col_2, '|', 1, level ) + 1,
  8                         instr( col_2, '|', 1, level+1 ) - instr( col_2, '|', 1, level ) - 1 )
  9            from dual
 10         connect by level <= length(col_2)-length(replace(col_2,'|','' )) -1
 11         )
 12         as sys.odcivarchar2List)
 13         )
 14   group by column_value
 15  having count(*) > :n
 16  /

COLUMN_VAL
----------
Y
X
A


Just to add for String Concatenation Question

A reader, July 15, 2011 - 12:08 pm UTC

Oracle Version is 10GR1.

Thanks for the answer

A reader, July 18, 2011 - 3:06 pm UTC

hi Tom,

Thanks a nice elegant solution. I didn't know to do that in a single query set.

I was trying with loops and collections and came up with a solution. It may not be that good but please consider a look.

I tried to search for the elements which are common to all rows.

What I did was:

Step 1: Take the longest string(maximum pipes) in the table, considering the more elements it has, the longer will be the string. Lets say, the longest was

'|X|Y|Z|A|' -->Longest
'|X|Y|A|'
'|X|Y|Z|'



Step 2: Parsed the string into a table collection.

my_tab tbl_collection_varchar;
my_tab(1) := 'X'
my_tab(2) := 'Y'
my_tab(3) := 'Z'
my_tab(4) := 'A'


Step 3: Took the number of rows in the original table object. lets say,
select count(*) into v_tab_count from tbl; --> 3


Step 4: Now I looped the table collection with an INSTR to get the count of each element. If count of INSTR = Count of rows in table, put it in a variable and continue concatenation for rest of elements of collection.

FOR i in my_tab.FIRST..my_tab.LAST loop
select count(*) into v_ele_count from tbl where INSTR(column, my_tab(i) )>0;
IF v_ele_count = v_tab_count THEN
  v_str := v_str + my_tab(i);
END IF;
END LOOP;


In this way I can also keep a count in another table collection which is indexed by VARCHAR2. And then I can do the operations for other options.

Please do let me know if this approach is ok? If not, where are the disadvantages.


Tom Kyte
July 18, 2011 - 6:56 pm UTC

Please do let me know if this approach is ok? If not, where are the disadvantages.


benchmark it. I'm not a fan of procedural code if I can do it in SQL.

I'm pretty sure you'll find your procedural code to be a bit slower. I see TONS of full scans in your approach.




oh, and by the way, why not start with the shortest string if you were going procedural. While it is true that the longest string MUST have the common set of all values - so must the shortest string (since ALL strings must have all of those values - the number of values shared by every string will be a subset of the shortest string too!).

But - I doubt seriously you'll find the procedural approach to be

a) faster
b) easier to code
c) less buggy ;)


Thanks

A reader, July 19, 2011 - 11:49 am UTC

Hi Tom,

I understood the point as the table I have has around 2 million rows. And it the string has 30 elements concatenated with each other, so with my approach I will do 30 times full table scan of the big table, which is in any case bad.


Now my question is: Is sys_connect_by_path advisable on such a big table which can get bigger in production environment? Do we have any performance problems in production that you might have observed because of sys_connect_by_path?
Tom Kyte
July 19, 2011 - 1:21 pm UTC

are you doing this for every row in that table?

your nightmare has just begun my friend, just begun. And you have the "designer" of this schema to thank for it. I hope they are not around any more - because you will be tempted to hurt them in some fashion soon I think, I know I would.


So, this will turn that 2,000,000 row table into a temporary 2mill*30 row table - and then aggregate. Configure a really big PGA workarea for sure....


connect by is just the last of many issues here.... but the good news is the connect by happens LAST - to the smallest amount of data.

I'd be more worried about the turning of 2mill rows into 60mill temporary rows...

Yes it is true

A reader, July 19, 2011 - 2:13 pm UTC

Hi Tom,

Thanks for the reply.

I had this in my back of mind when I was asking you for the workaround.

I have to look into other solution. May be I have to now find the source(table) from where they are comparing something and concatenating. May be that table with have the lesser entries as they may be look-up tables.

Can't afford nightmares because of that one :)
Tom Kyte
July 19, 2011 - 4:02 pm UTC

I'd still be *very* curious as to what their thoughts where here - what they were trying to retrieve? what they *thought* they should be retrieving???


A reader, July 20, 2011 - 11:49 am UTC

Hi Tom,

There are two tables :

CREATE TABLE Master_TBL(col_1 number primary key, col_2 varchar2(100));
CREATE TABLE Child_TBL(col_3 number primary key, col_4 number, col_5 varchar2(100));

The Master table has around 2-3 million records. Child_Tbl has around 1 million records.

Now the logic is
The child table has Child_TBL.col_4 column mapped with the Master_TBL.col_1 of Master table which can have multiple rows and accordingly multiple Child_TBL.col_5 values, which may have 30 elements concatenated |P|Q|R|S|T|U.....

Master Table Data:

Col_1 Col_2
M1 NULL
M2 NULL

Child Table Data:

Col_3 Col_4 Col_5
C1 M1 |X|Y|
C2 M1 |X|A|
C3 M2 |P|Q|R|
C4 M2 |P|X|R|
C5 M2 |A|P|S|

So now for each distinct master entry in the child table, check for the common entries and update master_tbl.col_2 column.

So the intermediate result is:

M1 -> |X|Y|X|A|
M2 -> |P|Q|R|P|X|R|A|P|S|

And after update the final result in Master Table is:
Col_1 Col_2
M1 |X|Y|A|
M2 |P|



Sorry for result

A reader, July 20, 2011 - 11:51 am UTC

And after update the final result in Master Table is:
Col_1 Col_2
M1 |X|
M2 |P|

As only these elements appeared in all rows for corresponding master entries.

ORA-30482 with listagg

Sokrates, August 11, 2011 - 6:20 am UTC

sokrates@11.2 > with d as (
  2  select 1 a, 'x' b from dual union all
  3  select 1, 'y' from dual union all
  4  select 1, 'x' from dual
  5  )
  6  select a, listagg(b) within group (order by b)
  7  from d
  8  group by a
  9  /

  A
----------
LISTAGG(B)WITHINGROUP(ORDERBYB)
--------------------------------------------------------------------------------
  1
xxy



lovely, but i want 'x' only once (in fact, every b, i want only once).
should be easy, though:

sokrates@11.2 > with d as (
  2  select 1 a, 'x' b from dual union all
  3  select 1, 'y' from dual union all
  4  select 1, 'x' from dual
  5  )
  6  select a, listagg(distinct b) within group (order by b)
  7  from d
  8  group by a
  9  /
select a, listagg(distinct b) within group (order by b)
          *
ERROR at line 6:
ORA-30482: DISTINCT option not allowed for this function


a. why do i get this ORA-30482 ?
b. the same bug as mentioned in http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15637744429336#1988197700346083006 ?
c. if it is a bug, do you have an example in which ORA-30482 is justified ?

Tom Kyte
August 14, 2011 - 7:50 pm UTC

a) because DISTINCT is not supported with listagg, just like it isn't with other aggregate/analytics:

ops$tkyte%ORA11GR2> select percentile_cont( distinct sal ) within group (ordre by empno ) from scott.emp;
select percentile_cont( distinct sal ) within group (ordre by empno ) from scott.emp
       *
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function



b) it is not the same bug or even similar. That bug was showing that a valid sql statement wasn't being accepted by the plsql compiler.

You are seeing with listagg that distinct is just not supported.


thanks

Sokrates, August 15, 2011 - 1:25 am UTC


Mikito's challenge

James Su, September 15, 2011 - 1:38 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402#11950282718015

create table zz_comm (rn NUMBER,ename varchar2(15),comm number);

insert into zz_comm values ( 1,'ADAMS',0);
insert into zz_comm values ( 2,'ALLEN',300);
insert into zz_comm values ( 3,'BLAKE',0);
insert into zz_comm values ( 4,'CLARK',0);
insert into zz_comm values ( 5,'FORD',0);
insert into zz_comm values ( 6,'JAMES',0);
insert into zz_comm values ( 7,'JONES',0);
insert into zz_comm values ( 8,'KING',0);
insert into zz_comm values ( 9,'MARTIN',1400);
insert into zz_comm values (10,'MILLER',0);
insert into zz_comm values (11,'SCOTT',0);
insert into zz_comm values (12,'SMITH',0);
insert into zz_comm values (13,'TURNER',0);
insert into zz_comm values (14,'WARD',500);

commit;

Here's a solution with "group by" that he likes and analytic functions that he doesn't like:

SELECT MIN(rn) begin
,MAX(rn) end
,comm
FROM (SELECT z.*
,ROW_NUMBER() OVER(ORDER BY rn) rn1
,ROW_NUMBER() OVER(PARTITION BY comm ORDER BY rn) rn2
FROM zz_comm z
)
GROUP BY comm,rn1-rn2
ORDER BY 1;

BEGIN END COMM
---------- ---------- ----------
1 1 0
2 2 300
3 8 0
9 9 1400
10 13 0
14 14 500

6 rows selected.

Distinct not working in PL/SQL for the stragg() function

Chan, May 14, 2012 - 10:36 am UTC

Hi Tom,

When using stragg() function with distinct in sql,for example stragg(distinct colname), it's working fine. However it's not working in PL/SQL.

Thanks,

-Chan
Tom Kyte
May 14, 2012 - 12:50 pm UTC

I filed a bug for that a while ago. You need to use dynamic sql for that reason in 9.2 and before, it was fixed in 10.1, backports for many released prior to that.


MOSNOTE(PUBLISHED) - 28640

Chan, May 14, 2012 - 1:47 pm UTC

I searched in My Oracle Support for document ID 28640 but didn't find it. My Oracle version is 11.1, however I'm still getting the error.

Thanks,
Tom Kyte
May 14, 2012 - 11:44 pm UTC

please post a test case/example to look at.

stragg not working in 11.2.0.3 with Distinct + Analytic Functions

Pasko, August 14, 2012 - 8:57 am UTC

Hello Tom,

We had a similar Function to your stragg, which used to work perfectly fine until 11.2.0.1, but after an Upgrade to 11.2.0.3 the Function produces Garbage when used in Combination with Distinct and an Analytic Function:

Example:

SQL> @check_asktom_stringagg.sql

Type created.


Type body created.


Function created.

SQL> select stragg( distinct val)  over (partition by rn)  str_udm
  2     from (
  3        select '1111' val , rownum rn from dual
  4          union all
  5        select '1111' val,  rownum rn from dual
  6          union all
  7        select '2222' val,  rownum rn from dual
  8          union all
  9       select '2222' val,  rownum rn from dual
 10          union all
 11      select '3333' val,  rownum rn from dual
 12  );

STR_UDM
--------------------------------------------------------------------------------
´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺
´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺
´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺
´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺
´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺ ,´┐¢ ☺☺☺☺

Regards,


It works without an analytic function:

SQL> select stragg( distinct val) str_udm
  2     from (
  3        select '1111' val , rownum rn from dual
  4          union all
  5        select '1111' val,  rownum rn from dual
  6          union all
  7        select '2222' val,  rownum rn from dual
  8          union all
  9       select '2222' val,  rownum rn from dual
 10          union all
 11      select '3333' val,  rownum rn from dual
 12  );

STR_UDM
--------------------------------------------------------------------------------
1111,2222,3333


Pasko

Tom Kyte
August 17, 2012 - 2:13 pm UTC

please contact support, I cannot reproduce your issue on my 11.2.0.3 database.

to reproduce error

Alex, November 14, 2012 - 8:28 am UTC

Hi, Tom
To reproduce error, try set nls_language other than AMERICAN

alter session set nls_language='RUSSIAN';
select sys.stragg(distinct 1) over () from dual;

The answer will be "c", not "1"
Tom Kyte
November 14, 2012 - 8:29 pm UTC

first - and this is very important - DO NOT USE SYS FOR ANYTHING EVER. do not install code into sys, stop - never ever do that. never. no excuses.

sys is special
sys is different
sys doesn't follow all of the rules
sys is "Oracles" do not use it.
period



second - do you have access to support? is so, please file a bug, if not, let me know and I will

This is a bug.

Alex, November 15, 2012 - 1:02 am UTC

Support said that this is a bug.

Bug 13396671 - wrong results with NLS and analytic Function [ID 13396671.8]


multibyte charcters

A reader, November 27, 2012 - 7:56 am UTC

Can you please help me is there are nay way we can cleanse this calue while loading on the table. These all are multibyte ascii charcters coming from DB2.

select REPLACE ('46616826Ô`Tÿÿç', chr(13), ' ') from dual

the desired result is 466168260. We cannot use subtr because the length may vary .

Thanks
Tom Kyte
November 29, 2012 - 7:54 am UTC

why is the desired output 466168260??? I don't even see a zero in there.


conitnuation String Concatenation", version 8.1.6

sandeep, December 13, 2012 - 2:17 am UTC

Hi,

I am using Oracle 10G and i tried all the options for custom string aggregation mentioned.
The one Tom mentioned with Custom Delimiter is still giving me a comma delimited string.

Please let me know what might be wrong and if i can implement in anyother way.
Tom Kyte
December 17, 2012 - 3:45 pm UTC

did you read the entire page?

it was inferred you would make that change. sorry, could have made that more clear. I demostrated
how to get the global variable set.



look for that, this has been discussed. I left a tiny bit of work for you to do.

AVG of an String

sunil, September 13, 2013 - 6:44 am UTC

Below Code is doing SUM of each number in a string.

For e.g.
Row 1 1_2_3_4_5
Row 2 2_3_4_5_3
Output with SUM 3_5_7_9_8

My Next goal I want to do the AVG for ROW1 & ROW2. Please help me in changing the below code so

CREATE OR REPLACE FUNCTION AC_SUM( x VARCHAR2 ) return VARCHAR2 PARALLEL_ENABLE AGGREGATE USING ARRAYCOUNTERSUM;
/


CREATE OR REPLACE TYPE ArrayCounterSum AS OBJECT
(
gCount NUMBER,
gNumArrayCounter arraycounter_T,
-- MEMBER FUNCTIONS AND PROCEDURES
STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT ArrayCounterSum) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ArrayCounterSum,
AC_Array IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ArrayCounterSum,
ctx2 IN ArrayCounterSum) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN ArrayCounterSum,
ReturnValue OUT VARCHAR2,
flags IN number) RETURN NUMBER
);


TYPE BODY ArrayCounterSum AS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(ACTX IN OUT ARRAYCOUNTERSUM) RETURN NUMBER IS
<span class="ln"> 27 </span> BEGIN
<span class="ln"> 28 </span>
<span class="ln"> 29 </span>
<span class="ln"> 30 </span>
<span class="ln"> 31 </span>
<span class="ln"> 32 </span> ACTX := ARRAYCOUNTERSUM(-1,
<span class="ln"> 33 </span> ARRAYCOUNTER_T(
<span class="ln"> 34 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 35 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 36 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 37 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 38 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 39 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 40 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 41 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 42 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 43 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 44 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 45 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 46 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 47 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 48 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 49 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 50 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 51 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 52 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 53 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 54 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 55 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 56 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 57 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 58 </span> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 59 </span> NULL,NULL,NULL,NULL,NULL,NULL,
<span class="ln"> 60 </span> NULL)
<span class="ln"> 61 </span> );
<span class="ln"> 62 </span>
<span class="ln"> 63 </span>
<span class="ln"> 64 </span> RETURN ODCICONST.SUCCESS;
<span class="ln"> 65 </span>
<span class="ln"> 66 </span> EXCEPTION
<span class="ln"> 67 </span> WHEN OTHERS THEN
<span class="ln"> 68 </span> RETURN ODCICONST.ERROR;
<span class="ln"> 69 </span> END;
<span class="ln"> 70 </span>
<span class="ln"> 71 </span>
<span class="ln"> 72 </span>
<span class="ln"> 73 </span>
<span class="ln"> 74 </span>
<span class="ln"> 75 </span>
<span class="ln"> 76 </span>
<span class="ln"> 77 </span> MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ARRAYCOUNTERSUM,
<span class="ln"> 78 </span> AC_ARRAY IN VARCHAR2) RETURN NUMBER IS
<span class="ln"> 79 </span> GOTELEMENT BOOLEAN;
<span class="ln"> 80 </span> PSEP VARCHAR2(1) := '_';
<span class="ln"> 81 </span> VINDEX PLS_INTEGER;
<span class="ln"> 82 </span> STARTPOS PLS_INTEGER;
<span class="ln"> 83 </span> ENDPOS PLS_INTEGER;
<span class="ln"> 84 </span> CURPOS PLS_INTEGER;
<span class="ln"> 85 </span> LENARRAY PLS_INTEGER;
<span class="ln"> 86 </span> VELEMENT NUMBER;
<span class="ln"> 87 </span> BEGIN
<span class="ln"> 88 </span>
<span class="ln"> 89 </span>
<span class="ln"> 90 </span> IF AC_ARRAY IS NULL OR LENGTH(AC_ARRAY) = 0 THEN
<span class="ln"> 91 </span> RETURN ODCICONST.SUCCESS;
<span class="ln"> 92 </span> END IF;
<span class="ln"> 93 </span>
<span class="ln"> 94 </span>
<span class="ln"> 95 </span> GOTELEMENT := FALSE;
<span class="ln"> 96 </span> VINDEX := 0;
<span class="ln"> 97 </span>
<span class="ln"> 98 </span> LENARRAY := LENGTH(AC_ARRAY);
<span class="ln"> 99 </span> STARTPOS := 1;
<span class="ln"> 100 </span> ENDPOS := 1;
<span class="ln"> 101 </span>
<span class="ln"> 102 </span> FOR I IN 1 .. LENARRAY
<span class="ln"> 103 </span> LOOP
<span class="ln"> 104 </span>
<span class="ln"> 105 </span> CURPOS := I;
<span class="ln"> 106 </span>
<span class="ln"> 107 </span> IF SUBSTR(AC_ARRAY, I, 1) = PSEP THEN
<span class="ln"> 108 </span>
<span class="ln"> 109 </span> GOTELEMENT := TRUE;
<span class="ln"> 110 </span> VINDEX := VINDEX + 1;
<span class="ln"> 111 </span>
<span class="ln"> 112 </span>
<span class="ln"> 113 </span> ENDPOS := I - 1;
<span class="ln"> 114 </span>
<span class="ln"> 115 </span>
<span class="ln"> 116 </span> IF ENDPOS &lt; STARTPOS THEN
<span class="ln"> 117 </span> STARTPOS := I + 1;
<span class="ln"> 118 </span> END IF;
<span class="ln"> 119 </span>
<span class="ln"> 120 </span> END IF;
<span class="ln"> 121 </span>
<span class="ln"> 122 </span>
<span class="ln"> 123 </span> IF GOTELEMENT THEN
<span class="ln"> 124 </span> VELEMENT := TO_NUMBER(SUBSTR(AC_ARRAY, STARTPOS, ENDPOS - STARTPOS + 1));
<span class="ln"> 125 </span> SELF.GNUMARRAYCOUNTER(VINDEX) := NVL(NVL(SELF.GNUMARRAYCOUNTER(VINDEX)+VELEMENT,SELF.GNUMARRAYCOUNTER(VINDEX)), VELEMENT);
<span class="ln"> 126 </span> GOTELEMENT := FALSE;
<span class="ln"> 127 </span>
<span class="ln"> 128 </span> STARTPOS := I + 1;
<span class="ln"> 129 </span>
<span class="ln"> 130 </span> END IF;
<span class="ln"> 131 </span>
<span class="ln"> 132 </span> END LOOP;
<span class="ln"> 133 </span>
<span class="ln"> 134 </span>
<span class="ln"> 135 </span> IF CURPOS = LENARRAY THEN
<span class="ln"> 136 </span> VINDEX := VINDEX + 1;
<span class="ln"> 137 </span>
<span class="ln"> 138 </span> IF SUBSTR(AC_ARRAY, LENARRAY, 1) = PSEP THEN
<span class="ln"> 139 </span> SELF.GNUMARRAYCOUNTER(VINDEX) := NULL;
<span class="ln"> 140 </span>
<span class="ln"> 141 </span>
<span class="ln"> 142 </span> ELSE
<span class="ln"> 143 </span> VELEMENT := TO_NUMBER(SUBSTR(AC_ARRAY, STARTPOS, LENARRAY - STARTPOS + 1));
<span class="ln"> 144 </span> SELF.GNUMARRAYCOUNTER(VINDEX) := NVL(NVL(SELF.GNUMARRAYCOUNTER(VINDEX)+VELEMENT,SELF.GNUMARRAYCOUNTER(VINDEX)), VELEMENT);
<span class="ln"> 145 </span>
<span class="ln"> 146 </span> END IF;
<span class="ln"> 147 </span>
<span class="ln"> 148 </span> END IF;
<span class="ln"> 149 </span>
<span class="ln"> 150 </span>
<span class="ln"> 151 </span>
<span class="ln"> 152 </span> IF SELF.GCOUNT &lt; VINDEX THEN
<span class="ln"> 153 </span> SELF.GCOUNT := VINDEX;
<span class="ln"> 154 </span> END IF;
<span class="ln"> 155 </span>
<span class="ln"> 156 </span> RETURN ODCICONST.SUCCESS;
<span class="ln"> 157 </span>
<span class="ln"> 158 </span> EXCEPTION
<span class="ln"> 159 </span> WHEN OTHERS THEN
<span class="ln"> 160 </span> RETURN ODCICONST.ERROR;
<span class="ln"> 161 </span>
<span class="ln"> 162 </span> END;
<span class="ln"> 163 </span>
<span class="ln"> 164 </span>
<span class="ln"> 165 </span>
<span class="ln"> 166 </span>
<span class="ln"> 167 </span>
<span class="ln"> 168 </span>
<span class="ln"> 169 </span> MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ARRAYCOUNTERSUM,
<span class="ln"> 170 </span> CTX2 IN ARRAYCOUNTERSUM) RETURN NUMBER IS
<span class="ln"> 171 </span> BEGIN
<span class="ln"> 172 </span>
<span class="ln"> 173 </span>
<span class="ln"> 174 </span> FOR I IN 1 .. SELF.GCOUNT
<span class="ln"> 175 </span> LOOP
<span class="ln"> 176 </span> SELF.GNUMARRAYCOUNTER(I) := SELF.GNUMARRAYCOUNTER(I) + CTX2.GNUMARRAYCOUNTER(I);
<span class="ln"> 177 </span> END LOOP;
<span class="ln"> 178 </span>
<span class="ln"> 179 </span> RETURN ODCICONST.SUCCESS;
<span class="ln"> 180 </span>
<span class="ln"> 181 </span> EXCEPTION
<span class="ln"> 182 </span> WHEN OTHERS THEN
<span class="ln"> 183 </span> RETURN ODCICONST.ERROR;
<span class="ln"> 184 </span>
<span class="ln"> 185 </span> END;
<span class="ln"> 186 </span>
<span class="ln"> 187 </span>
<span class="ln"> 188 </span>
<span class="ln"> 189 </span>
<span class="ln"> 190 </span>
<span class="ln"> 191 </span>
<span class="ln"> 192 </span> MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ARRAYCOUNTERSUM,
<span class="ln"> 193 </span> RETURNVALUE OUT VARCHAR2,
<span class="ln"> 194 </span> FLAGS IN NUMBER) RETURN NUMBER IS
<span class="ln"> 195 </span> VAC_ARRAY VARCHAR2(4000);
<span class="ln"> 196 </span> BEGIN
<span class="ln"> 197 </span>
<span class="ln"> 198 </span>
<span class="ln"> 199 </span> IF SELF.GCOUNT &lt; 1 THEN
<span class="ln"> 200 </span> RETURNVALUE := NULL;
<span class="ln"> 201 </span> ELSE
<span class="ln"> 202 </span>
<span class="ln"> 203 </span> VAC_ARRAY := TO_CHAR(SELF.GNUMARRAYCOUNTER(1));
<span class="ln"> 204 </span> FOR I IN 2 .. SELF.GCOUNT
<span class="ln"> 205 </span> LOOP
<span class="ln"> 206 </span> VAC_ARRAY := VAC_ARRAY || '_' || TO_CHAR(SELF.GNUMARRAYCOUNTER(I));
<span class="ln"> 207 </span> END LOOP;
<span class="ln"> 208 </span> RETURNVALUE := VAC_ARRAY;
<span class="ln"> 209 </span> END IF;
<span class="ln"> 210 </span>
<span class="ln"> 211 </span> RETURN ODCICONST.SUCCESS;
<span class="ln"> 212 </span>
<span class="ln"> 213 </span> EXCEPTION
<span class="ln"> 214 </span> WHEN OTHERS THEN
<span class="ln"> 215 </span> RETURN ODCICONST.ERROR;
<span class="ln"> 216 </span>
<span class="ln"> 217 </span> END;
<span class="ln"> 218 </span>
<span class="ln"> 219 </span>END;


Error when using sys_connect_by_path for String Concatenation

Talal, March 10, 2014 - 8:20 am UTC

Hi ,
why the following statement returned error ,
why cannot separated column values by chr(0) :

select level , sys_connect_by_path(level , chr(0))
from dual
connect by level <=10 ;
Thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library