SCHEMA OVERVIEW. Purpose: Account that allows HTTP access to Oracle XML DB. It is used in place of the APEX_PUBLIC_USER account when the Embedded PL/SQL. Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is a multi-model database management system produced and marketed by Oracle.
DBA Tips Archive for Oracle. Install Oracle Database 1. Oracle default password. The final column also includes an SQL statement that can be used to install the list.
I had found a couple reference sites, but had no luck enabling it. I ended up writing my own function to handle the concatenation.
CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR, sep Varchar2 ) RETURN VARCHAR2 IS ret VARCHAR2(32000); tmp VARCHAR2(4000); BEGIN loop fetch cur into tmp; exit when cur%NOTFOUND; if ret is null then ret:= tmp; else ret:= ret sep tmp; end if; end loop; RETURN ret; END;/ Then it can be called as SELECT distinct CONCAT_LIST(CURSOR(SELECT id FROM test_table1), ',') test_table1 FROM dual. I did some load testing on both functions, CONCAT_LIST and STRING_AGG and found I had to make one modification to my function. I forgot to close the cursor and I maxed out the allowed amount rather quickly. After making the change I created a query that grouped a set of 250 shops to their corresponding type. The CONCAT_LIST completed in 2.68 seconds and the STRING_AGG from oracle-base completed in 0.38 seconds and looks a lot cleaner. I have changed the few queries I had to reference this new function.
It is recommended not to use WM_CONCAT since it is an undocumented feature and it has been removed from 12c version. See If you are on 11gR2 and up, use LISTAGG. For version prior to 11g where LISTAGG is not supported, you could use ROW_NUMBER() and SYS_CONNECT_BY_PATH functions. For example, SELECT deptno, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees FROM (SELECT deptno, ename, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev FROM emp) GROUP BY deptno CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno START WITH curr = 1; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected.
Comments are closed.