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 ...

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?

0.

CREATE TYPE IntSet AS TABLE OF Integer;
/

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>  

 

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?

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.



 

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.


 

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

Thanks.

"
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...:)

well, there is gvim 

try

SQL> define editor="gvim -f"

 

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

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

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

 

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,

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

Regards,
Arun

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

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

?

>>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

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,
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

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?

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
 

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?

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;

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.

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?

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.

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

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 

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

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 - 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

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

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

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?

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

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

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>  

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;
/

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

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

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

"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?

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]

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

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

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 

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

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

"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?

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) 

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

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.

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?

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.

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 !  

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.

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

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.

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> 

 

the page ist very good!!!

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

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

To be more specific, which?

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??

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 ?

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
 
 

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

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

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

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 

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.

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"?

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 |
---------------------------------------------------------------------------

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 | |
-------------------------------------------------------------------------------------------------------------------------

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

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 ;

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.

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.

=====================
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.

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.

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. 

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().

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.

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.

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.

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.

 

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!

-- 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

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.

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

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.

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

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

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
 

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

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 

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?

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...



 

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

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.

 

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

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 

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

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

 

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'

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?

Thanks! I saw stragg, but obviously I overlooked something. Have a great evening.

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

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
.
.
.

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.

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;

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.

Thank you to both of you!

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>

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

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.

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.

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

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.

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> 


   

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

http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTION_ID:2196162600402#25879699852042
http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTIOID:2196162600402#7971282174613

http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTION_ID:2196162600402#25879699852042
http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTION_ID:2196162600402#7971282174613

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

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

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

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,

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!

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

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,

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,

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

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

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

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 ?

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.

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

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.

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.

...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?

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 ?

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

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,

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,

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'

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

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;
----------

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

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

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.

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

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.

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?

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.

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.

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.

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 lot. High level of mistake took place "U" will be careful in future.

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

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

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

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
,,

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

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)

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:

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

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

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.

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.