Did you know there is a new computation type in APEX - SQL Query (return colon separated value)? Probably not, because there are many other "sexier" features. However this "small" feature is realy cool. Before, you needed to do several steps if you wanted to compute your multiselect list, shuttle item, checkbox or a radio button:
1. DECLARE an array,
2. SELECT * BULK COLLECT INTO array
3. use APEX_UTIL package to convert that array into a string
4. RETURN that string
With APEX 4 you don't need to do that any more. You just do a normal select and whatever it returns will be concatenated into a colon delimited string. That's it.
11 comments:
I don't think this is that big a deal that builtin support via new computation type is necessary.
It is a simple matter of string aggregation and there are many techniques available for it, see http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
Since Apex 4.0 needs at least Oracle 10g, wm_concat() seems like the easiest solution.
No, this is definitely not a big deal - it is simply anoying. Every time you have a multiselect element you need to do an excercise in all the stuff posted on that web page. ;)
Denes Kubicek
Come now, is a computation using the new type and a query like "select empno from emp where mgr is null" any easier than a query like "select replace(wm_concat(empno),',',':') from emp where mgr is null", it is just syntax, no need for the Apex team to build special support into the product when a database/language feature already does the same thing.
To me it seems easier since I do not need to use the WM_CONCAT function frequently - there are so many other functions in Oracle and I remember only those which I use regularly.
Following your logic, APEX would not need a lot of other things either. Finaly, why would you need APEX at all? Use PL/SQL and htp.p function and you can generate a web page entirely without APEX since it is just syntax. I personally do not agree with that.
Hello Denes,
This is my first comment, so I start by thanking you for all you contribute on the Internet.
For this requirement, 11G R2 introduces the LISTAGG function:
select listagg(empno, ':') within group (order by empno) from emp where mgr is null;
Those who want to concatenate themselves should definitely use this function whenever possible.
I see your point about ease of use within APEX, this is just a heads-up for other environments.
Stew,
the name Application Express suggests "express" solutions. My opinnion is that the APEX Builder should reflect that. With shortcuts like this one id definitely does.
Thanks for getting my point.
Denes Kubicek
When it comes to SQL I have lot of doubts..But when people like you provided such good articles. I have no more doubts. I also need few more posts on querying SQL and reporting.
I was not aware of this feature but after knowing it I am feeling like Oracle is full of mysteries and there is lot more to explore. This feature is really nice one and I am going to try it now as per your instructions. Thanks.
hello.
i've been trying out this feature, as well as many other scripts, but i dont get the same results:( .
like this example, on the left column i show names, and they are associated with IDs. on the right column, with this script what shows up are the IDs, not the names... any help help please?
thanks!
Mark Pereira
Mark,
Example?
Denes
Hello Denes,
i have 2 components on page. a select list that lists all the departements and a shuttle that shows on the left column all the employes that do not belong to a chosen department from the select list and on the right side all the employess that belong to that department.
ok, the code for the shuttle is,
the right column :
select ename, empno from emp
minus
select ename, empno from emp where deptno=:P3_X (P3_X is the name of my select list)
the left column is on source, has:
select empno from emp where deptno=:P3_X (the type chosen is SQL QUERY retury colon separated value)
i need to show, on the right column the departaments names and save the ID. if i change this query to select ename from emp where deptno=:P3_X i'll shiow the names, but it will also save the names...:(
Post a Comment