Tuesday, March 23, 2010

String Concatnation

COLLECT function in Oracle 10g

We can use COLLECT function in Oracle 10g to get the same result. This method requires a table type and a function to convert the contents of the table type to a string.

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2
IS
l_string VARCHAR2(32767);
BEGIN
FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
IF i != p_varchar2_tab.FIRST THEN
l_string := l_string
p_delimiter;
END IF;

l_string := l_string

p_varchar2_tab(i);

END LOOP;

RETURN l_string;

END tab_to_string;



The query below shows the COLLECT function in action.

SELECT deptno,
tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM emp
GROUP BY deptno;



DEPTNO EMPLOYEES

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

10 CLARK,KING,MILLER

20 SMITH,JONES,SCOTT,ADAMS,FORD

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



3 rows selected.



LISTAGG Analytic Function in 11g Release 2



The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list.



COLUMN employees FORMAT A50



SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees

FROM emp

GROUP BY deptno;



DEPTNO EMPLOYEES

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

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

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



3 rows selected.



WM_CONCAT Built-in Function



If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you.



COLUMN employees FORMAT A50



SELECT deptno, WM_CONCATE(ename) AS employees

FROM emp

GROUP BY deptno;



DEPTNO EMPLOYEES

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

10 CLARK,KING,MILLER

20 SMITH,FORD,ADAMS,SCOTT,JONES

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

Monday, December 7, 2009

jQuery-Enter key Form Submit

$(document).ready(function(){


$.datepicker.setDefaults({

showOn: 'both',

buttonImageOnly: true,

buttonText: 'Alt text for calbutton',

buttonImage: '/i/themes/sfa/jquery/smoothness/images/calendar.gif', // Calendar image

duration:'fast'

});

$(function() {

$("#P2_CALL_ST").datepicker();

$("#P2_CALL_END").datepicker();

});

// Enterkey Form Submit

$('#wwvFlowForm').keyup(function(e) {

if(e.keyCode == 13) {

//alert('Enter key was pressed.');

fnSearch();

}

});

});