3 more methods
November 28, 2001 - 2pm Central time zone
Reviewer: Mikito Harakiri
1. http://www.oracle.com/oramag/code/tips2001/index.html?052001.html Pitfalls: slower than 2 and 3
2. http://www.oracle.com/oramag/code/tips2001/index.html?102801.html Essentially, this technique can be reduced to writing a package with a variable that accumulates
the aggregated value ordering the records correctly and firing a method that calculates the
aggregate. No need in ROLLUP or GROUPING.
3. 9i:
SELECT deptno, CURSOR(SELECT * FROM emp where deptno=dept.deptno) nested_TABLE
FROM dept;
-- User defined agregate through pipelined function
CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
RETURN VARCHAR2 IS
ret VARCHAR2(32000);
tmp VARCHAR2(4000);
BEGIN
loop
fetch cur into tmp;
exit when cur%NOTFOUND;
ret := ret || ',' || tmp;
end loop;
RETURN ret;
END;
/
SELECT distinct
deptno,
SUBSTR(CONCAT_LIST(CURSOR(
SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
)),1,40) employees
FROM emp e;
Followup November 28, 2001 - 2pm Central time zone:
Or, even better, in 9i -- use a user defined aggregate so you don't have run the concat_list
function once per row and then through out the dups (it would be better to write:
select deptno, SUBSTR(CONCAT_LIST(CURSOR(
SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
)),1,40) employees
from ( select distinct deptno from emp );
)
But a good general purpose solution is:
scott@ORA9I.WORLD> create or replace type StringAggType as object
2 (
3 theString varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT StringAggType )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT StringAggType ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN StringAggType,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT StringAggType,
22 ctx2 IN StringAggType)
23 return number
24 );
25 /
Type created.
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> create or replace type body StringAggType
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
5 return number
6 is
7 begin
8 sctx := StringAggType( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT StringAggType,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.theString := self.theString || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN StringAggType,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT StringAggType,
32 ctx2 IN StringAggType)
33 return number
34 is
35 begin
36 self.theString := self.theString || ',' || ctx2.theString;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /
Type body created.
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> CREATE or replace
2 FUNCTION stringAgg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING StringAggType;
5 /
Function created.
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> column enames format a30
scott@ORA9I.WORLD> select deptno, stringAgg(ename) enames
2 from emp
3 group by deptno
4 /
DEPTNO ENAMES
---------- ------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAME
S,WARD
scott@ORA9I.WORLD>
Final Question About transposing
March 7, 2002 - 2am Central time zone
Reviewer: Edgars from Latvia
Hello Tom!
This article is very useful, but I think there is one thing that could be added to it. Is there a
way to fully transpose table? For example suppose I have a table like this:
create table ts1( tm date, val float, descr varchar(5) );
And values like:
insert into ts1 values ( to_date( '01.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '01.01.2001.', 'DD.MM.YYYY.' ), -2, 'P2' );
insert into ts1 values ( to_date( '01.01.2001.', 'DD.MM.YYYY.' ), -3, 'P3' );
insert into ts1 values ( to_date( '02.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '02.01.2001.', 'DD.MM.YYYY.' ), -2, 'P2' );
insert into ts1 values ( to_date( '02.01.2001.', 'DD.MM.YYYY.' ), -3, 'P3' );
insert into ts1 values ( to_date( '03.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '03.01.2001.', 'DD.MM.YYYY.' ), -2, 'P3' );
insert into ts1 values ( to_date( '04.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '05.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '05.01.2001.', 'DD.MM.YYYY.' ), -2, 'P2' );
insert into ts1 values ( to_date( '05.01.2001.', 'DD.MM.YYYY.' ), -3, 'P3' );
All I need is to get a table like this:
TM P1 P2 P3
01.01.2001. -1 -2 -3
02.01.2001. -1 -2 -3
03.01.2001. -1 NULL -3
04.01.2001. -1 NULL NULL
05.01.2001. -1 -2 -3
Can this be done internally in oracle? For now I use a crosstable to achieve this. Thank you.
Followup March 7, 2002 - 7am Central time zone:
See
...
If you know the range of values for the column you wish to "list", you should
use DECODE(). An example of using decode to do this is at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:124812348063
Now, I will show 2 more ways to do it by calling PLSQL from sql. The ......
That shows (given you know p1, p2, p3) how to use decode to do that.
Followup to my previous comment
March 7, 2002 - 2am Central time zone
Reviewer: Edgars from Latvia
Hello Tom!
What I need in my comment can be achieved with decode if I know exectly values for descr (distinct
descr). But what should I do when I don't know what distinct values are there?
Thank you!
Followup March 7, 2002 - 8am Central time zone:
A SQL query *must* have a fixed number of columns (its one of the rules).
So, if you want to transpose in SQL, you need to know the columns in advance. If you didn't know
the domain of descr was (P1, P2, P3) -- you would not know the number of columns -- hence you could
not write the SQL (you could return a list like above, you could return a cursor for each row or
you could use a collection type and return a single column that was in fact an array of values, eg:
scott@ORA817DEV.US.ORACLE.COM> select tm, CURSOR( select descr, val from ts1 x where x.tm = ts1.tm
)
2 from ( select distinct tm
3 from ts1 ) ts1
4 /
TM CURSOR(SELECTDESCR,V
--------- --------------------
01-JAN-01 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
DESCR VAL
----- ----------
P1 -1
P2 -2
P3 -3
02-JAN-01 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
DESCR VAL
----- ----------
P1 -1
P2 -2
P3 -3
03-JAN-01 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
DESCR VAL
----- ----------
P1 -1
P3 -2
04-JAN-01 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
DESCR VAL
----- ----------
P1 -1
05-JAN-01 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
DESCR VAL
----- ----------
P1 -1
P2 -2
P3 -3
scott@ORA817DEV.US.ORACLE.COM>
Here you get a cursor for each row -- so there were only 5 rows returned -- each with a result set
contained therein.
Also, if you have my book -- I have a routine in there that dynamically builds a query that does
the transpose without having to know the domain up front. The stored procedure returns a ref
cursor (result set) that is the transposed data.
Function to concatenate output
October 23, 2002 - 12pm Central time zone
Reviewer: A reader
hi tom,
I don't know if this question will fit in here
because the question was for 8i and then 9i discussion started up.
but I want to know in the 8.1.7 db.
I have a pl/sql record consist of 5 field
and I have declared a type
v_rec new_data%rowtype;
I can access the elements by v_rec.field1..
but I want to convert the v_rece obj. in a string.
varchar2(2000) v_rec_str := fn (v_rec);
should move
v_rec.field1||v_rec.field2||.....
do we have any type casing function that will do this
or Ihave to manually
concatinate all the fields
convert to_cher if numeber
and assign to a varchar variable ?
Followup October 23, 2002 - 2pm Central time zone:
manually concatenate, yes.
very helpful..thanks Tom.
November 7, 2002 - 7pm Central time zone
Reviewer: A reader
Aggregate Function in Package
June 26, 2003 - 8am Central time zone
Reviewer: Jan from Europe
Hi Tom,
I want to create my agg. function, it works for a standalone function, but not for the packaged
one. What is the syntax?
Thanks
Followup June 26, 2003 - 9am Central time zone:
It does not appear to be supported -- the aggregate map is a simple standalong function only.
August 1, 2003 - 11am Central time zone
Reviewer: Carsten Laube from Germany
Hi Tom,
the stringaggtype is really smart. You can handle the stringagg-function like sum() or count().
Thanks a lot!
XMLTransform and XMLAgg [mis]use :-)
September 25, 2003 - 10am Central time zone
Reviewer: msc
What a wonderful world - Oracle 9.2 includes XMLAgg() and XMLTransform():
select rtrim( extractvalue
( XMLTransform
( XMLElement( "tables", XMLAgg( XMLElement( "table", TABLE_NAME)))
, XMLTYPE( '<xsl:stylesheet version="1.0" xmlns:xsl=" http://www.w3.org/1999/XSL/Transform" >
<xsl:template
match="tables"><result><xsl:apply-templates/></result></xsl:template>
<xsl:template match="table"><xsl:apply-templates/>, </xsl:template>
</xsl:stylesheet> ')), '/result' ),', ')
from USER_TABLES
where rownum < 10;
PS 2 Tom: is it possible to disable URL transformation into hyperlink in review text? At least
preview shows xmlns:xsl URL trsansformed to active hyperlink...
Followup September 25, 2003 - 11pm Central time zone:
put a space in the http -- make it be h ttp or something. i just look for http://
Please type PL/SQL in text editor.
January 21, 2004 - 3am Central time zone
Reviewer: Antony Paul from India
I am finding this site very useful. Usually I ask questions at OTN forums and people provide links
to this page. I have a request to you if possible type in the PL/SQL code in text editor and copy
this to the site instead of that from SQL Plus which contains line numbers and causes difficulty
incopy & paste.
Followup January 21, 2004 - 6am Central time zone:
sorry -- cut and paste is the ONLY way to do it properly. It is the only way to avoid mistakes.
I use this all of the time myself. using my favorite editor vi, it takes exactly two commands to
"fix the code"
:1,$s/.*WORLD>/xxxx/
:1,$s/.....//
You should have some equivalent in your text editor. It takes me about 2 seconds to go from this
page to something I can re-run in sqlplus.
Without using a cut and paste from sqlplus, you would not get my narrative inlined with the output
-- you'd see a script, followed by a ton of output and have to page up and down. i don't think it
is too hard to perform two simple edits to re-run the code.
Please type PL/SQL in text editor
January 22, 2004 - 4am Central time zone
Reviewer: Antony Paul
I learned regular expressions to remove numberings and now using it in a text editor to remove
numberings and then copy to SQL Plus.
Antony Paul
Tom doesn't listen to his users
January 23, 2004 - 5pm Central time zone
Reviewer: Mikito Harakiri
1. Not everybody uses vi.
2. Not every editor has regular expressions in it.
3. SQLPlus is the client that gives a bad reputation to oracle and oracle GUI development
abilities, in particular. See http://troels.arvin.dk/db/rdbms/links/ "Annoying Oracle".
Followup January 23, 2004 - 6pm Central time zone:
Mikito -- the contrarian.
anyway -- assume I didn't listen to them. Hmmm, would this place even exist? Rhetorical question,
of course it wouldn't. I listen to them all day, pretty much all day.
Maybe you didn't listen to me -- the answers would NOT make sense unless they were in fact cut and
pastes. Think about it. it would just be a bunch of code -- no results, no output from sqlplus.
If -- IF -- you cannot figure out how to get your editor to do those two global changes -- THEN --
well, I feel somewhat "sorry for you".
You either have the worst editor on the planet or..... well, I won't say the or outloud.
Tell me this Mr. Harakiri -- if it were not for sqlplus -- how, how the heck would this site even
exist. Tell me that. I should have 5 meg pages with screen shots of CHARACTER MODE DATA???
funny thing is, people keep emailing me junk like that. screen shots of their windoze screen -- 1,
2, 3,... N megabytes big -- think nothing of it. Kills me when they do that (not that I asked them
to, they just send it)
I find sqlplus to be the most enjoyable of environments.
If it were not for the character mode sqlplus -- this site would not exist.
If it were not for the character mode sqlplus -- my books would not be very readable. full of
"pretty pictures" no doubt but not as good.
If you don't like it -- well, guess you don't have to come here and read.
Think about it -- if I used a gui -- you wouldn't even be able to cut and paste it would you.
Nope, not really.
You should be saying "thank goodness for this simple, yet complete tool"
Tom's Miracle Ear
January 23, 2004 - 8pm Central time zone
Reviewer: Mark A. Williams from Indianapolis, IN USA
> 1. Not everybody uses vi.
True, but I found no place where Tom claimed this to be true - only that it was his favorite
editor.
> 2. Not every editor has regular expressions in it.
True, including the "alternative" from the link you provided. But surely line numbers can be
removed without using regular expressions? It may be easier to do with regular expressions, but
not required. Besides, most of Tom's examples are not 500 lines long.
> 3. SQLPlus is the client that gives a bad reputation to oracle...
http://troels.arvin.dk/db/rdbms/links/
A link to a page on the site of a guy who is a student at "IT University of Copenhagen", says
"PostgreSQL - my favorite, because of the nice feature set", and doesn't like sql plus (listing a
java/jdbc utility as an alternative)... I have to say that my experience with Oracle has been
(apparently) radically different from that of Mr. Arvins. Different strokes for different folks I
guess. However, saying that Tom doesn't listen to his users is plain silly in my opinion. I
believe he has been rather indulgent in your case in fact. I do find myself wondering what keeps
bringing you back, but, like I said earlier, different strokes for different folks.
- Mark
sql*plus from my point of view ...
January 24, 2004 - 6am Central time zone
Reviewer: j.
... after reading tom's books and customizing sql*plus the way he described it became my tool of
choice whenever i've to do things like spooling and tracing and such.
i prefer it to sql navigator for instance since sql*plus does *NOT* parse a statement *TWICE* for
executing it just *ONCE* ... ;o)
Re: Tom's Miracle Ear
January 24, 2004 - 8am Central time zone
Reviewer: Troels Arvin from Copenhagen, Denmark
Mark, what are you getting at?
What am i getting at...
January 24, 2004 - 11am Central time zone
Reviewer: Mark A. Williams from Indianapolis, IN USA
Hi Troels,
I did not do a very good job of just trying to point out that you have a certain viewpoint or
perspective that may (or probably) is different from the "typical" Oracle user or reader of AskTom.
I felt that your perspective should be taken into consideration when evaluating your statements,
etc., just as mine should, Tom's should, etc.
I guess my main confusion was that I don't see how your feelings about sql plus really substantiate
mikito's claim that "SQLPlus is the client that gives a bad reputation to oracle and oracle GUI
development abilities, in particular." Some of the things you mention like command line completion
are not important to me, but perhaps they are to other people. So, for those people, they may be
biased against sql plus from the start is my point stated differently perhaps. If I provided a
link to a page on a person's site that said sql plus is the greatest tool in the whole world, would
that be effective? I see that as being no more effective than a link to a page on a site that says
sql plus is terrible.
As I said in my other post, I believe your experiences with Oracle have been different from mine.
As a very pro-Oracle person, I do realize that not everyone has the same perspective as me. That
should be taken into account as well.
In any case, I do hope that I did not cause any offense as certainly none was intended. I
apoligize for my lack of clarity which probably has not been further focused by this post.
- Mark
What century do you guys live?
January 26, 2004 - 12pm Central time zone
Reviewer: Mikito Harakiri
I don't know what to say. A relational DBMS manages data in the relational data model, whose
primary construct is the table. And you advocate the client that cannot even properly render a
table???
Followup January 26, 2004 - 1pm Central time zone:
huh?
I don't know what to say to your not knowing what to say since I've not any clue what you mean by
"render a table".
If you want a spreadsheet -- have fun with excel.
If you cannot figure out how to cut and paste and use your editor to fix up spoon fed, complete
examples -- well, all I can say is "sorry, guess the gui has made the brain goto mush".
Just think, I could post pictures of a really pretty gui. Would that not be sooooo 2004 and hip.
It would also make each page 50meg in size and sort of obviate this entire discussion since cutting
and pasting would just be "so not happening"
I advocate people use the tools they like.
I like sqlplus
You do not have to like sqlplus. (hey -- you don't even like analytics, not "pure enough" for you
- circular arguments here Mikito, you like what you like -- i obviously like what I like)
whatever, last followup i'll make on this. have a nice day.
ordering stringagg output
March 15, 2004 - 7pm Central time zone
Reviewer: bob from PA
Tom,
I hope you still have energy to follow up on a real question/comment. Using StringAgg, how can I
order the concatenation so that it occurs alphabetically?
Followup March 15, 2004 - 7pm Central time zone:
You'd have to "save up" the inputs and then sort them and then concatenate them. they come in
random order to stragg...
more ordering stringagg output
April 22, 2004 - 8pm Central time zone
Reviewer: dusty from Fairbanks, AK USA
The followup was concatenated!
Followup:
You'd have to "save up" the inputs and then sort them and then concatenate them.
they come in random order to stragg...
Fill in the blanks, please!
Also, I need to order by things that aren't in the string, ie,
select .....
strAgg(collector_name
[ordered by collector_order, a numeric value in table collector]),
strAgg(part_name [ordered by part_names, alphabetically]).....
Removing Line numbers in sqlplus
May 6, 2004 - 7pm Central time zone
Reviewer: dharma from CA USA
You have used vi and showed us how to remove the line numbers which come from the cut and paste
I tried using
:1,$s/.*COM>/xxxx/
and it shows no changes to make since it cant find the pattern .*COM> if I am right. I copied the
above command from one of your answers, checked the one above also.
I couldnt get any further. What am I doing wrong or what should I do? Still learning vi :)
Thanks a lot,
-dharma.
Followup May 7, 2004 - 7am Central time zone:
well, look at the prompt I used in the example
.*COM>
won't match:
ask_tom@OSI1.WORLD> create table t
2 ( a varchar2(25),
3 b varchar2(25)
4 );
(obviously -- .*RLD> would match....)
but in another place on this page:
scott@ORA817DEV.US.ORACLE.COM> select tm, CURSOR( select descr, val from ts1 x
where x.tm = ts1.tm )
2 from ( select distinct tm
3 from ts1 ) ts1
4 /
it would match (.*COM> would)
basically you are taking the sqlplus prompt and converting it into four xxxx's. Look at the prompt
used and take the last couple of letters and the > sign and use that in the template.
99.9999% of the time, it'll be .*COM> on this site.
Get the most recent
November 19, 2004 - 5pm Central time zone
Reviewer: Patrick from Montreal, QC CAN
Hi Tom, I want to get the most recent record from a table. This table contains two column:
CREATE TABLE TstDate (
Issue varchar(10),
Installed timestamp
);
And here are the values that I add:
INSERT INTO TstDate VALUES ( 'V.1', '1980-01-01 01:00:00.000');
INSERT INTO TstDate VALUES ( 'V.2', '1990-01-01 01:00:00.000');
INSERT INTO TstDate VALUES ( 'V.3', '2000-01-01 01:00:00.000');
INSERT INTO TstDate VALUES ( 'V.1', '1980-01-01 02:00:00.000');
Notice that both issue 'V.1' are not installed at the same time.
And here is the output that I want:
INSTALLED ISSUE
-------------------------------------- ----------
1980-01-01 02:00:00.000000 V.1
1990-01-01 01:00:00.000000 V.2
2000-01-01 01:00:00.000000 V.3
Is there an easy way to achieve that? I tried with MAX() but not successful...
Thanks for your help.
concat
December 13, 2004 - 10am Central time zone
Reviewer: SC
Hi Tom.
create table concat_test (x number(4), y date, z varchar2(5))
/
begin
for i in 1..120000
loop
insert into concat_test values
( dbms_random.value(1,9999), sysdate-dbms_random.value(1,7), dbms_random.string('A',5) );
end loop;
end;
/
commit;
1.select x,y,z
from
concat_test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8001 0.37 0.37 0 8357 0 120000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8003 0.38 0.37 0 8357 0 120000
2. select x||'|'||y||'|'||z
from
concat_test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8001 0.99 0.91 0 8357 0 120000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8003 0.99 0.92 0 8357 0 120000
Why is select 2 slower and consumes more cpu than select 1
Some kind of conversion?
Followup December 13, 2004 - 1pm Central time zone:
it is not obvious?
f(x) is going to be slower than x alone...
not bad -- 5.083333e-6 seconds to concatenate 5 strings together.
Back to String Aggregation topic
April 28, 2005 - 11am Central time zone
Reviewer: Stew Stryker from Hanover, NH USA
NOT to beat a dead horse, but I found all the function solutions pretty complex to implement.
(Maybe I missed one?) If I can humbly offer up my solution for personal scrutiny and/or bashing...
This overloaded, packaged function accepts either a CURSOR or a string representation of the query
as the main argument. The CURSOR version can only be used within the SQL context, while the string
version can also be used in the PL/SQL context.
CREATE OR REPLACE PACKAGE misc_tools is
TYPE single_string_rec_t IS RECORD(
string_val VARCHAR2(256));
TYPE string_ref_cur IS REF CURSOR;
-- Given single-column ref cursor, return values separated by delimiter parameter value
FUNCTION cursor_to_list(
cur_in IN misc_tools.string_ref_cur,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2;
-- Given single-column ref cursor, return values separated by delimiter parameter value
-- Overloaded to accept query string instead of opened cursor
FUNCTION cursor_to_list(
select_query_in IN VARCHAR2,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2;
end misc_tools; /* End of package header specification */
/
CREATE OR REPLACE PACKAGE BODY misc_tools is
FUNCTION cursor_to_list(
cur_in IN misc_tools.string_ref_cur,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2
IS
/*
Description: Given single-column ref cursor, return values separated
by delimiter parameter value.
Trim last trailing delimiter.
Parameters: cur_in - opened cursor w/ single string column result
delimiter_in - separate values w/ this, defaults to comma-space
Returns: String of separated values
Usage: Within a SQL statement, you pass a cursor to this function using
the CURSOR clause, as follows:
SELECT DISTINCT id_number,
misc_tools.cursor_to_list(CURSOR (SELECT
child_id_number
FROM children c
WHERE c.id_number =
p.id_number
ORDER BY child_id_number))
childs
FROM parent p
WHERE p.id_number = '0000000006';
Note that this will ONLY work in the context of a SQL statement.
For use within PL/SQL context, see the other variant below.
*/
row_value single_string_rec_t;
ret_list VARCHAR2(32000);
BEGIN
IF cur_in%ISOPEN
THEN
FETCH cur_in
INTO row_value;
WHILE cur_in%FOUND
LOOP
ret_list := ret_list || row_value.string_val || delimiter_in;
FETCH cur_in
INTO row_value;
END LOOP;
-- Strip last delimiter
ret_list := RTRIM(ret_list, delimiter_in);
CLOSE cur_in;
END IF;
RETURN ret_list;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN ret_list;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('EXCEPTION IN misc_tools.cursor_to_list - '
|| TO_CHAR(SQLCODE)
|| ': '
|| SQLERRM);
RAISE;
RETURN ret_list;
END cursor_to_list;
-- Overloaded to accept query string instead of opened cursor
FUNCTION cursor_to_list(
select_query_in IN VARCHAR2,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2
IS
/*
Description: query string that will be opened dynamically,
return values separated by delimiter parameter value.
Trim last trailing delimiter.
Parameters: select_query_in - SELECT statement, so cursor is created dynamically
delimiter_in - separate values w/ this, defaults to comma-space
Returns: String of separated values
Usage: This version may be called from SQL or PL/SQL contexts.
children_list := misc_tools.cursor_to_list(
select_query_in => 'SELECT empno FROM scott.emp WHERE mgr = ' ||
mgr_id,
delimiter_in => ', '
);
*/
ret_list VARCHAR2(32000);
local_cur string_ref_cur;
BEGIN
OPEN local_cur
FOR select_query_in;
ret_list := cursor_to_list(cur_in => local_cur, delimiter_in => delimiter_in);
RETURN ret_list;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('EXCEPTION IN misc_tools.cursor_to_listV2 - '
|| TO_CHAR(SQLCODE) || ': ' || SQLERRM);
RAISE;
RETURN ret_list;
END cursor_to_list;
END misc_tools;
/
Concat column values in one SQL SELECT statement (without PLSQL)
September 22, 2005 - 9am Central time zone
Reviewer: Denis &#272;uki&#263; from Ljubljana (Slovenija), (Belgrade) Srbija, (Prijedor) BiH
Hi,
here is solution in one SELECT, isn't elegant at all, and have some drawbacks, so ...
CREATE TABLE t ( DATA VARCHAR2(20));
INSERT INTO t SELECT 'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;
SELECT SUBSTR (c, 2) concatenated
FROM (SELECT SYS_CONNECT_BY_PATH (DATA, ',') c, r
FROM (SELECT ROWNUM ID, DATA,
RANK () OVER (ORDER BY ROWID DESC) r
FROM t
ORDER BY DATA)
START WITH ID = 1
CONNECT BY PRIOR ID = ID - 1)
WHERE r = 1;
Concat column values in one SQL SELECT statement (without PLSQL)
September 23, 2005 - 3am Central time zone
Reviewer: Denis &#272;uki&#263; from Ljubljana (Slovenija), (Belgrade) Srbija, (Prijedor) BiH
Hi,
this version work with a views too.
DROP TABLE t;
CREATE TABLE t ( s VARCHAR2(20));
INSERT INTO t SELECT 'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;
SELECT SUBSTR (c, 2) concatenated
FROM (SELECT SYS_CONNECT_BY_PATH (s, ',') c, r
FROM (SELECT RANK () OVER (ORDER BY s ASC) ID, s,
RANK () OVER (ORDER BY s DESC) r
FROM t)
START WITH ID = 1
CONNECT BY PRIOR ID = ID - 1)
WHERE r = 1;
Concat column values in one SQL SELECT statement
September 23, 2005 - 6am Central time zone
Reviewer: Denis &#272;uki&#263; from Ljubljana (Slovenija), (Belgrade) Srbija, (Prijedor) BiH
Find a bug in above version (sorry).
Here:
CREATE TABLE t ( s VARCHAR2(20));
INSERT INTO t SELECT 'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;
SELECT SUBSTR (c, 2) concatenated
FROM (SELECT SYS_CONNECT_BY_PATH (s, ',') c, r
FROM (SELECT ROWNUM ID, s,
RANK () OVER (ORDER BY ROWNUM DESC) r
FROM t
ORDER BY s)
START WITH ID = 1
CONNECT BY PRIOR ID = ID - 1)
WHERE r = 1;
StringAgg and Parallel
October 25, 2005 - 3pm Central time zone
Reviewer: BC from Macomb Twp, MI
Tom,
StringAgg returns multiple comma's when the tables default degree of parallelism is set. Can you
help me resolve this issue ? I copied and pasted the code to create StringAgg from your reply above
in this post.
create table parallel_df
( func_id number(3,0) not null,
value_code varchar2(10)
)
parallel (degree default)
/
insert into parallel_df values(254,'CA');
insert into parallel_df values(254,'MN');
insert into parallel_df values(254,'LA');
insert into parallel_df values(225,'S');
insert into parallel_df values(225,'M');
insert into parallel_df values(225,'L');
insert into parallel_df values(225,'F');
insert into parallel_df values(226,'AP');
insert into parallel_df values(226,'AN');
insert into parallel_df values(226,'GEN');
insert into parallel_df values(226,'SDMD');
insert into parallel_df values(254,'NY');
insert into parallel_df values(254,'ME');
insert into parallel_df values(254,'NJ');
insert into parallel_df values (254,'FL');
commit;
select func_id,
stringagg(value_code)
from parallel_df
group by func_id;
FUNC_ID STRINGAGG(VALUE_CODE)
---------- ------------------------------------
225 S,,L,F,M
226 AN,AP,GEN,SDMD
254 FL,LA,NY,,ME,NJ,,CA,MN
--Notice the extra comma's in the output
alter table parallel_df noparallel;
select func_id,
stringagg(value_code)
from parallel_df
group by func_id;
FUNC_ID STRINGAGG(VALUE_CODE)
---------- -------------------------------------
225 S,M,L,F
226 AP,AN,SDMD,GEN
254 CA,MN,ME,FL,NJ,NY,LA
Your help is highly appreciated.
Thanks
BC
Followup October 26, 2005 - 11am Central time zone:
looks like the merge routine:
36 self.theString := self.theString || ',' || ctx2.theString;
remove the comma - what then?
Awesome
October 26, 2005 - 2pm Central time zone
Reviewer: BC from Macomb Twp, MI
Tom,
Thank you so much for the quick response.
It works fine now, Can you explain why?
member function ODCIAggregateMerge(self IN OUT StringAggType,
ctx2 IN StringAggType)
return number
is
begin
--self.theString := self.theString || ',' || ctx2.theString;
self.theString := self.theString || ctx2.theString;
return ODCIConst.Success;
end;
With parallel (degree default)
FUNC_ID STRINGAGG(VALUE_CODE)
---------- ----------------------------
254 FL,NY,CA,MN,ME,NJ,LA
226 AN,GEN,SDMD,AP
225 S,M,L,F
With noparallel
FUNC_ID STRINGAGG(VALUE_CODE)
---------- ----------------------------
225 S,M,L,F
226 AP,AN,SDMD,GEN
254 CA,MN,ME,FL,NJ,NY,LA
Followup October 27, 2005 - 4am Central time zone:
...
21 member function ODCIAggregateTerminate(self IN StringAggType,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
28 return ODCIConst.Success;
....
because my logic puts an extra ',' on the front of the string, which my terminate trimmed off - but
the merge did not ;)
Thanks
October 27, 2005 - 1pm Central time zone
Reviewer: BC from Macomb Twp, MI
Tom,
Thank you very much for your quick and informative reply.
January 5, 2006 - 12pm Central time zone
Reviewer: A reader
Hi Tom,
I am having a requirement something like this:
I have a database where the dateofbirth values are stored in a varchar column in the format
yyyymmdd. I want the output to come as mm-dd-yyyy. What is the easiest way to do that? Also, I have
the ssn stored as contiguous digits but I want to insert - in between like ###-##-####.
Please help.
Followup January 5, 2006 - 1pm Central time zone:
....
I have a database where the dateofbirth values are stored in a varchar column in
the format yyyymmdd....
no you don't, you have a database full of strings :) there are no dates to be found in that
database. (just ask the optimizer, it'll tell you that, especially when it makes mistakes on
cardinalities... Ask yourself
how many strings between 20001231 20010101
how many DAYS between December 31, 2000 and January 1, 2001
The easiest way to achieve your goal - use a date, it is what you meant to use in the first place.
The hard ways, keep it in a string, then
to_char( to_date( dateofbirth,'yyyymmdd' ), 'mm-dd-yyyy' )
but be prepared to find someone born on the 29th of Feb, 2001 sometime, or someone born on the 12th
day of the 13th month of the year 9999.
Or use substr to substr out the characters and put them back together.
I'd go with a DATE type myself - that way I'd find all of my bad data, fix it once and life would
be good from there on.
As for the SSN, you have a string again. Same discusssion repeated... Substr it.
Great info, Thanks...
July 27, 2006 - 12pm Central time zone
Reviewer: Jim Schwarz from Milwaukee, WI USA
Tom's first post answered my question completely. I was able to apply it immediately to my
situation. Thanks for providing this valuable information in such a timely way. Thanks to those
on the thread who managed to keep it to the point.
Review of concatenating rows
August 2, 2006 - 2pm Central time zone
Reviewer: blanche from colorado
Definitely the solution to my problem (the sys_connect_by_path approach). Much easier in a very
low-volume situation than creating the aggregate function Tom sugggested at the beginning of this
thread.
However, now I need to figure out how to alphabetize the list. My data is a number of rows with
only 1 column, the name of a state (e.g. Maine, Colorado, etc). The list comes out ordered by the
row number. Any suggestions how to order by the state?
Followup August 3, 2006 - 7am Central time zone:
ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select job,
5 ename,
6 row_number() over (partition by job order by ename) rn,
7 count(*) over (partition by job) cnt
8 from emp
9 )
10 select job, ltrim(sys_connect_by_path(ename,','),',') scbp
11 from data
12 where rn = cnt
13 start with rn = 1
14 connect by prior job = job and prior rn = rn-1
15 order by job
16 /
JOB SCBP
--------- ----------------------------------------
ANALYST FORD,SCOTT
CLERK ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN ALLEN,MARTIN,TURNER,WARD
when you assign the row_number to the "group by" columns (the thing you want to pivot on, job in
this example) - simply order by what you want.
Review of concatenating rows, part 2
August 2, 2006 - 3pm Central time zone
Reviewer: blanche from colorado
Urps...I guess I should have provided my code for comment:
SELECT substr(c, 2) state_list
FROM (SELECT sys_connect_by_path(state, ', ') c, r
FROM (SELECT rownum id,
initcap(c.rv_meaning) state,
rank() over(ORDER BY rownum DESC) r
FROM students cad,
student_class_year ccy,
cg_ref_codes c
/*
cg_ref_codes is a table that contains a mapping from the state abbreviation to the full-text name
of the state
*/
WHERE cad.pid = ccy.cad_pid
AND ccy.cyr_year_identifier = '2005'
AND cad.state = c.rv_low_value
AND c.rv_domain = 'STATE CODES'
ORDER BY c.rv_meaning)
START WITH id = 1
CONNECT BY PRIOR id = id - 1)
WHERE r = 1
Review of concatenating rows, the followup
August 3, 2006 - 7pm Central time zone
Reviewer: blanche
Um...if I had 2 columns and were using the partion approach, this would work. But I'm using the
rank() over and must use the rownum to order. In your original example, you're ordering by the
ename. When I try the "order by state" I get one state, not the entire list.
I gotta think some more on this.
Followup August 4, 2006 - 7am Central time zone:
you gotta provide an example to accompany your words too maybe.
If I have N columns, using the partition approach, this would world
You shouldn't be using RANK(), that would not work.
You asked a question - recieved the technique. rank would not be appropriate in the simplified
query I gave there.
so you have a query that returns a list of state names (nothnig to partition by)
You want a sorted list.
easy - forget the three table join (you'll need it for YOUR result, we don't need it for OUR
example)
we just need a source of text right:
(select ename from emp)
pretend that is your query that returns the states.
ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select ename, row_number() over (order by ename) rn, count(*) over () cnt
5 from
6 (
7 select ename
8 from emp
9 )
10 )
11 select sys_connect_by_path(ename, ',') scbp
12 from data
13 where rn = cnt
14 start with rn = 1
15 connect by prior rn = rn-1
16 /
SCBP
-------------------------------------------------------------------------------
,ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER
,WARD
ORA-00938: not enough arguments for function
December 29, 2006 - 7am Central time zone
Reviewer: Khurram from Paksitan
Hi
Tom i dont know is this my question relevant from this thread or not but i tried to google out asktom fro this relevant thread but found only this thread relevant one,I have proble with this query with views,but not with plan table why?How can i overcome on it
Why not ORA-00938: not enough arguments for function with this SQL??
SQL> CONN scott/tiger@takaful
Connected.
SQL> SELECT empno,deptno
2 FROM scott.emp
3 WHERE deptno IN (DECODE(deptno,'10','20','30','30'),'40','50')
4 /
EMPNO DEPTNO
---------- ----------
7521 30
7654 30
7698 30
7844 30
7900 30
SQL> CONN pktcl/pktcl@takaful
Connected.
SQL> DESC vw_ins_outstanding
Name Null? Type
----------------------- -------- ----------------
REC_TYPE VARCHAR2(7)
ASSORTED_CODE VARCHAR2(12)
CLAIM_CODE VARCHAR2(10)
CLAIM_INFO_CODE VARCHAR2(10)
CLASS_CODE VARCHAR2(2)
PRINCIPAL_COMPANY VARCHAR2(1)
INSURANCE_TYPE_CODE VARCHAR2(1)
DUE_AMOUNT NUMBER
DUE_DATE DATE
MODE_CODE VARCHAR2(3)
SEQ_NO NUMBER(28)
COINSURANCE_CODE VARCHAR2(8)
POLICY_CODE VARCHAR2(12)
CHEQUE_NO VARCHAR2(20)
CHEQUE_AMOUNT NUMBER
CHEQUE_DATE DATE
VOUCHER_NO VARCHAR2(20)
VOUCHER_DATE DATE
SUP_BY VARCHAR2(20)
PAID_TO_CODE VARCHAR2(8)
CANCEL_DATE DATE
SUP_DATE DATE
Why ORA-00938: not enough arguments for function with this SQL??
SQL> SELECT mode_code
2 FROM vw_ins_outstanding
3 WHERE MODE_CODE IN (DECODE(PRINCIPAL_COMPANY,'D','24','34'),'31','02')
4 .
SQL> /
WHERE MODE_CODE IN (DECODE(PRINCIPAL_COMPANY,'D','24','34'),'31','02')
*
ERROR at line 3:
ORA-00938: not enough arguments for function
SQL> DISCONNECT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Khurram
Followup December 29, 2006 - 10am Central time zone:
scott%ORA10GR2> SELECT mode_code
2 FROM (select dummy mode_code, dummy principal_company from dual) vw_ins_outstanding
3 WHERE MODE_CODE IN (DECODE(PRINCIPAL_COMPANY,'D','24','34'),'31','02')
4 /
no rows selected
I cannot reproduce - please utilize support.
Amendment
December 29, 2006 - 7am Central time zone
Reviewer: Khurram from Khurram
Sorry
but not with plan table why?
Instead
but not with plain table why?
Khurram
Another way to convert from table to comma
December 29, 2006 - 11am Central time zone
Reviewer: Hirdey from NY USA
Function Fn_Table2Comma(p_cursor in sys_refcursor) return clob as
x dbms_utility.uncl_array;
y BINARY_INTEGER;
z clob;
Begin
Fetch p_cursor bulk collect into x;
dbms_utility.table_to_comma(x, y, z);
return z;
end Fn_Table2Comma;
With 10g also SQL Model Clause may help
January 3, 2007 - 1pm Central time zone
Reviewer: Tonguc from Istanbul, Turkiye
Tested below scripts on Oracle Database 10g Express Edition, Release 10.2.0.1.0;
drop table tbl_tst_mdl_clause purge ;
create table tbl_tst_mdl_clause (id varchar2(2), des varchar2(4), t number);
INSERT INTO tbl_tst_mdl_clause values(¿A',¿a1',10);
INSERT INTO tbl_tst_mdl_clause values(¿A',¿a2',20);
INSERT INTO tbl_tst_mdl_clause values(¿A',¿a3',30);
INSERT INTO tbl_tst_mdl_clause values(¿B',¿a1',40);
INSERT INTO tbl_tst_mdl_clause values(¿B',¿a2',50);
INSERT INTO tbl_tst_mdl_clause values(¿C',¿a3',60);
SELECT * FROM tbl_tst_mdl_clause ;
ID DES T
-- ---- ----------
A a1 10
A a2 20
A a3 30
B a1 40
B a2 50
C a3 60
select distinct i, A1, A2, A3
from tbl_tst_mdl_clause c
model
ignore nav
dimension by(c.id i,c.des d)
measures(c.t t, 0 A1, 0 A2, 0 A3)
rules(
A1[any,any] = t[cv(i),d = 'a1'],
A2[any,any] = t[cv(i),d = 'a2'],
A3[any,any] = t[cv(i),d = 'a3']
);
I A1 A2 A3
-- ---------- ---------- ----------
B 40 50 0
A 10 20 30
C 0 0 60
select distinct d, A, B, C
from tbl_tst_mdl_clause c
model
ignore nav
dimension by(c.id i,c.des d)
measures(c.t t, 0 A, 0 B, 0 C)
rules(
A[any,any] = t[i = `A¿, cv(d)],
B[any,any] = t[i = `B¿, cv(d)],
C[any,any] = t[i = `C¿, cv(d)]
);
D A B C
---- ---------- ---------- ----------
a1 10 40 0
a3 30 0 60
a2 20 50 0
explain plan set statement_id 'tonguc' for
select distinct d, A, B, C
from tbl_tst_mdl_clause c
model
ignore nav
dimension by( c.id i,c.des d)
measures(c.t t, 0 A, 0 B, 0 C)
rules(
A[any,any] = t[i = 'A', cv(d)],
B[any,any] = t[i = 'B', cv(d)],
C[any,any] = t[i = 'C', cv(d)]
);
select plan_table_output from table(dbms_xplan.display(¿plan_table¿,'tonguc¿));
..
| 0 | SELECT STATEMENT | | 6 | 120 | 3 (34)|00:00:01 |
| 1 | HASH UNIQUE | | 6 | 120 | 3 (34)|00:00:01 |
| 2 | SQL MODEL ORDERED | | 6 | 120 | | |
| 3 | TABLE ACCESS FULL| TBL_TST_MDL_CLAUSE | 6 | 120 | 2 (0)|00:00:01 |
..
"Why Use SQL Modeling?" from documenation;
Oracle modeling enables you to perform sophisticated calculations on your data. A typical case is when you want to apply business rules to data and then generate reports. Because Oracle Database integrates modeling calculations into the database, performance and manageability are enhanced significantly.
Like Mr.Kyte always suggests; "do it in a single SQL if possible, analytics rock-and-roll" Like Analytics it is not easy to understand the Model clause, but even when you try to understand I believe a new domain starts with SQL :)
Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)
Chapter 22 SQL for Modeling http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#sthref1855
Best regards,
Tonguc
transpose return size > 4000
January 10, 2007 - 12pm Central time zone
Reviewer: zile liao from NY USA
Hi Tom,
I have read one of your response that to speed up clob operation, you can store clob data in multiple varchar2(4000) rows. If I have implemented that, what would be the easiest/efficient way to retrive the data into an application (i.e. java.lang.String)?
Thanks.
problem with NVARCHAR2 datatype
November 30, 2007 - 1pm Central time zone
Reviewer: Rytis from Lithuania
The concatenate aggregate function returns nothing when working with NVARCHAR2 datatype columns.
ODCIAggregateTerminate seems also doesn't support NVARCHAR2 data type. The oracle connection is
terminated on select execution, after this change:
MEMBER FUNCTION ODCIAggregateTerminate(self IN StringAggType,
returnValue OUT NVARCHAR2,
flags IN NUMBER)
Followup November 30, 2007 - 4pm Central time zone:
if you are getting an ora-600|7445|3113 - please utilize support, that would be not expected.
Does not work with Oracle 11
December 1, 2007 - 5pm Central time zone
Reviewer: rc from The Netherlands
The select statement of Denis from Ljubljana (Slovenija), (Belgrade) Srbija, (Prijedor) BiH (see
above) seems not to work in Oracle 11.
CREATE TABLE t ( s VARCHAR2(20));
INSERT INTO t SELECT 'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;
SELECT SUBSTR (c, 2) concatenated
FROM (SELECT SYS_CONNECT_BY_PATH (s, ',') c, r
FROM (SELECT ROWNUM ID, s,
RANK () OVER (ORDER BY ROWNUM DESC) r
FROM t
ORDER BY s)
START WITH ID = 1
CONNECT BY PRIOR ID = ID - 1)
WHERE r = 1;
no rows selected in Oracle 11!
SQL> select * from v$version;
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
It works fine in Oracle 10!!
Denis himself/herself made that change
December 3, 2007 - 3pm Central time zone
Reviewer: rc from The Netherlands
Denis changed RANK () OVER (ORDER BY ROWID DESC) r
into RANK () OVER (ORDER BY ROWNUM DESC).
His/her first statement used order by rowid desc, the third order by rownum desc.
Maybe some serious testing is needed.
Review in the future
December 3, 2007 - 3pm Central time zone
Reviewer: rc from The Netherlands
I don't get this, Laurent Schneider, has written a review in the future: December 4, 2007 - 3am
US/Eastern. Now the order of the reviews is messed up!
order by rownum desc
December 4, 2007 - 3am Central time zone
Reviewer: Laurent Schneider from Switzerland
RANK () OVER (ORDER BY ROWNUM DESC)
Denis used :
RANK () OVER (ORDER BY ROWID DESC) r
rownum
December 4, 2007 - 5am Central time zone
Reviewer: Laurent Schneider from Switzerland
you are right, but it does not work with rownum...
select current_timestamp at time zone 'US/Eastern' from dual;
CURRENT_TIMESTAMPATTIMEZONE'US/EASTERN'
---------------------------------------
04-DEC-07 05.10.48.910583 AM US/EASTERN
Why not do like this?
August 5, 2008 - 7pm Central time zone
Reviewer: Srini from India
Hi Tom,
I was going through this article. A method was suggested to go by XMLTRANSFORM and get the output in the desired format. Rather doing like that, why not we directly do like this.. (oracle 9i)
select rtrim(replace(replace(xmlagg(xmlelement("t",table_name)),'<t>',''),'</t>',','),',') from
user_tables where rownum <10
Followup August 5, 2008 - 10pm Central time zone:
sure, there are many ways - and that works.
date with constant time
September 24, 2008 - 1pm Central time zone
Reviewer: Anurag from India
Hi Tom
I need to find some data for a day with fixed timings. Basic query like this is failing
select to_date(concat(to_char(to_date('24-sep-2008','dd-mon-yyyy')-1,'dd-mon-yyyy'),'
21:15'),'dd-mon-yyyy') from dual;
with error
ERROR at line 1:
ORA-01830: date format picture ends before converting entire inpuut string
Could you please help.
Thanks in advance
Followup September 24, 2008 - 8pm Central time zone:
select to_date( '24-sep-2008', 'dd-mon-yyyy')-1 + 21/24 + 15/24/60 from dual;
if you look at yours it is:
to_date( '24-sep-2008' ) -1 ----- returns a DATE, the 23rd of sept, 2008
to_char( <that>, 'dd-mon-yyyy' ) ---- returns a string - 23-sep-2008
concat( <that>, ' 21:15' ) --- returns a string 23-sep-2008 21:15
to_date( <that>, 'dd-mon-yyyy' ) -- which fails because you didn't tell us how to convert 21:15!!!!! (date picture of dd-mon-yyyy ended before the string to be converted did)
I would not use your long approach - get the fixed date, add so many hours and minutes to it.
thnx
September 25, 2008 - 3pm Central time zone
Reviewer: anurag from india
Thanks Tom!
I'll check the desired result from my query and will update.
regards,
string of values
October 30, 2008 - 10am Central time zone
Reviewer: DM from IND
Hi Tom
I am writing function to Return the string of values .it is like this ...
FUNCTION Check1 ( i_spec_id IN study_v.sp_id%TYPE,
i_dataset_nm IN dset.dset_nm%TYPE)
RETURN VARCHAR2
IS
l_sql VARCHAR2(4000) := NULL ;
BEGIN
DBMS_OUTPUT.PUT_LINE('l_sql1 = '||NULL);
----Get columns missing in staging table
For i IN(SELECT var_nm from (
SELECT var_nm
FROM spec_ds_v
WHERE dataset_nm = i_dataset_nm
AND spec_id = i_spec_id
MINUS
SELECT column_name
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = i_dataset_nm||'_STAGE' ))
LOOP
l_sql := i.var_nm||',';
END LOOP;
l_sql := rtrim(l_sql,',');
RETURN l_sql;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END Check1;
Tom it is not doing as expected ....Please can you help me with this .....or suggest me a better
way to go for it.
Many thanks in Advance ....
Followup November 2, 2008 - 3pm Central time zone:
EXCEPTION
WHEN OTHERS
THEN
NULL;
END Check1;
I hate your code, that is all.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1277497900346822151
(ctl-f for "i hate" on that page to see why)
I'm not fond of your "detailed explanation" either...
I am writing function to Return the string of values
not very "detailed", you provide code that does not work, but NOT ANY EXPLANATION of what said code is *supposed to do*.
How about this:
I have this following code, it does not work, it is supposed to fargulate the values into garfaks.
an example of non-functioning code is omitted - for the simple reason that it would not help you understand the problem at all - it would only, could only confuse and mislead you.
there is a famous acronym in our industry
GIGO
you just got it.
Function for cancatinating output
December 10, 2008 - 10am Central time zone
Reviewer: shiva from India
Hi Tom,
I have a tables called TALENT and TALENT_SKILL and having a one-many relationship. One of my UI
screen requirement here is to display list of Talents and their comma separated skills.
I wrote a following function:
CREATE OR REPLACE FUNCTION TALENT_SKILLS_FUNC(t_id IN INTEGER) RETURN VARCHAR2 IS
RESULT VARCHAR2(1000);
BEGIN
WITH data AS(
SELECT myvalues,
row_number() OVER(ORDER BY myvalues) rn,
count(*) over() cnt
FROM (SELECT sp.prop_value myvalues
FROM LOOKUP lp,TALENT_SKILL ts
WHERE lp.lookup_id=ts.talent_skill_id
and ts.talent_id=t_id)
)
)
SELECT ltrim(sys_connect_by_path(myvalues, ','), ',') catvalues
INTO result
FROM data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1;
RETURN(result);
END TALENT_SKILLS_FUNC;
I am presently using following SQL query to fulfill my requirement:
SELECT t.talent_id,t.first_nm,t.last_name,TALENT_SKILLS_FUNC(t.talent_id) talent_skills FROM TALENT
t;
But problem here is that, it is taking more time in retriving records. Appreciate,if you suggest me
for any enhancements.
Thanks in advance
Shiva
Followup December 10, 2008 - 1pm Central time zone:
You can just join and do this
ops$tkyte%ORA10GR2> create table talent
2 as
3 select dname tname, deptno pk
4 from scott.dept
5 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table talent_skills
2 as
3 select deptno fk, ename skill
4 from scott.emp
5 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select pk, tname, substr(max(sys_connect_by_path(skill,',')),2) skills
2 from (
3 select t.pk, t.tname, ts.skill, row_number() over (partition by t.pk order by ts.skill) rn
4 from talent t, talent_skills ts
5 where t.pk = ts.fk
6 )
7 start with rn = 1
8 connect by prior pk = pk and prior rn = rn-1
9 group by pk, tname
10 order by tname
11 /
PK TNAME SKILLS
---------- -------------- ----------------------------------------
10 Accounting CLARK,KING,MILLER
20 RESEARCH ADAMS,FORD,JONES,SCOTT,SMITH
30 SALES ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
Concatenate multiple rows into single
December 11, 2008 - 11am Central time zone
Reviewer: Shiva from India
Hi Tom,
Thanks very much for your valuable suggestion
Regards
Shiva
Function for cancatinating output
December 26, 2008 - 2am Central time zone
Reviewer: Shiva from India
Hi Tom,
Based on your useful suggestion I modified the SQL code accordingly. After this modifiation
response time in Searching UI changed from 12 secs to 8 secs. However business analysts expecting a
response time at 2-4 secs. After analysing all the way, I concluded to create a materialized view
with a FAST REFRESH ON COMMIT option. Here is the code I tried:
CREATE MATERIALIZED VIEW talent_skill_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT pk, tname, substr(MAX(sys_connect_by_path(skill,',')),2) skills
FROM (
SELECT t.pk, t.tname, ts.skill, row_number() over (PARTITION BY t.pk ORDER BY ts.skill) rn
FROM talent t, talent_skills ts
WHERE t.pk = ts.fk
)
START WITH rn = 1
CONNECT BY PRIOR pk = pk AND PRIOR rn = rn-1
GROUP BY pk, tname
ORDER BY tname;
I got the following error due to limitations on fast refresh for the Materialized view-
"ORA-12054: Cannot set the ON COMMIT refresh attribute for the materialized view"
Please can you help me with this or suggest me a better
way to go for it.
Many thanks in Advance.
Regards
Shiva
Followup December 29, 2008 - 3pm Central time zone:
why are the business people retrieving ALL OF THE DATA - that doesn't make any sense. More information here - what are you really doing here.
connect_by_isleaf
December 31, 2008 - 3am Central time zone
Reviewer: Mihail Bratu from Bucharest, Romania
Hi Shiva,
Try this variant:
select pk
, tname
, substr(sys_connect_by_path(skill,','), 2) skills
from (
select t.pk
, t.tname
, ts.skill
, row_number() over (partition by t.pk order by ts.skill) rn
from talent t, talent_skills ts
where t.pk = ts.fk
)
where connect_by_isleaf = 1
start with rn = 1
connect by prior pk = pk and prior rn = rn - 1
order by tname
/
Concatenate the output with materialized view
January 21, 2009 - 4am Central time zone
Reviewer: Shiva Ellur from India
Hi Tom,
Here is the business requirement:
User is allowed to search the talents by thier skill. Based on search critiria, result should
display the list of Talents and their comma separated talent skills.
For example:
Search string: Actor
And result would be:
Talent Name Talent Skills
----------- -------------
Shiva Producer,Director,Actor
Tom Manager,Actor
John Actor
Tim Actor,Director
The search result may span across more than one page and also application provides the page
navigation links.
Initially I tried with your suggested SQL code as shown below:
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select pk, tname, substr(max(sys_connect_by_path(skill,',')),2) skills
2 from (
3 select t.pk, t.tname, ts.skill, row_number() over (partition by t.pk order by ts.skill) rn
4 from talent t, talent_skills ts
5 where t.pk = ts.fk
6 )
7 start with rn = 1
8 connect by prior pk = pk and prior rn = rn-1
9 where skills like '%Actor%'
10 group by pk, tname
11 order by tname
12 /
With this query, response time increased from 12 secs to 8 secs. However business users expecting
result should display at the max 2 secs in a Page - 1.
Hence it drived me to create a materializd view with fast refresh method to improve the response
time. However, i was unable to create the materilized view due to limitations with connect clause.
Please can you help me with this or suggest me a better
way to go for it.
Many thanks in Advance.
Regards
-Shiva
Followup January 21, 2009 - 1pm Central time zone:
(I would really suggest you do not use MY PROMPT that makes it look like MY SQL to present your sql...)
... With this query, response time increased from 12 secs to 8 secs. ....
sounds like it decreased.
I asked you before "why are they asking for all of the data"
it appears *they are not*, they only want people that are actors.
You are building the entire hierarchy
and then pruning it
why not let's just build the hierarchy of data you are interested in?
select pk, tname, substr(max(sys_connect_by_path(skill,',')),2) skills
from (
select t.pk, t.tname, ts.skill, row_number() over (partition by t.pk order by ts.skill) rn
from talent t, talent_skills ts
where t.pk = ts.fk
and t.pk in (select fk from talent_skills where skill = 'Actor')
)
start with rn = 1
connect by prior pk = pk and prior rn = rn-1
group by pk, tname
order by tname
/
find the actors
get their skills
pivot them
instead of
get everyone
find all of their skills
pivot them
and only keep the rows such that the pivoted data contains actors
Thanks a lot
March 24, 2009 - 12pm Central time zone
Reviewer: Shiva from India
Thanks very much for valuable feedback. There was a problem with the UI design and now we made a
proper change to the screen. Accordingly we also changed the SQL code to support the need.
Here is the modified SQL code:
select t.pk,stringagg(l.lookup_name) talent_skills from talent t,talent_skill ts,lookup l
where t.pk = ts.fk and
ts.skill_id=l.pk
and t.pk in (select fk from talent_skills where skill_id = ?)
group by t.pk
Thanks once again....
Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"
April 29, 2009 - 1am Central time zone
Reviewer: Matthew from Sydney Australia
Dear Tom,
I liked the solution posted by Srini from India, but found that it works sometimes, whereas other
times it produces an "ORA-19011: Character string buffer too small", when I try to REPLACE the html
tags with commas.
See my SQL statement below. Can you tell me if there is a way to get this REPLACE and XMLAGG
solution working reliably?
Thanks,
Matthew
select c.column_name, c.full_data_type,
xmlagg(xmlelement("x",c.table_name) order by c.table_name),
replace(replace(replace(decode('a','a',xmlagg(xmlelement("x",c.table_name) order by
c.table_name)),
'</x><x>',','),'</x>',null),'<x>',null) list_tables
from all_tables t,
( select i.*,
data_type||'('||
nvl(data_precision, data_length)||
decode(data_scale, null, null, ',')||
data_scale||')' full_data_type
from all_tab_columns i ) c
where c.table_name = t.table_name
group by c.column_name, c.full_data_type
having count(*) > 1
order by 1 desc;
Followup April 29, 2009 - 9am Central time zone:
you are hitting the 4000 byte limit for strings in SQL.
Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"
April 30, 2009 - 1am Central time zone
Reviewer: Matthew from Sydney Australia
Hi Tom,
Thanks for a great site. I used to Google for Oracle SQL solutions, but now I search here first.
I don't mind the 4000 character restriction, I'm just trying to get something that will work
consistently. My guess is that the return from XMLAGG sometimes implicitly converts to VARCHAR2
(<=4000) and other times CLOB (>4000), so I get an error if I assume that it's a CLOB, or a
different error if I assume that it's returning a VARCHAR2.
I've searched the internet for solutions, but haven't found any.
I've added a CASE to the SQL to try and ignore values > 4000 characters. I have three alternatives
for the CASE WHEN condition, see commented SQL below:
1ST TRY - length doesn't seem to handle CLOBs, get's ORA-19011
2ND TRY - dbms_lob.getlength get's ORA-06553, is it complaining that it's not a CLOB?
3RD TRY - to_clob doesn't work either, gets ORA-19011
I'm probably missing something quite simple. Can you tell me what I need to change to make this
SQL run?
select c.column_name, c.full_data_type,
xmlagg(xmlelement("x",c.table_name) order by c.table_name),
-- 1ST TRY --
case when length(xmlagg(xmlelement("x",c.table_name)
order by c.table_name)) > 4000
-- 2ND TRY --
-- case when dbms_lob.getlength(xmlagg(xmlelement("x",c.table_name)
-- order by c.table_name)) > 4000
-- 3RD TRY --
-- case when dbms_lob.getlength(to_clob(xmlagg(xmlelement("x",c.table_name)
-- order by c.table_name))) > 4000
then 'TOO BIG'
else replace(replace(replace(
xmlagg(xmlelement("x",c.table_name)
order by c.table_name),
'</x><x>',','),'</x>',null),'<x>',null)
end list_tables
from all_tables t,
( select i.*,
data_type||'('||
nvl(data_precision, data_length)||
decode(data_scale, null, null, ',')||
data_scale||')' full_data_type
from all_tab_columns i ) c
where c.table_name = t.table_name
group by c.column_name, c.full_data_type
having count(*) > 1
order by 1 desc;
By the way, I'm using Oracle 10g.
Thanks again,
Matthew
Followup April 30, 2009 - 9am Central time zone:
it is not "sometimes" varchar2 - the way it is coded, it is a varchar2.
select c.column_name, c.full_data_type,
xmlagg(xmlelement("x",c.table_name) order by c.table_name) x,
replace(replace(replace(decode('a','a',xmlagg(xmlelement("x", TO_CLOB(c.table_name) ) order
by c.table_name)),
'</x><x>',','),'</x>',null),'<x>',null) list_tables
from all_tables t,
( select i.*,
data_type||'('||
nvl(data_precision, data_length)||
decode(data_scale, null, null, ',')||
data_scale||')' full_data_type
from all_tab_columns i ) c
where c.table_name = t.table_name
group by c.column_name, c.full_data_type
having count(*) > 1
order by 1 desc;
not saying it'll be fast or efficient, but it'll work.
Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"
April 30, 2009 - 8pm Central time zone
Reviewer: Matthew from Sydney Australia
I'm sorry Tom, your SELECT statement is still not working on my database.
To reproduce the problem using only the SYS and SYSTEM schemas, I've revised and simplified the
query a little, with:
- WHERE condition on t.owner
- Removed full_data_type expression
- Shortened GROUP BY to reproduce ORA error in this simplified query.
select substr(c.column_name,1,5),
xmlagg(xmlelement("x",c.table_name) order by c.table_name) x,
replace(replace(replace(decode('a','a',xmlagg(xmlelement("x", TO_CLOB(c.table_name) )
order by c.table_name)),
'</x><x>',','),'</x>',null),'<x>',null) list_tables
from all_tables t, all_tab_columns c
where c.table_name = t.table_name
and t.owner in ('SYS','SYSTEM')
group by substr(c.column_name,1,5)
having count(*) > 1
order by 1 desc;
The above SELECT (including your TO_CLOB) still produces an ORA-19011 error. Can you see how to
get past this error?
Thanks,
Matthew
Followup April 30, 2009 - 9pm Central time zone:
I'll refer you over to otn.oracle.com -> discussion forums. I don't really use this xml stuff (don't see the point 99% of the time) - they might have an idea for you.
or use my stragg that returns a clob.
Comma separated list of table columns
July 2, 2009 - 8am Central time zone
Reviewer: Sabine from Frankfurt, Germany
I used Tom's statement above to retrieve all columns of all tables in my schema as a comma separated list. Maybe someone else finds this useful. (I use it for code generation.)
Best regards,
Sabine
WITH DATA AS (
SELECT table_name, column_name, row_number() over(PARTITION BY table_name ORDER BY column_id) rn,
COUNT(*) over(PARTITION BY table_name) cnt
FROM user_tab_cols
)
SELECT table_name, ltrim(sys_connect_by_path(column_name, ', '), ', ') scbp
FROM data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR table_name = table_name
AND PRIOR rn = rn - 1
ORDER BY table_name
sing XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"
July 22, 2009 - 5pm Central time zone
Reviewer: Jim from Aberdeen, NJ USA
Ok, so this is an ancient thread, and I just stumbled upon it.
Anyway, in response to XMLAgg-based solutions still giving string length errors, I believe the
problem lies in the use of REPLACE(). You need to use a CLOB-happy equivalent.
Follow-up on XMLAgg
July 23, 2009 - 11am Central time zone
Reviewer: Jim from Aberdeen, NJ USA
Correcting my above ^^^
REPLACE() is fine. You just need to do
REPLACE(REPLACE(XMLAgg(...).getclobval(),'<x>',''),'</x>','')) and all is well. Just ran this
myself.
Error in the transpose function
July 30, 2009 - 11am Central time zone
Reviewer: Abhishek Banerjee from USA
Hello Tom,
I am the DBA for an Oracle 9i database (9.2.0.6).
There is a function which is exactly similar to the TRANSPOSE function in this article.
CREATE OR REPLACE function ISISRPT.get_transposed( p_a in varchar2 )
return varchar2
is
l_str varchar2(9000) default null;
l_sep varchar2(5) default null;
begin
for x in ( select reas_cmnt_txt from vw_reas_cmnt where reas_id = p_a ) loop
l_str := l_str || l_sep || x.reas_cmnt_txt;
l_sep := ';';
end loop;
return l_str;
end;
/
When I try to run the below query which calls the function, it fails:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ISISRPT.get_transposed", line 27
Could you please help me regarding this?
Thank you,
Abhishek
Followup August 3, 2009 - 5pm Central time zone:
what is line 27 in your code, there is no line 27 in this code
concatenate both sides
December 16, 2009 - 4pm Central time zone
Reviewer: Vinod from Iselin, NJ
create table chassis (chassis varchar2(20), delivery_date date);
insert into chassis values ('A', to_date('20091015', 'yyyymmdd'))
insert into chassis values ('B', to_date('20091115', 'yyyymmdd'))
insert into chassis values ('C', to_date('20091215', 'yyyymmdd'))
insert into chassis values ('D', to_date('20091002', 'yyyymmdd'))
insert into chassis values ('E', to_date('20091110', 'yyyymmdd'))
insert into chassis values ('F', to_date('20091111', 'yyyymmdd'))
insert into chassis values ('G', to_date('20091220', 'yyyymmdd'))
create table charges (charge_code_fk number(12), effective_date date, expiration_date date)
insert into charges values (100, to_date('20090101', 'yyyymmdd'), to_date('20091031', 'yyyymmdd'))
insert into charges values (200, to_date('20090101', 'yyyymmdd'), to_date('20091130', 'yyyymmdd'))
insert into charges values (300, to_date('20090101', 'yyyymmdd'), to_date('20091130', 'yyyymmdd'))
insert into charges values (400, to_date('20090101', 'yyyymmdd'), to_date('20091231', 'yyyymmdd'))
insert into charges values (500, to_date('20090101', 'yyyymmdd'), to_date('20091231', 'yyyymmdd'))
insert into charges values (600, to_date('20090101', 'yyyymmdd'), to_date('20091031', 'yyyymmdd'))
Using
with data as (
select chassis,
charge_code_fk ,
row_number() over (partition by chassis order by charge_code_fk) rn,
count(*) over (partition by chassis) cnt
from charges, chassis
where delivery_date between effective_date and expiration_date
)
select chassis, ltrim(sys_connect_by_path(charge_code_fk,','),',') charge_list
from data
where rn = cnt
start with rn = 1
connect by prior chassis = chassis and prior rn = rn-1
order by 2, 1
i can get
CHASSIS CHARGE_LIST
A 100,200,300,400,500,600
D 100,200,300,400,500,600
B 200,300,400,500
E 200,300,400,500
F 200,300,400,500
C 400,500
G 400,500
How can I get to the resultset below (group together chassis which have same charge_code_fks
applicable) just using SQL?
CHASSIS_LIST CHARGE_LIST
A,D 100,200,300,400,500,600
B,E,F 200,300,400,500
C,G 400,500
ps : In actual code, charges and chassis will be PL/SQL tables.
Followup December 17, 2009 - 7am Central time zone:
In 11gR2
ops$tkyte%ORA11GR2> select listagg(chassis,',') within group (order by chassis) chassis_list,
2 charge_list
3 from (
4 with data as (
5 select chassis,
6 charge_code_fk ,
7 row_number() over (partition by chassis order by charge_code_fk) rn,
8 count(*) over (partition by chassis) cnt
9 from charges, chassis
10 where delivery_date between effective_date and expiration_date
11 )
12 select chassis, ltrim(sys_connect_by_path(charge_code_fk,','),',') charge_list
13 from data
14 where rn = cnt
15 start with rn = 1
16 connect by prior chassis = chassis and prior rn = rn-1
17 )
18 group by charge_list
19 order by 2, 1
20 /
CHASSIS_LIST CHARGE_LIST
---------------------------------------- ----------------------------------------
A,D 100,200,300,400,500,600
B,E,F 200,300,400,500
C,G 400,500
prior to that, just use the technique you already used, just add another layer, you want to string up chassis by charge_list.
December 17, 2009 - 8am Central time zone
Reviewer: A reader
Distinct is not allowed in listagg. If we need distinct then stragg is there to help us out.
|