Wowsers ...
December 11, 2001 - 11pm Central time zone
Reviewer: Mike Wilson from Los Angeles, CA
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 ...
December 12, 2001 - 1am Central time zone
Reviewer: A reader
And in case if you want to pivot it back...
December 13, 2001 - 12pm Central time zone
Reviewer: Mikito harakiri
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?
Followup December 13, 2001 - 2pm Central time zone:
I cut and paste all of my examples from SQLPlus. It cuts way down on typos. I manually reuse my
own code in here by using an editor...
I'll not be changing the format -- its too nice having the code/response/code/response. Just
posting the script and then the response is a hassle and not as nice.
I use vi, I cut and paste the examples into vi and then a simple:
:1,$s/.*COM>/xxxx/
:1,$s/.....//
gets rid of all of the line numbers and sqlplus prompts. I just have to delete " created", "s
inserted." and so on after that...
missed definition
December 13, 2001 - 12pm Central time zone
Reviewer: Mikito harakiri
0.
CREATE TYPE IntSet AS TABLE OF Integer;
/
Another Oracle 8i workaround
June 17, 2002 - 6pm Central time zone
Reviewer: msc from Estonia
Here is another 8i workaround which is of course not perfect :-) but suitable for certain cases.
1. create package which collects strings into pl/sql table for each department.
create or replace package stragg as
function result(i in integer) return varchar2;
function collect(i in integer, s in varchar2) return integer;
end;
/
create or replace package body stragg as
type TStringTable is table of long index by binary_integer;
tab TStringTable;
iNullIndex binary_integer := -1;
bCanEmpty boolean := False;
function result(i in integer) return varchar2 is
itmp binary_integer := nvl(i,iNullIndex);
begin
bCanEmpty := True;
if tab.exists(itmp) then
return tab(itmp);
else
return NULL;
end if;
end;
function collect(i in integer, s in varchar2) return integer is
itmp binary_integer := nvl(i,iNullIndex);
stmp long := substr(s,1,4000);
begin
if bCanEmpty then -- after result(..) we can clean up here
tab.delete;
bCanEmpty := False;
end if;
if tab.exists(itmp) then
tab(itmp) := substr( tab(itmp)|| '.' || s, 1, 4000);
else
tab(itmp) := stmp;
end if;
return 1;
end;
end;
/
2. use in SQL:
SQL> col names for a50
SQL> select deptno, stragg.result(deptno) as NAMES
2 from emp
3 where stragg.collect( deptno, ename) = 1
4 group by deptno
5 /
DEPTNO NAMES
---------- --------------------------------------------------
10 CLARK.KING.MILLER
20 SMITH.JONES.SCOTT.ADAMS.FORD
30 ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES
SQL>
----
This solutions has one (afaik) pitfall -- one must ensure that stragg.collect is executed.
Following query does *not* work as expected because Oracle optimizes collect() function out:
SQL> select deptno, stragg.result(deptno) as NAMES
2 from (select deptno, stragg.collect( deptno, ename) as COL from EMP)
3 group by deptno;
DEPTNO NAMES
---------- --------------------------------------------------
10
20
30
SQL>
(btw if this query happens to show right results then they are from *previous* execution)
This subquey can be tricked to work with where clause:
SQL> select deptno, stragg.result(deptno) as NAMES
2 from (select deptno, stragg.collect( deptno, ename) as COL from EMP)
3 where COL = 1
4 group by deptno;
DEPTNO NAMES
---------- --------------------------------------------------
10 CLARK.KING.MILLER
20 SMITH.JONES.SCOTT.ADAMS.FORD
30 ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES
SQL>
Can you use a dynamic separator with the 9i solution?
September 30, 2002 - 8am Central time zone
Reviewer: Daryl from Texas
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?
Followup October 1, 2002 - 9am Central time zone:
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),
delim varchar2(1),
.....
create or replace type body string_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
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 );
return ODCIConst.Success;
end;
....
and then:
ops$tkyte@ORA9I.WORLD> column enames format a25
ops$tkyte@ORA9I.WORLD> select deptno, stragg(ename) enames
2 from emp
3 group by deptno
4 /
DEPTNO ENAMES
---------- -------------------------
10 CLARK|KING|MILLER
20 SMITH|FORD|ADAMS|SCOTT|JO
NES
30 ALLEN|BLAKE|MARTIN|TURNER
|JAMES|WARD
3 rows selected.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> exec dbms_application_info.set_client_info( 'delim=|' )
PL/SQL procedure successfully completed.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> select deptno, stragg(ename) enames
2 from emp
3 group by deptno
4 /
DEPTNO ENAMES
---------- -------------------------
10 CLARK|KING|MILLER
20 SMITH|FORD|ADAMS|SCOTT|JO
NES
30 ALLEN|BLAKE|MARTIN|TURNER
|JAMES|WARD
3 rows selected.
Exactly what I was looking for
January 7, 2003 - 11am Central time zone
Reviewer: John
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.
Never Mind
January 7, 2003 - 12pm Central time zone
Reviewer: John
I read you solution wrong. I don't 9i so I will have to find another way.
Thanks.
Linux vi
January 18, 2003 - 7am Central time zone
Reviewer: Jerry from Washington, DC USA
"
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...:)
Followup January 18, 2003 - 9am Central time zone:
well, there is gvim
try
SQL> define editor="gvim -f"
Need an order by statement...
February 5, 2003 - 9am Central time zone
Reviewer: Damian Smith from UK
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
Followup February 5, 2003 - 12pm Central time zone:
Just write a custom function instead of an aggregate. Aggregates are "SET" things and "sets"
really don't have any concept of order.
select deptno, f(deptno) from dept;
where f(deptno) runs your query against EMP with an order by and does what you want....
Using only SQL (9i only though : ( )
February 21, 2003 - 10am Central time zone
Reviewer: Alan Patil from Reading, UK
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
Followup February 21, 2003 - 3pm Central time zone:
...
The only issue I can think of is what happens if the list is longer than the
varchar2 limit of 4000 bytes.
.....
use a clob then -- it works, i've done that.
don't understand the spurious reference to sys_connect_by_path??? stragg seems a bit easier?
A straight sql solution
February 21, 2003 - 5pm Central time zone
Reviewer: Jack Mulhollan from Little Rock, AR USA
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
Followup February 21, 2003 - 6pm Central time zone:
Yes, that works as well -- very similar to the example I have in expert one on one Oracle to
demonstrate pivots.
but it is funny -- once you install stragg -- it is "straight sql" as well.
analytic sql solutions are disgusting
February 21, 2003 - 8pm Central time zone
Reviewer: Mikito Harakiri
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>
Followup February 22, 2003 - 10am Central time zone:
You are amusing sometimes with your analysis and comments you know ;)
Analytic functions ROCK AND ROLL -- coolest thing to happen to SQL since the keyword select. I
find AT LEAST 2 or 3 answers a day involve (gasp) analytics.
This will not be the first time I take one of your SQL only solutions and show has dastardly slow,
hard to read, unfathomable in the real world they actually are.
I find queries that are
o really fast
o readable
o easy to maintain, develop
to be slightly less digusting then queries that
o are not any of the above ;)
Now, I cannot see how ANY index could be used with your psuedo procedural approach (it is so
procedural as to be scary -- looks almost like a SAS program except it is using SELECT instead of
proc print; to show the end result).
You sort emp by deptno (all of the rows).
You then assign a rownumber to those ordered rows...
you then find the mininum and maximum rownums by deptno and then procede to query...
So, lets benchmark shall we? Well use all three approaches. Then, you tell us which is
"disgusting"
So, I'll start with:
drop table emp;
create table emp
as
select object_id empno, substr(object_name,1,10) ename, object_type job, data_object_id mgr,
created hiredate, object_id sal, object_id comm, mod(rownum,500) deptno
from all_objects
/
Just to generate some data. TKPROF says:
select deptno,
max(decode(my_seq, 1,ename)) || ' ' ||
max(decode(my_seq, 2,ename)) || ' ' ||
max(decode(my_seq, 3,ename)) || ' ' ||
...... 4..67 snipped
max(decode(my_seq,68,ename)) || ' ' ||
max(decode(my_seq,69,ename)) emp_list
from (
select deptno,
ename,
row_number() over
(partition by deptno order by ename) my_seq
from emp
)
group by deptno
order by 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 35 1.12 1.13 0 237 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37 1.13 1.14 0 237 0 500
Rows Row Source Operation
------- ---------------------------------------------------
500 SORT GROUP BY
32498 VIEW
32498 WINDOW SORT
32498 TABLE ACCESS FULL OBJ#(42644)
so, analytics, while limited in this case as you have to sort of know the MAX number of elements,
perform quite outstanding here -- don't they (especially as we move on)
********************************************************************************
select deptno, stragg(ename) from emp group by deptno order by deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 35 4.70 4.93 0 240 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37 4.70 4.94 0 240 0 500
Rows Row Source Operation
------- ---------------------------------------------------
500 SORT GROUP BY
32498 TABLE ACCESS FULL EMP
by far the simpliest solution but a tad more cpu intensive on this set... but wait:
with ordemp as (select * from emp order by deptno),
seqemp as (select rownum rn, ordemp.* from ordemp),
startfinish as (select min(rn) low, max(rn) high, deptno from seqemp ee group by deptno),
paths as (
select sys_connect_by_path(ename,',') concat, deptno, rn
from seqemp o
connect by rn = prior rn + 1 and deptno = prior deptno
start with rn in (select low from startfinish s where s.deptno = o.deptno))
select concat, deptno
from paths p
where rn in (select high from startfinish s where s.deptno = p.deptno)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 6 0
Fetch 35 13.97 15.69 2 259435 1 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37 13.97 15.70 2 259435 7 500
Rows Row Source Operation
------- ---------------------------------------------------
500
500 FILTER
32498 VIEW
32498 CONNECT BY WITHOUT FILTERING
32498 COUNT
32498 VIEW
32498 TABLE ACCESS FULL SYS_TEMP_4254950912
500 VIEW
16124250 TABLE ACCESS FULL SYS_TEMP_4254950913
500 VIEW
16124250 TABLE ACCESS FULL SYS_TEMP_4254950913
that is a query I wouldn't like to see on my system -- slightly too many IO's for my taste and it
really chomps up the CPU doesn't it?
So, here is what I would say if you need to do this:
o have a known finite number of elements? row_number() is looking pretty good
o have an unknown or just looking for the easiest query and you find the result sets you are going
against perform more then well enought? stragg
o your psuedo procedural approach? I don't think so.....
But -- you are free to keep ignoring the existence of language features that can greatly increase
the performance of your system all you want....
April 7, 2003 - 10am Central time zone
Reviewer: Arun Mathur from Marietta,GA
Tom,
This is a great site. Would you mind explaining how the PARALLEL_ENABLE option works?
Regards,
Arun
sys_connect_by_path
July 30, 2003 - 4pm Central time zone
Reviewer: A reader
Tom Can you please explain the post by Alan Patil where he uses sys_connect_by_path ?
Followup July 30, 2003 - 4pm Central time zone:
which part needs explaining? sys_connect_by_path is documented in the 9i doc set if that is it?
analytical sql is not intuitive
July 31, 2003 - 8pm Central time zone
Reviewer: mikito harakiri
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
?
Followup July 31, 2003 - 9pm Central time zone:
because you added "order by"
the order by makes things "the same". you order by deptno -- well, isn't everything with the same
deptno the same? (rhetorical question -- YES)
when you order by empno -- they are NOT the same (empno happens to be unique) so the sum is a pure
cumulative total.
using sum(1) is "not intuitive" -- i believe you truly meant to use the correct and proper
"count(*)" syntax. here is an example:
scott@ORA920LAP> select deptno, ename,
2 count(*) over (partition by deptno order by deptno) cnt1,
3 count(*) over (partition by deptno order by deptno, ROWID) cnt2,
4 count(*) over (partition by deptno order by empno) cnt3,
5 count(*) over (partition by deptno order by job) cnt4
6 from emp
7 /
DEPTNO ENAME CNT1 CNT2 CNT3 CNT4
---------- ---------- ---------- ---------- ---------- ----------
10 CLARK 3 1 1 2
10 KING 3 2 2 3
10 MILLER 3 3 3 1
see with order by deptno -- cnt1 -- all of the rows are "the same". with order by deptno, rowid
-- they are "all different" -- rowid makes them different.
order by empno - same thing since empno just happens to be unique.
order by job, boring in this case -- job was "unique"
20 SMITH 5 1 1 4
20 JONES 5 2 2 5
20 SCOTT 5 3 3 2
20 ADAMS 5 4 4 4
20 FORD 5 5 5 2
oh, now cnt 4, order by job, is interesting. apparently ford and scott have the same job, adams
and smith have the same job, and jones is unique. since the jobs were the same -- the rows in the
window defined by the order by are the SAME
30 ALLEN 6 1 1 6
30 WARD 6 2 2 6
30 MARTIN 6 3 3 6
30 BLAKE 6 4 4 2
30 TURNER 6 5 5 6
30 JAMES 6 6 6 1
14 rows selected.
sorry you don't find it "intuitive" -- but it is the way it has to work.
FUNCTION stragg in package
August 14, 2003 - 3pm Central time zone
Reviewer: fan from PA
>>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
Followup August 14, 2003 - 3pm Central time zone:
don't believe you can, it is just a 'definition', no code really.
are analytical queries powerful?
August 22, 2003 - 8pm Central time zone
Reviewer: Mikito Harakiri
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:-)
Followup August 23, 2003 - 10am Central time zone:
"clearly" "clearly what???? continous ranges of what? I've no idea what your basic question is --
why should the output be that? what is the question you are trying to answer -- and more
importantly where is the subquery query you thing is "better"
i give up. Mikito -- do what you will. Like what you like.
I'll write fast queries that perform and scale well. I'll be using analytics out the whazoo to do
it. I'll drop down to relatively inefficient subqueries (in general, not always) when I have to.
how bout ODCITABLE ?
November 19, 2003 - 10pm Central time zone
Reviewer: ant from ny
Tom,
Stragg, using the ODCI, is very cool. I came across the ODCITABLE interface and thought it was
interesting.
I noticed that stragg outperforms a "regular" plsql function that does pretty much the same
operation
and was wondering if a function using ODCITABLE interface would outperform a pipelined function.
I did a search for ODCITABLEFETCH on metalink and it returned no results. I got all of 4 hits ( all
to the oracle doc ) from google. Can a function using ODCITABLE be used to "stream" result sets
like pipelined functions ?
The ODCI doc doesn't have a complete example using just pl/sql and the ODCI interface ( the
examples use OCI and JAVA ).
Can you provide an example that uses the ODCITABLE interface and pl/sql ( no OCI or JAVA calls ) ?
thanks,
ant
An exception with sys views?
November 20, 2003 - 1am Central time zone
Reviewer: Alex Taylor from Canberra, Australia
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?
Followup November 21, 2003 - 3pm Central time zone:
don't know what you mean by "only the last sql_text value of 6 values"
give us the "example", show the unaggregated data and the aggregated data and point out what is
"wrong"
Analytic Functions are VERY powerful - get used to it.
November 20, 2003 - 8pm Central time zone
Reviewer: Glen Morris from Canberra, Australia
I tried out the 'challenge' from Mikito above and used Analytic Functions. This may not be the most
elegant method but since the requirement wasn't stated clearly I'll forgive myself. I've assumed
the rown wasn't actually in the source table. If it turns out to be then the query is simpler and
just need to replace all occurences of 'row_number() over (order by ename)' with 'rown'.
SQL> create table zz_comm
2 (ename varchar2(15),comm number);
Table created.
SQL>
SQL> insert into zz_comm values ('ADAMS',0);
1 row created.
SQL> insert into zz_comm values ('ALLEN',300);
1 row created.
SQL> insert into zz_comm values ('BLAKE',0);
1 row created.
SQL> insert into zz_comm values ('CLARK',0);
1 row created.
SQL> insert into zz_comm values ('FORD',0);
1 row created.
SQL> insert into zz_comm values ('JAMES',0);
1 row created.
SQL> insert into zz_comm values ('JONES',0);
1 row created.
SQL> insert into zz_comm values ('KING',0);
1 row created.
SQL> insert into zz_comm values ('MARTIN',1400);
1 row created.
SQL> insert into zz_comm values ('MILLER',0);
1 row created.
SQL> insert into zz_comm values ('SCOTT',0);
1 row created.
SQL> insert into zz_comm values ('SMITH',0);
1 row created.
SQL> insert into zz_comm values ('TURNER',0);
1 row created.
SQL> insert into zz_comm values ('WARD',500);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select start_range
2 ,nvl(lead(start_range,1) over (order by rown) -1,start_range) end_range
3 ,comm
4 from (select row_number() over (order by ename) rown
5 ,comm
6 ,case when comm <> nvl(lag(comm,1) over (order by ename),-1)
7 then row_number() over (order by ename) else null end start_range
8 from zz_comm
9 )
10 where start_range is not null;
START_RANGE END_RANGE COMM
----------- ---------- ----------
1 1 0
2 2 300
3 8 0
9 9 1400
10 13 0
14 14 500
6 rows selected.
SQL> spool off
Awsome STRAGG, how to explicitly order the result?
January 9, 2004 - 4pm Central time zone
Reviewer: Antonio from Houston, TX
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?
Followup January 10, 2004 - 10am Central time zone:
1) you cannot -- that is one of the "issues" with aggregates. they work on unordered sets. Most
likely the reason there is no such thing as "stragg" in the "real database" since the results are
somewhat indepterminate as far as order goes.
ROW_NUMBER() is deterministic however since you assign the numbers after ordering.
2) the data cartridge developers guide and application developers guide, both available on
otn.oracle.com
Handy function for sorting elements in a string
January 12, 2004 - 1am Central time zone
Reviewer: Gary from Sydney, Aus
In a pinch, the following may be useful. It splits a string by a delimiter, stores the elements in
an associative array, then re-extracts them in a sorted order, described in the PL/SQL User's Guide
as :
"For associative arrays with VARCHAR2 keys, these methods (NEXT and PRIOR) return the appropriate
key value; ordering is based on the binary values of the characters in the string, unless the
NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the
locale-specific sort order specified by the NLS_SORT initialization parameter."
Since Associative arrays are a 9i feature, this cannot be ported to 8i or earlier (unless you
implement your own sort code)
create or replace function sort_string
(p_string in varchar2, p_delim in varchar2,
p_dup in varchar2 default 'I') return varchar2 is
type t_sort_tab is table of number(4) index by varchar2(4000);
t_sort t_sort_tab;
v_start number := 1;
v_end number := 0;
v_ext varchar2(4000);
v_out varchar2(4000);
begin
while v_end < length(p_string) loop
v_end := instr(p_string,p_delim,v_start);
if v_end = 0 then
v_end := length(p_string)+1;
end if;
v_ext := substr(p_string,v_start,v_end-v_start);
if t_sort.exists(v_ext) THEN
t_sort(v_ext) := t_sort(v_ext) + 1;
else
t_sort(v_ext) := 1;
end if;
v_start := v_end + length(p_delim);
end loop;
v_ext := t_sort.first;
WHILE v_ext is not null loop
IF p_dup = 'I' THEN
-- Option A - Ignore duplicates (treat as single occurrence)
v_out := v_out||v_ext||p_delim;
ELSIF p_dup = 'N' THEN
-- Option B - Add number of entries after value in brackates
v_end := t_sort(v_ext);
v_out := v_out||v_ext||'('||to_char(v_end)||')'||p_delim;
ELSIF p_dup = 'D' THEN
-- Option C - Repeat duplicated entries
v_end := t_sort(v_ext);
FOR i in 1..v_end loop
v_out := v_out||v_ext||p_delim;
END LOOP;
END IF;
v_ext := t_sort.next(v_ext);
END LOOP;
v_out := substr(v_out,1,instr(v_out,p_delim,-1)-1);
return v_out;
end;
Sorting the results
January 13, 2004 - 9am Central time zone
Reviewer: Noam Tene from McLean, VA
This is exactly what I have been trying to do for a while.
My problem was that (as Tom says) aggregates are by definition unsorted. It gets even worse
because with parallel execution the order in which ODCIAggregateMerge happens is unspecified and
may even be non-deterministic. I am not sure the results are even reproduceable.
In cases where the results is going to be grouped by the value returned by stragg, it is often
desireable to treat
"CLARK,KING,MILLER" and "KING,CLARK,MILLER"
as the same combination and to do that it would be useful of the result were lexicographically
sorted.
The sort_string function from Gary was the missing piece.
I wonder if there is some way to create the associative array in ODCIAggregateInitialize and
generate the sorted result in ODCIAggregateTerminate.
That would make the solution much more elegant.
using sragg in Crystal report
February 6, 2004 - 9pm Central time zone
Reviewer: A reader
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?
Followup February 7, 2004 - 2pm Central time zone:
I supposed you would have to call Crystal Reports and ask them why?
Comment to Glenn's solution
February 9, 2004 - 7pm Central time zone
Reviewer: Mikito Harakiri
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.
Followup February 9, 2004 - 8pm Central time zone:
Analytical functions are for those who crave, desire or just sometimes NEED speed.
Instant Gratification
February 20, 2004 - 2pm Central time zone
Reviewer: Kevin Meade from Connecticut USA
Thanks once more Tom. Had a guy call me minutes ago who wanted "a way in 9i to concatinate strings
as an aggregate function, can this be done?".
Natrually being a very experienced IM guy I did what any experienced developer/dba/architect worth
his salt would do; I stole from somebody else who is better at it than I am.
Damn if I don't look good. If you could have heard him: "wow, I have never seen anything like this
before!, this is amazing!, where do you get this stuff?".
I plan on spending some time with him to make sure he verses himself with the technology before he
does anything real with it. Can't have people using code they don't understand.
Did I tell you this is now my favorite site on the internet?
Kevin
10g goes one better...
May 11, 2004 - 5am Central time zone
Reviewer: Adrian Billington from UK
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
Followup May 11, 2004 - 8am Central time zone:
Cool, nice idea there.
anytime we can do something in "more pure sql" the better. what you've effectively done here is
reduce the context switches from sql to plsql from 196k down to 100.
I like it.
How to change STRAGG to use Clob
June 11, 2004 - 6am Central time zone
Reviewer: Ajeet from Bombay.India
Hi Tom,
Stragg does not work if I have large amount of data --as earlier you told that in this case I
should use CLOB...You have refered to an example at google for this.but I want to use the STRAGG
for 9i...How can we change it to use CLOB..I have not worked much on Clobs so want to know from
you.
Thanks
Is this ok
June 11, 2004 - 9am Central time zone
Reviewer: Ajeet from Bombay.india
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
Followup June 11, 2004 - 4pm Central time zone:
the caller might need to close the clob after fetching it (it'll be a temporary clob). you'll want
to check that out and be careful if so.
STRAGG fails if table has a parallel > 1
June 15, 2004 - 2am Central time zone
Reviewer: Ajeet from Bombay,India
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
Followup June 15, 2004 - 3pm Central time zone:
please contact support (for all internal errors)
Invokers rights
August 19, 2004 - 2pm Central time zone
Reviewer: A reader
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
Followup August 19, 2004 - 7pm Central time zone:
since they do no sql, and the invoke nothing that does sql -- it matters not that they are invoker
or definers rights.
it would make sense to place it into a utility schema, yes. but not for security -- for
maintenance.
I see no benefit from authid current_user here, no sql, no change.
Different delimitd
August 27, 2004 - 10am Central time zone
Reviewer: A reader
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
Followup August 27, 2004 - 10am Central time zone:
1) no i didn't, i changed the global variables in the type as well and set the value of one of them
in the return statement.
2) not that i'm aware of. it is why i went with the "obscure" approach.
August 27, 2004 - 11am Central time zone
Reviewer: A reader
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?
Followup August 27, 2004 - 11am Central time zone:
it was inferred you would make that change. sorry, could have made that more clear. I demostrated
how to get the global variable set.
Straight SQL?
September 9, 2004 - 10am Central time zone
Reviewer: Kashif from Reston, VA
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
Followup September 9, 2004 - 12pm Central time zone:
no, i mean it is "straight sql" from then on in.
it would be like saying "don't use max()"
after installation, how does stragg materially differ from max?
September 14, 2004 - 8pm Central time zone
Reviewer: A reader
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
Followup September 15, 2004 - 7am Central time zone:
a quick google search seems to suggest that
db2 has a "technique"
postgress has support
Parameterized stragg...
September 15, 2004 - 8am Central time zone
Reviewer: Padders from UK
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>
Followup September 15, 2004 - 9am Central time zone:
nice, sweet - i like that.
using distinct with parameterized stragg
September 22, 2004 - 12pm Central time zone
Reviewer: Oliver from Germany
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;
/
October 19, 2004 - 6am Central time zone
Reviewer: A reader from Italy
Creating index DDL
October 20, 2004 - 3pm Central time zone
Reviewer: A reader
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
Followup October 20, 2004 - 5pm Central time zone:
o dbms_metadata
o exp rows=n, imp indexfile=foo
o CONFIGURATION MANAGEMENT (you know, the fine art of actually having the ddl for your system)
all of those are inifintely better (especially the last one!) than do it yourself ddl extraction.
DIY DDL extraction is so 1994, this is 2004 - there are much bigger fish to fry.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1464804639878#16619648888465
read that entire thread, but that particular piece shows how to use max(decode... if you continue
down this "bad idea" path.
October 20, 2004 - 3pm Central time zone
Reviewer: A reader
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
Followup October 20, 2004 - 5pm Central time zone:
see above.
dbms_metadata
October 20, 2004 - 5pm Central time zone
Reviewer: A reader
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
Followup October 20, 2004 - 8pm Central time zone:
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 ( ?
October 20, 2004 - 9pm Central time zone
Reviewer: A reader
"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?
Followup October 20, 2004 - 9pm Central time zone:
if you go the procedure route -- getting the owner off is easy (the beginning of a create index is
pretty predicable).
replace() would work -- if the schema name isn't used elsewhere in the create, replace('"SCHEMA".',
'' )
would nuke them all
dbms_metadata
October 20, 2004 - 9pm Central time zone
Reviewer: A reader
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]
Followup October 21, 2004 - 6am Central time zone:
not necessarily, you have instr, substr, etc on a clob. You could move from "clob to clob" using
32k or less pieces.
Excellent
October 21, 2004 - 10am Central time zone
Reviewer: Sasa from Belgrade, Serbia
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
Followup October 21, 2004 - 2pm Central time zone:
stragg does this by default?
October 21, 2004 - 3pm Central time zone
Reviewer: A reader
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
Followup October 22, 2004 - 2pm Central time zone:
give a go -- there are lots of examples on this page -- please, give a "good college try" as they
say.
the way to understand is to do sometimes... it is how i've learned most of what I now know..
October 22, 2004 - 3pm Central time zone
Reviewer: A reader
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
Followup October 23, 2004 - 9am Central time zone:
the map method returned a string (a varchar2) which was the two elements concatented together --
eg:
1|
in your case. 1| = 1| so distinct would treat them the "same".
October 23, 2004 - 3pm Central time zone
Reviewer: A reader
I still dont get it. So in what case would that map method really eliminate dupes?
Followup October 23, 2004 - 4pm Central time zone:
the MAP doesn't eliminate anything.
the MAP permits "distinct" to "distinct", map returns a scalar (string, number, date) represenation
of the complex object. DISTINCT removed the duplicates (based on their MAPPED value)
October 24, 2004 - 12pm Central time zone
Reviewer: A reader
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
Followup October 24, 2004 - 1pm Central time zone:
letting you pass in the delimiter quite simply.
to pass in the delimiter, you use a user defined type.
to distinct a user defined time, you need a map.
October 24, 2004 - 1pm Central time zone
Reviewer: A reader
"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?
Followup October 24, 2004 - 2pm Central time zone:
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
to distinct these things, one needs a MAP or order method.
That is what this MAP method is doing... As stated:
"to distinct a user defined time, you need a map"
it cannot distinct these user defined types without it.
The DISTINCT cannot function without the MAP (or order)
odciaggregatemerge
November 24, 2004 - 9am Central time zone
Reviewer: A reader
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
Followup November 24, 2004 - 9am Central time zone:
seems you have proven your own premise "incorrect" no?
see the data cartridge developers guide on otn.oracle.com, it describes this entire process in
great detail
distinct keyword within aggregate function parameter
November 30, 2004 - 6pm Central time zone
Reviewer: Mikito harakiri
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.
Followup November 30, 2004 - 8pm Central time zone:
Let's see:
you hate aggregates and the syntax (either that or you refuse to read the docs and actually
understand how they are designed. We are back to looking at the screen and just thinking at it and
having it come up with 42 for us all by itself)
you hate analytics
geez..... Ok, I want to know the number of distinct enames and the count of records.
Let's see:
select count(distinct ename), count(*) from emp;
yeah, that is much less clear than:
select count(*), sum(cnt)
from (select ename, count(*) cnt from emp group by emp);
I almost didn't understand that first query but the second -- a work of art, so much more
understandable.
NOT
Let's see your totally intuitive approach to this please:
scott@ORA9IR2> select count(distinct job), count(distinct ename), count(distinct mgr) from emp;
COUNT(DISTINCTJOB) COUNT(DISTINCTENAME) COUNT(DISTINCTMGR)
------------------ -------------------- ------------------
5 14 6
I want it in one row, just like that. Please, share with us the intuitive answer you have without
distinct inside the aggregate.
good example
November 30, 2004 - 9pm Central time zone
Reviewer: mikito
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?
Followup December 1, 2004 - 8am Central time zone:
what do you mean by "How does it happen that ad-hock
solution (distinct keyword inside an aggr function) work better?"
what is ad-hoc about using SQL as documented?
why count distinct is a hack
December 1, 2004 - 6pm Central time zone
Reviewer: mikito
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.
Followup December 1, 2004 - 8pm Central time zone:
so, please (begging here), show me a more intuitive, clear, performant way to do it.
you know, i read the documentation.
I understand what it says.
I use the tools.
I don't make the standards up. I don't even really care frankly. I myself want to know what I can
and cannot do and how to do it and I do it. Period.
You can make up all kinds of fancy terms -- whatever..
count(distinct ... ) sure is nifty when you need it ain't it. SQL would be worse off without it.
tell you what -- ignore its existence (i won't but you can). I'll have faster code, with more
answers -- and the "purity" thing doesn't bother me. I know what it does. I understand what it
does. I respect what it does. Thats all folks.
Clearing the Temporaty CLOB
December 2, 2004 - 2am Central time zone
Reviewer: A reader from Australia from Sydney
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 !
Followup December 2, 2004 - 7am Central time zone:
what is your client code written in. dbms_lob has an api to close/free open temporary clobs --
freetemporary.
Clearing the Temporaty CLOB
December 2, 2004 - 6pm Central time zone
Reviewer: A reader from Sydney
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.
Followup December 2, 2004 - 7pm Central time zone:
call dbms_lob.freetemporary in your code.
verify as you are now that they are freed (you have verfied now that they are not freed, make the
change and check again)
Carefull w
December 7, 2004 - 10am Central time zone
Reviewer: Eugen
If you're using PL/SQL you might experience, what is described in the Note 228479.1 (TEMPORARY LOBS
are not freed up automatically after PL/SQL block execution).
Fix (as stated in the Note):
>>To workaround the problem, disconnect the database user session.
>>This behaviour does not reproduce in Oracle10, but is not a bug.
HtH
Dynamic concatenation string
December 7, 2004 - 9pm Central time zone
Reviewer: A reader from Sydney
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.
Followup December 8, 2004 - 10am Central time zone:
you need to bind a clob to a clob in general. sounds like you are binding a string to a clob.
in old releases -- you really had to do clob to clob, there was no implicit conversion as there is
in current releases.
but you really don't give an example of how you were using it.
Dynamic concatenation string
December 9, 2004 - 2am Central time zone
Reviewer: A reader from Sydney
I had another look at my code and found the proverbial typo.
I am coding against 9iR1.
This works but I would appreciate any feedback. Again it follows Padders from UK (15 Sep 2004)
example. The trick is to output a CLOB rather than VARCHAR2 in concat_all_ot
and the Member Function ODCIAggregateTerminate. The Concat_all Function must also return a CLOB.
But of course you-all knew that already !
So here is a Clobberised version of Padders Code :
SQL> CREATE OR REPLACE TYPE concat_expr AS OBJECT (
2 str VARCHAR2 (4000),
3 del VARCHAR2 (4000));
4 /
Type created.
Elapsed: 00:00:00.00
SQL>
SQL> CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (
2 str CLOB,
3 del VARCHAR2 (4000),
4
5 STATIC FUNCTION odciaggregateinitialize (
6 sctx IN OUT concat_all_ot)
7 RETURN NUMBER,
8
9 MEMBER FUNCTION odciaggregateiterate (
10 SELF IN OUT concat_all_ot,
11 ctx IN concat_expr)
12 RETURN NUMBER,
13
14 MEMBER FUNCTION odciaggregateterminate (
15 SELF IN concat_all_ot,
16 returnvalue OUT CLOB,
17 flags IN NUMBER)
18 RETURN NUMBER,
19
20 MEMBER FUNCTION odciaggregatemerge (
21 SELF IN OUT concat_all_ot,
22 ctx2 concat_all_ot)
23 RETURN NUMBER);
24 /
Type created.
Elapsed: 00:00:00.00
SQL>
SQL> CREATE OR REPLACE TYPE BODY concat_all_ot
2 AS
3 STATIC FUNCTION odciaggregateinitialize (
4 sctx IN OUT concat_all_ot)
5 RETURN NUMBER
6 IS
7 BEGIN
8 sctx := concat_all_ot (NULL, NULL);
9 RETURN odciconst.success;
10 END;
11
12 MEMBER FUNCTION odciaggregateiterate (
13 SELF IN OUT concat_all_ot,
14 ctx IN concat_expr)
15 RETURN NUMBER
16 IS
17 BEGIN
18 IF SELF.str IS NOT NULL THEN
19 SELF.str := SELF.str || ctx.del;
20 END IF;
21 SELF.str := SELF.str || ctx.str;
22 RETURN odciconst.success;
23 END;
24
25 MEMBER FUNCTION odciaggregateterminate (
26 SELF IN concat_all_ot,
27 returnvalue OUT CLOB,
28 flags IN NUMBER)
29 RETURN NUMBER
30 IS
31 BEGIN
32 returnvalue := SELF.str;
33 RETURN odciconst.success;
34 END;
35
36 MEMBER FUNCTION odciaggregatemerge (
37 SELF IN OUT concat_all_ot,
38 ctx2 IN concat_all_ot)
39 RETURN NUMBER
40 IS
41 BEGIN
42 IF SELF.str IS NOT NULL THEN
43 SELF.str := SELF.str || SELF.del;
44 END IF;
45 SELF.str := SELF.str || ctx2.str;
46 RETURN odciconst.success;
47 END;
48 END;
49 /
Type body created.
Elapsed: 00:00:00.00
SQL>
SQL> CREATE OR REPLACE FUNCTION concat_all (
2 ctx IN concat_expr)
3 RETURN CLOB DETERMINISTIC PARALLEL_ENABLE
4 AGGREGATE USING concat_all_ot;
5 /
Function created.
Elapsed: 00:00:00.00
SQL>
Thank you
December 9, 2004 - 11am Central time zone
Reviewer: Lam from Germany
the page ist very good!!!
Another approach - Using Different Delimiters
January 5, 2005 - 2pm Central time zone
Reviewer: JMattinson from CANADA
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
Followup January 5, 2005 - 7pm Central time zone:
I think that silently truncating data, rathering that raising an error is more along the lines of
introducing a bug rather than fixing an error.
How Best to get the Manager Name for an Employee?
February 17, 2005 - 10am Central time zone
Reviewer: Mac
Using the standard EMP table, what is the best way to get the MGR name for each EMP?
Followup February 17, 2005 - 1pm Central time zone:
join
Lookup Function? Join? Something Else?
February 17, 2005 - 11am Central time zone
Reviewer: A reader
To be more specific, which?
Followup February 17, 2005 - 1pm Central time zone:
just join.
Want to make order Ename group by deptno
May 3, 2005 - 3am Central time zone
Reviewer: andrea from Kor
that result is like that
deptno stragg(ename)
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
select deptno, stragg(ename)
from emp
group by deptno;
but that Stragg(ename) is not ordered
even though
SQL like that
select deptno, stragg(ename) from
(select deptno, ename from emp order by deptno, ename)
group by deptno;
i Can't expect ordered name like under result.
deptno stragg(ename)
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
how can i make this??
String Functions
July 19, 2005 - 9am Central time zone
Reviewer: sat from US
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 July 19, 2005 - 10am Central time zone:
ops$tkyte@ORA10G> variable x varchar2(20)
ops$tkyte@ORA10G> exec :x := 'david';
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select str,
2 (length(str)-length(replace(upper(str),upper(:x),'')))/length(:x) cnt
3 from t;
STR CNT
------------------------------------------------------- ----------
david is older than gary 1
david is older than gary, david is younger than smith 2
bob is older than gary, bob is younger than smith 0
nvl
July 19, 2005 - 10am Central time zone
Reviewer: Gabe
Need to nvl the length(replace()) call.
Followup July 19, 2005 - 10am Central time zone:
true, if the string is just daviddaviddavid ....
nvl( length(), 0 )
One more ..
July 19, 2005 - 10am Central time zone
Reviewer: A reader
Could you please explain me this logic. I didn't followed
Why did you use division,length and replace
July 19, 2005 - 10am Central time zone
Reviewer: A reader
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
Followup July 19, 2005 - 12pm Central time zone:
see above
The question of ordering the values we are concatenating.
August 25, 2005 - 10am Central time zone
Reviewer: Jack Douglas from England
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
Followup August 25, 2005 - 2pm Central time zone:
<quote>
If you are prepared to take the additional performance hit, you can build up the
array inside the type and calculate and return the ordered concatenation in
ODCIAggregateTerminate.
</quote>
I've got that somewhere here too ;)
Maybe not on this page, but somewhere - I remember writing it once upon a time ago .
but yes, you are very much correct.
Built in functionality
August 25, 2005 - 5pm Central time zone
Reviewer: Bob B from Albany, NY
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.
Followup August 25, 2005 - 6pm Central time zone:
yup, we've done that too :)
(with the same caveats.... precisely the same caveats)
as always, your help is most useful
December 5, 2005 - 7pm Central time zone
Reviewer: jeff from Portland, OR
Thanks Tom! This has given us the blueprint to help solve a data warehouse denormalization 'sticky
wicket'. Not only that, we can apply the solution to a number of different scenarios - can you say
"Reusable Objects"?
How to make COLLECT( ename ) run parallel?
December 19, 2005 - 5pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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 |
---------------------------------------------------------------------------
Followup December 19, 2005 - 5pm Central time zone:
It does look like a limitation of collect - not sure you can change that behaviour without an
enhancement request.
we can write a parallel enabled version, would that work for you?
Please! make COLLECT( ename ) run parallel.
December 19, 2005 - 6pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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 | |
----------------------------------------------------------------------------------------------------
---------------------
Followup December 20, 2005 - 8am Central time zone:
with such a small set, I would not even be considering parallel - likely you are spending more time
setting up parallel execution than you are actually running the query.
Parallel is good for things that take minutes, anything running as fast as a few seconds, I would
not even *consider* it.
So, unless you have millions of big records - parallel isn't something you want to consider in this
case.
90 Millions rows master table Join 5 Child tables
December 20, 2005 - 1pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
<code>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 (SubQ
Followup December 20, 2005 - 2pm Central time zone:
2) won't happen. we can possibly push the pga workarea to 4gig (maybe) but that's it.
3) we would have to *write it*, it would be variation on the existing stragg function.
The table join order, make small or big table as driven table?
December 20, 2005 - 2pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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 ;
Followup December 20, 2005 - 2pm Central time zone:
well the question (in a review/followup section) was big and long - so it made up for it :)
I read these things *fast* (have to, well over 1,000 a month....)
I'd rather be getting some new questions...
put the tables in any order, it is the job of the CBO to reorder them (in my opinion)
COLLECT( ename ) and read-only physical standby database
December 21, 2005 - 12pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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.
Followup December 21, 2005 - 7pm Central time zone:
that is interesting, if you enable sql_trace first - what do you see in the trace file?
COLLECT( ename ) and read-only physical standby database
December 22, 2005 - 1pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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.
Followup December 22, 2005 - 5pm Central time zone:
just edit the trace file (if you have expert one on one Oracle - I describe the contents in depth).
We'd be looking for the statement that "failed" - search for "err"
The err in trace file
December 23, 2005 - 2pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
=====================
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.
Followup December 23, 2005 - 2pm Central time zone:
ok, easy to see now - it is recursive sql to create a type to collect into - a "fake" type.
You will solve this by creating a REAL type to be used:
create or replace type ename_type as table of varchar2(30)
/
select deptno, cast( collect(ename) as ename_type ) enames
from emp
group by deptno
/
You'll create the real type in production - wait for dataguard (assuming that is what you are
using) to propagate it over to the standby and when you open it read only - that'll not attempt to
do any recursive sql to create a new type.
Error message
December 23, 2005 - 2pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
oerr ora 604
00604, 00000, "error occurred at recursive SQL level %s"
// *Cause: An error occurred while processing a recursive SQL statement
// (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.
@abelabs-em64t-32bit /home/oracle/admin/lab/maint>oerr ora
16000
16000, 00000, "database open for read-only access"
// *Cause: The database has been opened for read-only access. Attempts to
// modify the database using inappropriate DML or DDL statements
// generate this error.
// *Action: In order to modify the database, it must first be shut down and
// re-opened for read-write access.
CAST COLLECT( )
December 23, 2005 - 3pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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.
Followup December 23, 2005 - 4pm Central time zone:
guess I should have tested that in a read only database - i was relying on the trace only. It is
trying to create the "hidden" type after the database startup.
COLLECT( ) and PGA limit
December 28, 2005 - 2pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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().
Followup December 28, 2005 - 6pm Central time zone:
the pga_aggregate_target won't come into play here at all, that is used for sort/hash/bitmap
workareas, it cannot control the overall heap
and that is what you ran out of here.
have you monitored the pga memory usage of the process? what is it using
what is to_string?
you can skip the cast and your own type (which is likely adding overhead) if you want.
COLLECT( ), StrAgg( ) and PGA limit
December 30, 2005 - 3pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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.
January 3, 2006 - 3pm Central time zone
Reviewer: A reader
<code>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=cu
Help
March 27, 2006 - 9pm Central time zone
Reviewer: A reader
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.
Followup March 28, 2006 - 7am Central time zone:
just type:
exit;
or
exit when (condition);
but it is unlikely you want to use "exit" when a better way exists:
ops$tkyte@ORA9IR2> declare
2 l_string varchar2(4000) := 'how now brown cow';
3 begin
4 while (l_string is NOT null)
5 loop
6 dbms_output.put_line
( substr( l_string, 1, instr(l_string||' ',' ') ) );
7 l_string := substr( l_string, instr(l_string||' ',' ')+1 );
8 end loop;
9 end;
10 /
how
now
brown
cow
PL/SQL procedure successfully completed.
ok
March 28, 2006 - 7am Central time zone
Reviewer: A reader
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!
April 20, 2006 - 1pm Central time zone
Reviewer: A reader
April 21, 2006 - 1am Central time zone
Reviewer: stupid
-- 1
Insert into test (ID, STATUS, GRP) Values (1, 'B', 'x');
Insert into test (ID, STATUS, GRP) Values (2, 'B', 'x');
Insert into test (ID, STATUS, GRP) Values (3, 'A', 'x');
Insert into test (ID, STATUS, GRP) Values (4, 'A', 'y');
COMMIT;
-- 2
select distinct z.grp,last_value (z.xconcat) over (partition by grp) zconcat
from
(
select x.grp ,sys_connect_by_path(status,'.') xconcat
from
(select id,lag(id,1) over (partition by grp order by grp,id) parent_id, status, grp
from (select grp, status, min(id) id from test group by grp, status) a
) x
connect by prior id=parent_id
start with parent_id is null
) z
-- 3
GRP ZCONCAT
----------------
x .B.A
y .A
A reader
May 25, 2006 - 5pm Central time zone
Reviewer: Azeem from USA
Hi,
It was the exact procedure what I was looking for.I spent around 3 days to solve this problem but
using your solution I could do it in 2 min.
Thanks to you.
Suppressing buffer Errors -- StringAggType
May 29, 2006 - 9am Central time zone
Reviewer: Anindya Mitra from India
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
nocopy in aggregate function
May 31, 2006 - 9am Central time zone
Reviewer: Anindya from India
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.
Followup May 31, 2006 - 10am Central time zone:
As long as you don't care that the OUT values of a procedure will be "messed up upon hitting an
unhandled exception" you may use it.
You own the code - you make that call. Seems pretty "safe" in this case since we would discard the
result set upon hitting some error anyway it would seem.
String Concatenation
June 22, 2006 - 10am Central time zone
Reviewer: Purav Shah from Texas
Can you do the same thing in Microsoft SQL server 2000....
Followup June 22, 2006 - 2pm Central time zone:
that would be a good question for askbill@ms.com....
string
June 28, 2006 - 5pm Central time zone
Reviewer: mal
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
Followup June 28, 2006 - 6pm Central time zone:
ops$tkyte@ORA10GR2> select substr( file_name, instr( file_name, '/', -1 )+1 ) from dba_data_files;
SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',-1)+1)
-------------------------------------------------------------------------------
users01.dbf
sysaux01.dbf
undotbs01.dbf
system01.dbf
You are the man...
June 28, 2006 - 7pm Central time zone
Reviewer: mal
For windows
June 28, 2006 - 7pm Central time zone
Reviewer: Mahmood Lebbai from Bothell,WA USA
And for windows it is forward slah(/) to be replaced with backward slash(\)
The query...
select substr( file_name, instr( file_name, '\', -1 )+1 )
from dba_data_files
Nice use of COLLECT but can we prevent hidden type creation?
June 29, 2006 - 5pm Central time zone
Reviewer: Aaron Valdes from San Diego, Ca
Its seems to create a type for every table when the length gets bigger:
SQL> create or replace type ntt_varchar2 as table of varchar2(4000);
2 /
Type created.
SQL>
SQL> CREATE TABLE names (NAME VARCHAR2(40));
Table created.
SQL> CREATE TABLE names2 (NAME VARCHAR2(60));
Table created.
SQL>
SQL> INSERT INTO names VALUES ('rick');
1 row created.
SQL> INSERT INTO names VALUES ('bob');
1 row created.
SQL> INSERT INTO names VALUES ('tom');
1 row created.
SQL>
SQL> INSERT INTO names2 VALUES ('rick');
1 row created.
SQL> INSERT INTO names2 VALUES ('bob');
1 row created.
SQL> INSERT INTO names2 VALUES ('tom');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TYPE';
OBJECT_NAME
--------------------------------------------------------------------------------
NTT_VARCHAR2
1 row selected.
SQL>
SQL> SELECT CAST( COLLECT (NAME) AS ntt_varchar2 )
2 FROM names;
CAST(COLLECT(NAME)ASNTT_VARCHAR2)
--------------------------------------------------------------------------------
NTT_VARCHAR2('rick', 'bob', 'tom')
SQL>
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TYPE';
OBJECT_NAME
--------------------------------------------------------------------------------
NTT_VARCHAR2
SYSTPF2Ofw9UPTkbgQCUK7ZV+YQ==
2 rows selected.
SQL>
SQL> SELECT CAST( COLLECT (NAME) AS ntt_varchar2 )
2 FROM names2;
CAST(COLLECT(NAME)ASNTT_VARCHAR2)
--------------------------------------------------------------------------------
NTT_VARCHAR2('rick', 'bob', 'tom')
SQL>
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TYPE';
OBJECT_NAME
--------------------------------------------------------------------------------
NTT_VARCHAR2
SYSTPF2Ofw9UPTkbgQCUK7ZV+YQ==
SYSTPF2Ofw9UUTkbgQCUK7ZV+YQ==
3 rows selected.
SQL>
Kinda annoying since these objects show up in schema compares, etc.
thanks
number of occurances
July 12, 2006 - 6pm Central time zone
Reviewer: A reader
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?
Followup July 12, 2006 - 6pm Central time zone:
a = length(str) = length of the original string
b = length(replace(upper(str),upper(:x),'')) = length of string WITHOUT :x in it
c = length(:x) = length of the :x string, the thing you want to count
so....
a-b = how many characters were removed from STR by the replace()
a-b
--- = how many times we removed :x from A
c
eg:
str = 'hello mellow yellow'
:x = ll
1 select length(:str),
2 length(replace(upper(:str),upper(:x),'')),
3 length(:x)
4* from dual
ops$tkyte@ORA10GR2> /
LENGTH(:STR) LENGTH(REPLACE(UPPER(:STR),UPPER(:X),'')) LENGTH(:X)
------------ ----------------------------------------- ----------
19 13 2
So, str-replace() = 19-13 = 6. We removed six characters by replacing 'll' with nothing.
6/2 = 3, we must have removed 3 occurences of 'll' from :str...
allowing only charcters
August 4, 2006 - 3pm Central time zone
Reviewer: ram from usa
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
Followup August 4, 2006 - 4pm Central time zone:
ops$tkyte%ORA10GR2> create table t
2 ( x varchar2(30)
3 constraint alpha_check
4 check ( replace(
5 translate(
6 upper(x),' ABCDEFGHIJKLMNOPQRSTUVWXYZ',rpad('X',27,'X')),
7 'X','') is null )
8 )
9 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'Tom Kyte' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 'tom_kyte' );
insert into t values ( 'tom_kyte' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.ALPHA_CHECK) violated
the translate turns all valid characters (space plus alphabet) into 'X'
the replace turns all 'X's into nothing
if the replaced, translated, upper case string is now NULL - it is OK.
ORA-600
August 9, 2006 - 1pm Central time zone
Reviewer: Dierk from Germany
<code>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
Followup August 9, 2006 - 4pm Central time zone:
ora-600 = please utilize support. You have metalink access, that is the place for this.
Getting ORA-03113 error: Am I missing something ?
August 11, 2006 - 4pm Central time zone
Reviewer: Santosh Kompella from Philly, NJ USA
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
Followup August 11, 2006 - 6pm Central time zone:
please contact support.
check constraint
August 23, 2006 - 1pm Central time zone
Reviewer: sat
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
Followup August 27, 2006 - 5pm Central time zone:
one way is:
check ( substr( col, 4,1 ) = '-' and translate(col,'0123456789','----------') = '--------' )
ops$tkyte%ORA10GR2> create table t
2 (
3 col varchar2(8)
4 constraint check_phone check
5 ( substr( col, 4,1 ) = '-'
6 and
7 translate(col,'0123456789','----------') = '--------'
8 )
9 )
10 /
Table created.
ops$tkyte%ORA10GR2> insert into t values ( '123-4567' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '12304567' );
insert into t values ( '12304567' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated
ops$tkyte%ORA10GR2> insert into t values ( '123-456' );
insert into t values ( '123-456' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated
ops$tkyte%ORA10GR2> insert into t values ( 'x23-4567' );
insert into t values ( 'x23-4567' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated
ops$tkyte%ORA10GR2> insert into t values ( ' 23-4567' );
insert into t values ( ' 23-4567' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_PHONE) violated
problem in 10g
January 8, 2007 - 3pm Central time zone
Reviewer: Rahul from columbus
nor i can use sys_connect_by_path
or stragg in the function
CREATE OR REPLACE FUNCTION APPS.PEL_NUM_CHARS(INSTRING VARCHAR2)
RETURN VARCHAR2
IS
l_output VARCHAR2(100);
BEGIN
select apps.stragg(ch)
--LTRIM(MAX(SYS_CONNECT_BY_PATH (ch , ',')),',')
into l_output
FROM (
SELECT SUBSTR(IN_STR, LEVEL , 1) ch , ROWNUM rn
FROM (SELECT INSTRING AS IN_STR FROM dual)
CONNECT BY LEVEL <= LENGTH(IN_STR)
)
-- START WITH rn = 1
-- CONNECT BY PRIOR rn = rn -1
;
return l_output;
exception when others then
return null;
END;
/
if the string is '11011' i want the output to be '1,1,0,1,1'
Multiple columns
February 22, 2007 - 3pm Central time zone
Reviewer: Tom Fox from Cincinnati, OH
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?
Followup February 22, 2007 - 7pm Central time zone:
search this site for stragg
then you can
select id, stragg( type || '|' || name ) from t group by id
February 22, 2007 - 7pm Central time zone
Reviewer: Tom Fox from Cincinnati, OH
Thanks! I saw stragg, but obviously I overlooked something. Have a great evening.
Concatenate Varchar2 in a single SQL without using Functions
February 25, 2007 - 11pm Central time zone
Reviewer: Jaidev Thakur from Singapore
SQL> select * from temp_temp
KEY ERRORS
--------- ----------
S01992 631
S01992 632
S01992 634
S01994 701
S01994 705
select key,max(substr(sys_connect_by_path(errors,','),2)) errors
from (select key, errors, row_number() over ( partition by key order by
errors ) curr,
row_number() over ( partition by key order by errors ) - 1 prev from
temp_temp )
start with curr = 1
connect by prior curr = prev
and prior key= key
group by key
/
KEY ERRORS
----- -------
S01992 631,632,634
S01994 701,705
STRAGG - Reverse
July 9, 2007 - 3pm Central time zone
Reviewer: patty from NC
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
.
.
.
Followup July 9, 2007 - 8pm Central time zone:
ops$tkyte%ORA10GR2> create table your_data
2 as
3 select deptno, stragg( ename ) names
4 from scott.emp
5 group by deptno
6 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with
2 data
3 as
4 (
5 select level r
6 from dual
7 connect by level <= 4000
8 )
9 select deptno, r, names,
10 substr( names,
11 instr( names, ',', 1, r )+1,
12 instr( names, ',', 1, r+1 ) - instr( names, ',', 1, r ) - 1
13 ) nm
14 from (select deptno, ','||names||',' names,
15 length(names)-length(replace(names,',',''))+1 elements
16 from your_data
17 ) your_data, data
18 where data.r <= your_data.elements
19 order by deptno, r
20 /
DEPTNO R NAMES NM
---------- ---------- ----------------------------------------
----------------------------------------
10 1 ,clark,king,miller, clark
10 2 ,clark,king,miller, king
10 3 ,clark,king,miller, miller
20 1 ,smith,ford,adams,scott,jones, smith
20 2 ,smith,ford,adams,scott,jones, ford
20 3 ,smith,ford,adams,scott,jones, adams
20 4 ,smith,ford,adams,scott,jones, scott
20 5 ,smith,ford,adams,scott,jones, jones
30 1 ,allen,blake,martin,turner,james,ward, allen
30 2 ,allen,blake,martin,turner,james,ward, blake
30 3 ,allen,blake,martin,turner,james,ward, martin
30 4 ,allen,blake,martin,turner,james,ward, turner
30 5 ,allen,blake,martin,turner,james,ward, james
30 6 ,allen,blake,martin,turner,james,ward, ward
14 rows selected.
RE: STRAGG - Reverse
July 10, 2007 - 10am Central time zone
Reviewer: Frank Zhou from Braintree, MA
Patty,
Here are two more alternative SQL solutions for " Reverse the STRAGG"
Frank
http://www.jlcomp.demon.co.uk/faq/Split_Strings.html
SQL> set serveroutput on
SQL> Create table ts as
2 select 10 as id , 'CLARK.KING.MILLER' as str from dual
3 union all
4 select 20 as id , 'SMITH.JONES.SCOTT.ADAMS.FORD' as str from dual
5 union all
6 select 30 as id , 'ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES ' as str from dual
7 ;
Table created.
SQL> select * from ts;
ID STR
---------- -------------------------------------
10 CLARK.KING.MILLER
20 SMITH.JONES.SCOTT.ADAMS.FORD
30 ALLEN.WARD.MARTIN.BLAKE.TURNER.JAMES
SQL> COLUMN name FORMAT A12
SQL> select id as deptno, name_str.extract('/X/text()').getstringVal() name
2 from ts, table(xmlSequence(
3 extract(XMLType('<DOC><X>'||replace(str,'.','</X><X>')||'</X></DOC>'),'/DOC/X'))) name_str;
DEPTNO NAME
---------- ------------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 JONES
20 SCOTT
20 ADAMS
20 FORD
30 ALLEN
30 WARD
30 MARTIN
DEPTNO NAME
---------- ------------
30 BLAKE
30 TURNER
30 JAMES
14 rows selected.
SQL> spool off;
Reverse STRAGG
July 10, 2007 - 11am Central time zone
Reviewer: patty from NC
Much appreciated! This works beautifully. My data has an element of complexity in that it looks like this:
ID DATA (3 fields separated by columns, and pipes to separate each group of fields)
-- -----
10 3,,|5,x,|
20 5,,|
30 7,a,c|2,x,|10,,|
.
.
I'll work on splitting out the other two fields as well, but you've enabled me to get such a head start with this code you provided.
Many thanks.
Reverse Stragg
July 10, 2007 - 11am Central time zone
Reviewer: Patty from NC
Thank you to both of you!
RE: STRAGG - Reverse
July 10, 2007 - 12pm Central time zone
Reviewer: Frank Zhou from Braintree, MA
Hi Patty,
How about the following query?
Is that answered your real question?
Frank
SQL> select * from ts2;
ID STR
---------- ----------------
10 3,,|5,x,|
20 5,,|
30 7,a,c|2,x,|10,,|
SQL> select id as deptno, name_str.extract('/X/text()').getstringVal() name
2 from
3 (select id, replace(rtrim(str, '|'), '|', ',') str from ts2),
4 table(xmlSequence(
5 extract(XMLType('<DOC><X>'||replace(str,',','</X><X>')||'</X></DOC>'),'/DOC/X'))) name_str
6 ;
DEPTNO NAME
---------- ------------
10 3
10
10
10 5
10 x
10
20 5
20
20
30 7
30 a
30 c
30 2
30 x
30
30 10
30
30
18 rows selected.
SQL>
reverse stragg
July 10, 2007 - 12pm Central time zone
Reviewer: Patty from NC
I should make myself clear.
Input:
ID STR
-------------------
10 3,,|5,x,|
20 5,,|
30 7,a,c|2,x,|10,,|
The output will end up like this:
ID STR CD1 CD2
-- --- --- ---
10 3
10 5 x
20 5
30 7 a c
30 2 x
30 10
RE: STRAGG - Reverse
July 10, 2007 - 2pm Central time zone
Reviewer: Frank Zhou from Braintree, MA
SQL> select deptno, extractvalue(data, '//Z[1]') col_1,
2 extractvalue(data, '//Z[2]') col_2, extractvalue(data, '//Z[3]') col_3
3 from
4 (select deptno, XMLType('<DOC1><Z>'|| replace(name,',', '</Z><Z>')||'</Z></DOC1>') data
5 from
6 (select id as deptno, name_str.extract('/X/text()').getstringVal() name
7 from
8 (select id, rtrim(str, '|') str from ts2),
9 table(xmlSequence(
10 extract(XMLType('<DOC><X>'|| replace(str,'|', '</X><X>')||'</X></DOC>'),'/DOC/X'))
) name_str
11 )
12 );
DEPTNO COL COL COL
---------- --- --- ---
10 3
10 5 x
20 5
30 7 a c
30 2 x
30 10
6 rows selected.
Reverse STRAGG
July 10, 2007 - 4pm Central time zone
Reviewer: Patty from NC
Thank you, Frank.
I don't understand the code completely, but I was able to modify it to use my table and column names and got the results I need. Now - I need to attempt to figure out how it works!
Many thanks.
String concatenation
October 12, 2007 - 1am Central time zone
Reviewer: Tarun Sehgal from INDIA
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
Followup October 14, 2007 - 9pm Central time zone:
if you can provide a test case from start to finish - create table, insert into table, run stragg, I'll take a look
short of that, please utilize support.
MODEL
November 21, 2007 - 7am Central time zone
Reviewer: Laurent Schneider from Switzerland
I am aggreing with your remark about WM_CONCAT. I thought it was fun to post it on otn forums, but I should have specified more explicitely that it is undocumented. My bad.
Ok, I tried something else with model
select deptno, enames
from emp
model
return updated rows
partition by (deptno)
dimension by (
row_number() over (
partition by deptno
order by ename)
rn)
measures (
cast(ename as varchar2(4000)) enames)
rules iterate (1000) until presentv(enames[ITERATION_NUMBER+3],1,2)=2 (
enames[1] = enames[1] || presentv(enames[2],',','') || enames[ITERATION_NUMBER+2]
);
DEPTNO ENAMES
------ ------------------------------------
20 ADAMS,FORD,JONES,SCOTT,SMITH
10 CLARK,KING,MILLER
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
it is then quite straightforward to change the order in the ORDER BY clause.
wm_concat
November 21, 2007 - 12pm Central time zone
Reviewer: Barbara Boehmer from Riverside County, CA USA
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>
Followup November 20, 2007 - 2pm Central time zone:
my suggestion is going to be consistent....
Never use undocumented stuff, it is subject to change, removal, broken-ness without recourse.
either
a) use stragg
b) write your own
c) use the connect by trick.
stragg has sorting built in
January 2, 2008 - 3pm Central time zone
Reviewer: Sean D. Stuber from Columbus, OH USA
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
Correcting above links
January 2, 2008 - 7pm Central time zone
Reviewer: Balaji from Stamford,CT
http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTION_ID:2196162600402#2587
9699852042
http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTIOID:2196162600402#797128
2174613
Correction of Link
January 2, 2008 - 7pm Central time zone
Reviewer: Balaji from Stamford,CT
http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTION_ID:2196162600402#2587
9699852042
http://asktom.oracle.com/pls/asktom/f?p=100:11:207529176231702::::P11_QUESTION_ID:2196162600402#7971
282174613
An extension of concatenation
March 29, 2008 - 1pm Central time zone
Reviewer: A reader
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
Followup March 31, 2008 - 8am Central time zone:
ugh, that is not an extension of concatenation.
that is turning a database into a calculator.
You have to write a function
Summing concatenated numbers
March 30, 2008 - 9am Central time zone
Reviewer: A reader
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
Followup March 31, 2008 - 9am Central time zone:
can you spell:
SQL INJECTION
google it, you are very much subject to it.
ouch:
when others then
return -999999999;
end calc_tot;
/
I'll never ever understand code like that... :( if you have an error, LET IT BE AN ERROR, don't hide it!
I can give you much more complex way using cast, multiset, lots of substrings - but I won't (you are doing this all wrong, this data should be evaluated AND THEN stored, not stored and evaluated, over and over and over and over and over again)
SQL Injection
April 1, 2008 - 12am Central time zone
Reviewer: A reader
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
Followup April 1, 2008 - 9am Central time zone:
to understand it is an error - LET IT BE AN ERROR FOR GOODNESS SAKE.
What is more understandable:
return code = ORA-12345 Error happened!!!!!
or
return code = 0, everything is OK.
and that forum posting seems to have, well, absolutely nothing to do with this?
ref cursor
May 23, 2008 - 11am Central time zone
Reviewer: A reader from poland
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
Followup May 23, 2008 - 6pm Central time zone:
it would be the same thing as the link
May 29, 2008 - 11am Central time zone
Reviewer: Karteek from India
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!
Followup May 29, 2008 - 1pm Central time zone:
that would be a bug...
String Concatenation & storing the data into VARCHAR2(4000)
June 27, 2008 - 1pm Central time zone
Reviewer: Hemal Deshmukh from Pune,India
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
Followup June 27, 2008 - 3pm Central time zone:
magic?
seriously - look at what you wrote, look at what you ask...
you will either
a) do something entirely different (i would suggest - umm - DO NOT STORE A CONCATENATED STRING - i hate that)
b) change your list of "we cannot do this" in order to make it possible to do
String Concatenation & storing the data into VARCHAR2(4000)
June 28, 2008 - 4am Central time zone
Reviewer: Hemal Deshmukh from Pune,India
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
Followup June 28, 2008 - 1pm Central time zone:
clients have software requirements
clients do not describe physical storage.
it would be silly to store this concatenated string in multiple rows to put together in client - when it would be the SAME CODE TO DO IT TO THE EXISTING DATA
do you get the point here - they either take some number of rows and in a client concatenate them (that varchar2(4000) approach) or they take some number of rows and in a client concatenate them (the leave the data as it is approach)
Now, which makes more sense.
(and if a developer could not do what was asked above - which I firmly believe should not be done - which is "build a string from an array - until adding to it would exceed 4000 characters, then reset string and start over - until it exceeds 4000 and so on".........................)
Parsing a string data into multiple columns data
September 19, 2008 - 11pm Central time zone
Reviewer: Anan from AZ USA
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,
Followup September 21, 2008 - 1pm Central time zone:
correct me if I'm wrong, but is the logic you tried to describe for parsing:
FULLPATH :=
'/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU000523
55-SAL';
ABC1 = 'TAG-MF-AVAIL'
ABC2 = 'APP_JA'
ABC3 = 'PROD-TAG-POOL'
ABC4 = 'AU00052355-PEOPLE'
ABC4 = 'AU00052355-SAL'
really:
a) look for /C- and take everything from there upto the next /
b) look for /C-O_ and take everything from there upto the next /
and so on? are the /<tags> FIXED, always c, c-o_ and so on.
Parsing a string data into multiple columns data
September 22, 2008 - 2pm Central time zone
Reviewer: Anan from AZ USA
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
Followup September 23, 2008 - 12pm Central time zone:
so, I would not use regexp - you need a simple substr.
ops$tkyte%ORA10GR2> create table t ( x varchar2(100) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO T VALUES (
'/C-TAG-MF-AVAIL/C-O_APP_JA/A-A_PROD-TAG-POOL/R-R_AU00052355-PEOPLE/R-M_AU00052355-SAL' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select substr( c1, 1, instr(c1,'/')-1 ) c1,
2 substr( c2, 1, instr(c2,'/')-1 ) c2,
3 substr( c3, 1, instr(c3,'/')-1 ) c3,
4 substr( c4, 1, instr(c4,'/')-1 ) c4,
5 substr( c5, 1, instr(c5,'/')-1 ) c5
6 from (
7 select substr( x, instr( x, '/C-' )+4 ) c1,
8 substr( x, instr( x, '/C-O_' ) +5 ) c2,
9 substr( x, instr( x, '/A-A_' ) +5 ) c3,
10 substr( x, instr( x, '/R-R_' ) +5 ) c4,
11 substr( x, instr( x, '/R-M_' ) +5 ) ||'/' c5
12 from t
13 );
C1 C2 C3 C4 C5
-------------------- ---------- -------------------- -------------------- --------------------
AG-MF-AVAIL APP_JA PROD-TAG-POOL AU00052355-PEOPLE AU00052355-SAL
Parameter as a column
October 30, 2008 - 6am Central time zone
Reviewer: A Reader
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
How to get the concatenated string SORTED ?
January 12, 2009 - 9am Central time zone
Reviewer: Philippe Gaudin from Belgium
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 ?
Followup January 12, 2009 - 9pm Central time zone:
did you ctl-f for ordered on this page and read the previous numerous conversations on this?
How to get the concatenated string SORTED ?
January 13, 2009 - 3am Central time zone
Reviewer: Philippe Gaudin from Belgium
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.
Followup January 16, 2009 - 3pm Central time zone:
ops$tkyte%ORA9IR2> select deptno, substr( max( sys_connect_by_path(ename,',')), 2) enames
2 from (
3 select deptno, ename, row_number() over (partition by deptno order by ename) rn
4 from emp
5 )
6 start with rn = 1
7 connect by prior deptno = deptno and prior rn+1 = rn
8 group by deptno
9 order by deptno
10 /
DEPTNO ENAMES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
Concatinating a string with a complex parameters
February 24, 2009 - 4am Central time zone
Reviewer: Andrey from Russia, Moscow
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.
Followup February 24, 2009 - 6am Central time zone:
give a bit more context here, sketch out your requirement a bit better. the context parameter is a type in all of the examples here already?
More explanation about the problem
February 24, 2009 - 7am Central time zone
Reviewer: Andrey from Russia, Moscow
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.
Followup February 24, 2009 - 4pm Central time zone:
... But, as i see we have no place to call this constructor in order to it'll be passed into an ODCIAggregateInitialize... Am i right? ...
it is called by us, we call it, you do what you want in it. Not sure what the issue is yet?
...
If i am, then could we write some code into the aggregate function itself? ...
you write all of the code, yes.
If the sys_context is to be used in the query itself, no, this will not be safe, if you need some "state", you can add more attributes to your object type - not sure what you would use sys context for - the query that invokes your code would already be "started", it would be too late to set context values for it.
Or do you mean "i'd like to write an aggregate that works like this:
select my_aggregate( a, b, c, d ) from t;
with many inputs"
More explanation about the problem 2
February 25, 2009 - 5am Central time zone
Reviewer: Andrey from Russia, Moscow
...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?
Followup February 25, 2009 - 7am Central time zone:
I'm not following you at all here -
...
Nope. Oracle points an error here. We only can declare an aggregate function, but we can't implement it's body. Or just i don't know how.
..
you write the type body, we call it, you WRITE the aggregate.
If your goal is to be able to have more than one parameter to the aggregate, the only way I know is the approach already shown on this page.
ctl-f for:
"You would have to sort of "prime the pump" on this one."
to see it, it does involve a "context", you would set the parameters in the session context before invoking the aggregate, yes.
Converting multiple rows into single row
March 25, 2009 - 4am Central time zone
Reviewer: SuryaKiran from India
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 ?
Followup March 29, 2009 - 7pm Central time zone:
This is sufficient to demonstrate you have found a bug (I filed bug 8391928 for this)
ops$tkyte%ORA11GR1> create table test_asg(
2 Assignment_id number(15),
3 Title varchar2(50),
4 Emp_cat varchar2(2)
5 ) ;
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into test_asg values(100,'Comp info','AP') ;
1 row created.
ops$tkyte%ORA11GR1> insert into test_asg values(100,'Technician','CS') ;
1 row created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select assignment_id, title, emp_cat,
2 row_number() over (partition by assignment_id order by assignment_id) rnum
3 from test_asg
4 where assignment_id = 100
5 /
ASSIGNMENT_ID TITLE EMP_CAT RNUM
------------- -------------------- -------------------- ----------
100 Comp info AP 1
100 Technician CS 2
ops$tkyte%ORA11GR1> select assignment_id,
2 sys_connect_by_path(title,';') asg_title,
3 sys_connect_by_path(emp_cat,';') asg_empcat
4 from (
5 select assignment_id, title, emp_cat,
6 row_number() over (partition by assignment_id order by assignment_id) rnum
7 from test_asg
8 where assignment_id = 100
9 )
10 start with rnum = 1
11 connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
12 /
ASSIGNMENT_ID ASG_TITLE ASG_EMPCAT
------------- --------------------- --------------------
100 ;Comp info ;AP
100 ;Comp info;Technician ;AP;CS
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select assignment_id,
2 max( asg_title ) job_title,
3 max( asg_empcat ) emp_category
4 from (
5 select assignment_id,
6 sys_connect_by_path(title,';') asg_title,
7 sys_connect_by_path(emp_cat,';') asg_empcat
8 from (
9 select assignment_id, title, emp_cat,
10 row_number() over (partition by assignment_id order by assignment_id) rnum
11 from test_asg
12 where assignment_id = 100
13 )
14 start with rnum = 1
15 connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
16 )
17 group by assignment_id
18 order by assignment_id
19 /
ASSIGNMENT_ID JOB_TITLE EMP_CATEGORY
------------- -------------------- --------------------
100 ;Comp info;Comp info ;AP;AP
I took query 1 and placed into query 2, query 2 shows the differing strings - we take query 2 and place it into query 3 with a MAX() and - well, data appears that doesn't exist.
Not repeat result
April 14, 2009 - 11am Central time zone
Reviewer: Serpini from Spain
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;
Followup April 14, 2009 - 12pm Central time zone:
what happens if the strings are Pepe and Pepe Le Pew....
watch out for strings contained in other strings.
Calculating the value of a string with numbers and arithmetic operators
April 27, 2009 - 1am Central time zone
Reviewer: Logan Palanisamy from Santa Clara, CA, USA
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
Followup April 27, 2009 - 2pm Central time zone:
think about it, how would '1+1' be 'castable' (change datatype) to a number?
It would have to be evaluated.
will it always be plus (addition) or in real is this a string of any degree of complexity (+, -, (), *, /, etc)
Evaluating an arithmetic string
April 27, 2009 - 3pm Central time zone
Reviewer: Logan Palanisamy from Santa Clara, CA, USA
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'
Followup April 27, 2009 - 3pm Central time zone:
ops$tkyte%ORA10GR2> create table t ( x int, y varchar2(30) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1, '+1+15+25+45' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 2, '18*27*32' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select x, y, case when oper = '+' then sum(column_value)
2 else exp(sum(ln(case when oper='+' then 1 else column_value end)))
3 end result
4 from (select x, y,
5 ','||translate( ltrim(y,'+'), '+*', ',,') ||',' newy,
6 case when instr( y, '*' ) > 0 then '*' else '+' end oper
7 from t ) t,
8 TABLE(
9 cast (
10 multiset(
11 select trim( substr(newy, instr (newy, ',', 1, level ) + 1,
12 instr (newy, ',', 1, level+1) - instr (newy, ',', 1, level) -1 ) )
13 from dual
14 connect by level <= length(newy)-length(replace(newy,',',''))-1
15 )
16 as sys.odcinumberlist )
17 )
18 group by x, y
19 /
X Y RESULT
---------- ------------------------------ ----------
1 +1+15+25+45 86
2 18*27*32 15552
Superb!!!
April 27, 2009 - 5pm Central time zone
Reviewer: Logan Palanisamy from Santa Clara, CA, USA
Not repeat result version 2
April 28, 2009 - 7am Central time zone
Reviewer: Serpini from Spain
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;
----------
Followup April 28, 2009 - 10am Central time zone:
or, you could leave code "as is" and use distinct
ops$tkyte%ORA11GR1> select deptno, stragg(distinct job) a, stragg(job) b from emp group by deptno;
DEPTNO A B
---------- ------------------------------ ------------------------------
10 CLERK,MANAGER,PRESIDENT MANAGER,CLERK,PRESIDENT
20 ANALYST,CLERK,MANAGER CLERK,CLERK,ANALYST,ANALYST,MA
NAGER
30 CLERK,MANAGER,SALESMAN SALESMAN,SALESMAN,CLERK,SALESM
AN,MANAGER,SALESMAN
varchar2 max size reduced to 8192
May 13, 2009 - 4am Central time zone
Reviewer: Michal Pravda from Prague
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
Followup May 13, 2009 - 1pm Central time zone:
what is your character set - any change the resulting STRING is using multibyte data (and your char(15000) is 15000 BYTES not characters...)
ops$tkyte%ORA10GR2> declare
2 cl clob;
3 str varchar2(15000 char);
4 begin
5 for idx in 1..1000 loop -- works fine
6 str := str || '0123456789';
7 dbms_output.put_line(idx);
8 end loop;
9
10 for idx in 1..1000 loop -- ends with "numeric or value error" when trying to
11 -- assign clob longer then 8192 to varchar2 variable
12 cl := cl || '0123456789';
13 dbms_output.put_line(idx);
14 str := cl;
15 end loop;
16
17 end;
18 /
1
2
3
4
5
6
7
...
992
993
994
995
996
997
998
999
1000
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> declare
2 cl clob;
3 str varchar2(15000 char);
4 begin
5 for idx in 1..1000 loop -- works fine
6 str := str || '0123456789';
7 end loop;
8 dbms_output.put_line( 'length = ' || length(str) );
9
10 for idx in 1..1000 loop -- ends with "numeric or value error" when trying to
11 -- assign clob longer then 8192 to varchar2 variable
12 cl := cl || '0123456789';
13 str := cl;
14 end loop;
15 dbms_output.put_line( 'length = ' || length(str) );
16 end;
17 /
length = 10000
length = 10000
PL/SQL procedure successfully completed.
reduced string length
June 12, 2009 - 8am Central time zone
Reviewer: Michal Pravda from Prague
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
Followup June 12, 2009 - 11am Central time zone:
... So I believed that varchar2(15000 char) would be sufficient. ...
multibyte data is MULTI-byte (more than one, more than two even, or three), the longest string in plsql is 32k - no matter what.
so, that might not be sufficient.
in any case, why wouldn't you do a little debugging to see how big the string can actually get - don't just print out the error message, print out the length in bytes (lengthb) of the string as it was when it was last succcessfully appended to
reduced varchar2 size
June 15, 2009 - 5am Central time zone
Reviewer: Michal Pravda from Prague
Hi,
I haven't done the "debugging" before because of something between
a) not very used to lengthB (and similar byte) function
and belief that
b) utf-8 uses up to 3 bytes so 8192 boundary has nothing in common with the problem. (which is
wrong(originally utf-8 was up to 6 bytes, norm from 2003 reduced that to 4 (according to
wikipaedia)))
Here's my revised script, it's results and my interpretation (ran on utf-8 10.2.04 DB with
(NLS_NCHAR_CHARACTERSET AL16UTF16).
DECLARE
ncl NCLOB;
cl CLOB;
str VARCHAR2(32767);
tmp VARCHAR2(10);
BEGIN
-- tmp := chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) ||
chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980);
-- tmp := '0123456789';
tmp := '¿¿¿¿¿¿¿¿¿¿';
dbms_output.put_line('CLOB CLOB CLOB CLOB CLOB CLOB');
str := NULL;
BEGIN
FOR idx IN 1 .. 819 LOOP
-- ends with "numeric or value error" when trying to
-- assign clob longer then 8191 to varchar2 variable
cl := cl || tmp;
str := cl;
END LOOP;
cl := cl || '¿';
str := cl;
dbms_output.put_line('ok so far');
dbms_output.put_line('clob2varchar length = ' || length(str) || ', clob length ' ||
length(cl));
dbms_output.put_line('clob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(cl));
cl := cl || 'a';
str := cl;
dbms_output.put_line('ok so far');
dbms_output.put_line('clob2varchar length = ' || length(str) || ', clob length ' ||
length(cl));
dbms_output.put_line('clob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(cl));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('clob2varchar length = ' || length(str) || ', clob length ' ||
length(cl));
dbms_output.put_line('clob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(cl));
END;
dbms_output.put_line('NCLOB NCLOB NCLOB NCLOB NCLOB NCLOB');
str := NULL;
BEGIN
FOR idx IN 1 .. 1638 LOOP
-- ends with "numeric or value error" when trying to
-- assign nclob longer then 16383 to varchar2 variable
ncl := ncl || tmp;
str := ncl;
END LOOP;
ncl := ncl || '¿¿¿';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' ||
length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
ncl := ncl || 'a';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' ||
length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' ||
length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
END;
dbms_output.put_line('NCLOB with kanji NCLOB with kanji NCLOB with kanji ');
str := NULL;
ncl := null;
tmp := chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) ||
chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980) || chr(14989980); BEGIN
FOR idx IN 1 .. 1092 LOOP
-- ends with "buffer too small" when trying to
-- assign nclob longer then 16383 to varchar2 variable
ncl := ncl || tmp;
str := ncl;
END LOOP;
ncl := ncl || '¿¿¿a';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' ||
length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
ncl := ncl || 'a';
str := ncl;
dbms_output.put_line('ok so far');
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' ||
length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('nclob2varchar length = ' || length(str) || ', nclob length ' ||
length(ncl));
dbms_output.put_line('nclob2varchar lengthb= ' || lengthb(str) || ', dbms_lob.getlength ' ||
dbms_lob.getlength(ncl));
END;
END;
/
CLOB CLOB CLOB CLOB CLOB CLOB
ok so far
clob2varchar length = 8191, clob length 8191
clob2varchar lengthb= 16382, dbms_lob.getlength 8191
ORA-06502: PL/SQL: numeric or value error
clob2varchar length = 8191, clob length 8192
clob2varchar lengthb= 16382, dbms_lob.getlength 8192
NCLOB NCLOB NCLOB NCLOB NCLOB NCLOB
ok so far
nclob2varchar length = 16383, nclob length 16383
nclob2varchar lengthb= 32766, dbms_lob.getlength 16383
ORA-06502: PL/SQL: numeric or value error
nclob2varchar length = 16383, nclob length 16384
nclob2varchar lengthb= 32766, dbms_lob.getlength 16384
NCLOB with kanji NCLOB with kanji NCLOB with kanji
ok so far
nclob2varchar length = 10924, nclob length 10924
nclob2varchar lengthb= 32767, dbms_lob.getlength 10924
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
nclob2varchar length = 10924, nclob length 10925
nclob2varchar lengthb= 32767, dbms_lob.getlength 10925
Varchar2 max size is 32k -1 bytes. Utf-8 takes up to 4 chars so I think that Oracle takes the safe
and fast way of raising an error when there is potential of buffer overflow regardless of actual
data. So for this assignment there is a limit of trunc((32768 - 1) /4) = 8191 characters.
With NCLOB the situation is a bit different. I think that I am missing something, but don't know
what.
Unicode with aforementioned charset (NLS_NCHAR_CHARACTERSET AL16UTF16) uses I think exactly
two bytes per character. However you need 3 bytes to save some characters (codepoints)
representable in this encoding when you want to reencode it in utf-8. So Oracle shouldn't allow
(32767 /2) chars to be assigned to varchar2, because it can't guarantee that it will map all
characters within NCLOB to utf-8 varchar2 with 2 or less bytes. Which is demonstrated in NCLOB with
Kanji section.
Looks like Oracle takes the "fast and rather safe" way of allowing only (32768 - 1) / 2 chars to be
assigned and relies on other mechanisms (buffer overflow check) to prevent the bad from happen.
OK
July 9, 2009 - 9am Central time zone
Reviewer: Kumar from Pune,India
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?
Followup July 14, 2009 - 2pm Central time zone:
... Can't a varchar2 value be added to a clob variable? ...
it could be pre-pended or appended - you don't really "add" strings.
and you still have the 32k limit - so it would not matter.
and the type of input to native dynamic sql in 10g and before is a varchar2, not a clob.
ops$tkyte%ORA10GR2> declare
2 l_clob clob := 'select * from dual';
3 c sys_refcursor;
4 begin
5 open c for l_clob;
6 end;
7 /
open c for l_clob;
*
ERROR at line 5:
ORA-06550: line 5, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored
ops$tkyte%ORA10GR2> declare
2 l_clob varchar2(100) := 'select * from dual';
3 c sys_refcursor;
4 begin
5 open c for l_clob;
6 end;
7 /
PL/SQL procedure successfully completed.
and the result of clob || varchar2 is going to be a clob. you could to_char() it
ops$tkyte%ORA10GR2> declare
2 l_clob clob := 'select * from dual';
3 l_varchar2 varchar2(100) := ' xxx';
4 c sys_refcursor;
5 begin
6 open c for to_char(l_clob||l_varchar2);
7 end;
8 /
PL/SQL procedure successfully completed.
In 11g -clobs are OK with native dynamic sql.
|