Home » SQL & PL/SQL » SQL & PL/SQL » not worked concat_all function
not worked concat_all function [message #185086] Sun, 30 July 2006 23:43 Go to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member


my oracle version is 8i
I am trying to create concat_all function
but it is giving error


PLS-00103: Encountered the symbol "AGGREGATE" when expecting one
of the following:
; is authid deterministic parallel_enable as

please send me any solution on it
Re: not worked concat_all function [message #185138 is a reply to message #185086] Mon, 31 July 2006 03:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think @maaher is the author of the concat_all function on this forum (although its possible he copied it from elsewhere), and he is on holiday, so don't hold your breath waiting for a response.

It's possible that it uses some 9i functionality (but probably not). If you want to start searching for an answer, look into the Data Cartridge Developers Guide.

Ross Leishman
Re: not worked concat_all function [message #185258 is a reply to message #185138] Mon, 31 July 2006 15:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The original user-defined aggregate concat_all function was by James Padfield and was a variation of Tom Kyte's user-defined aggregate stragg function. James Padfield's version used an object as an input parameter that allowed specification of a delimiter. Oracle version 9i is required to create a user-defined aggregate function.

Here is a link to one of the places where the original concat_all function was posted:

http://www.orafaq.com/forum/m/34541/43710/?srch=concat_all#msg_34541

There have been various modifications made since then, such as providing for ordering.

Here is a demonstration of an alternate method for 8i that I just wrote:

SCOTT@10gXE> CREATE OR REPLACE FUNCTION concat_all_8i
  2    (p_columns_to_concat  IN VARCHAR2,
  3  	p_table_names	     IN VARCHAR2,
  4  	p_pivot_columns      IN VARCHAR2,
  5  	p_pivot_values	     IN VARCHAR2,
  6  	p_delimiter	     IN VARCHAR2 DEFAULT ', ',
  7  	p_where_clause	     IN VARCHAR2 DEFAULT '1=1',
  8  	p_order_by	     IN VARCHAR2 DEFAULT NULL)
  9    RETURN VARCHAR2
 10  AS
 11    TYPE ref_cursor_type  IS REF CURSOR;
 12    v_ref_cursor_variable	ref_cursor_type;
 13    v_value_to_concat	VARCHAR2 (4000);
 14    v_concatenated_values	VARCHAR2 (4000);
 15  BEGIN
 16    OPEN v_ref_cursor_variable FOR
 17  	   'SELECT '   || p_columns_to_concat
 18    || ' FROM '     || p_table_names
 19    || ' WHERE '    || p_pivot_columns || ' = :b_pivot_values'
 20    || ' AND ' || p_where_clause
 21    || ' ORDER BY ' || NVL (p_order_by, p_columns_to_concat)
 22    USING p_pivot_values;
 23    LOOP
 24  	 FETCH v_ref_cursor_variable INTO v_value_to_concat;
 25  	 EXIT WHEN v_ref_cursor_variable%NOTFOUND;
 26  	 v_concatenated_values := v_concatenated_values || p_delimiter || v_value_to_concat;
 27    END LOOP;
 28    CLOSE v_ref_cursor_variable;
 29    RETURN LTRIM (v_concatenated_values, p_delimiter);
 30  END concat_all_8i;
 31  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> COLUMN employees FORMAT A60 WORD_WRAPPED
SCOTT@10gXE> SELECT deptno,
  2  	    concat_all_8i ('ename', 'emp', 'deptno', deptno) AS employees
  3  FROM   emp
  4  GROUP  BY deptno
  5  ORDER  BY deptno
  6  /

    DEPTNO EMPLOYEES
---------- ------------------------------------------------------------
        10 CLARK, KING, MILLER
        20 ADAMS, FORD, JONES, SCOTT, SMITH
        30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

SCOTT@10gXE> COLUMN "JOBS AND EMPLOYEES" FORMAT A60 WORD_WRAPPED
SCOTT@10gXE> SELECT deptno,
  2  	    concat_all_8i
  3  	      ('job || ''-'' || ename',
  4  	       'emp',
  5  	       'deptno',
  6  	       deptno) AS "JOBS AND EMPLOYEES"
  7  FROM   emp
  8  GROUP  BY deptno
  9  ORDER  BY deptno
 10  /

    DEPTNO JOBS AND EMPLOYEES
---------- ------------------------------------------------------------
        10 CLERK-MILLER, MANAGER-CLARK, PRESIDENT-KING
        20 ANALYST-FORD, ANALYST-SCOTT, CLERK-ADAMS, CLERK-SMITH,
           MANAGER-JONES

        30 CLERK-JAMES, MANAGER-BLAKE, SALESMAN-ALLEN, SALESMAN-MARTIN,
           SALESMAN-TURNER, SALESMAN-WARD


SCOTT@10gXE> COLUMN "DEPARTMENTS AND JOBS" FORMAT A20 WORD_WRAPPED
SCOTT@10gXE> SELECT deptno || ' ' || job AS "DEPARTMENTS AND JOBS",
  2  	    concat_all_8i
  3  	      ('ename || ''-'' || empno',
  4  	       'emp',
  5  	       'deptno || '' '' || job',
  6  	       deptno || ' ' || job) AS employees
  7  FROM   emp
  8  GROUP  BY deptno || ' ' || job
  9  ORDER  BY deptno || ' ' || job
 10  /

DEPARTMENTS AND JOBS EMPLOYEES
-------------------- ------------------------------------------------------------
10 CLERK             MILLER-7934
10 MANAGER           CLARK-7782
10 PRESIDENT         KING-7839
20 ANALYST           FORD-7902, SCOTT-7788
20 CLERK             ADAMS-7876, SMITH-7369
20 MANAGER           JONES-7566
30 CLERK             JAMES-7900
30 MANAGER           BLAKE-7698
30 SALESMAN          ALLEN-7499, MARTIN-7654, TURNER-7844, WARD-7521

9 rows selected.

SCOTT@10gXE> SELECT deptno || ' ' || job AS "DEPARTMENTS AND JOBS",
  2  	    concat_all_8i
  3  	      ('empno || ''-'' || ename',
  4  	       'emp',
  5  	       'deptno || '' '' || job',
  6  	       deptno || ' ' || job) AS employees
  7  FROM   emp
  8  GROUP  BY deptno || ' ' || job
  9  ORDER  BY deptno || ' ' || job
 10  /

DEPARTMENTS AND JOBS EMPLOYEES
-------------------- ------------------------------------------------------------
10 CLERK             7934-MILLER
10 MANAGER           7782-CLARK
10 PRESIDENT         7839-KING
20 ANALYST           7788-SCOTT, 7902-FORD
20 CLERK             7369-SMITH, 7876-ADAMS
20 MANAGER           7566-JONES
30 CLERK             7900-JAMES
30 MANAGER           7698-BLAKE
30 SALESMAN          7499-ALLEN, 7521-WARD, 7654-MARTIN, 7844-TURNER

9 rows selected.

SCOTT@10gXE> COLUMN "DEPARTMENTS AND EMPLOYEES" FORMAT A60 WORD_WRAPPED
SCOTT@10gXE> SELECT e.job,
  2  	    concat_all_8i
  3  	      ('d.dname || ''-'' || e.ename',
  4  	       'emp e, dept d',
  5  	       'e.job',
  6  	       e.job,
  7  	       ', ',
  8  	       'd.deptno=e.deptno') AS "DEPARTMENTS AND EMPLOYEES"
  9  FROM   emp e, dept d
 10  WHERE  d.deptno = e.deptno
 11  GROUP  BY e.job
 12  ORDER  BY e.job
 13  /

JOB       DEPARTMENTS AND EMPLOYEES
--------- ------------------------------------------------------------
ANALYST   RESEARCH-FORD, RESEARCH-SCOTT
CLERK     ACCOUNTING-MILLER, RESEARCH-ADAMS, RESEARCH-SMITH,
          SALES-JAMES

MANAGER   ACCOUNTING-CLARK, RESEARCH-JONES, SALES-BLAKE
PRESIDENT ACCOUNTING-KING
SALESMAN  SALES-ALLEN, SALES-MARTIN, SALES-TURNER, SALES-WARD

SCOTT@10gXE> 





Re: not worked concat_all function [message #186219 is a reply to message #185258] Mon, 07 August 2006 02:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Barbara Boehmer wrote on Mon, 31 July 2006 22:33

The original user-defined aggregate concat_all function was by James Padfield and was a variation of Tom Kyte's user-defined aggregate stragg function. James Padfield's version used an object as an input parameter that allowed specification of a delimiter. Oracle version 9i is required to create a user-defined aggregate function.
You are entirely right, Barbara. I never claimed ownership of concat_all. I first encountered it at asktom.oracle.com and it was posted by J. Padfield. I'm still grateful to him for posting it. I have used it on several occasions and it works like a charm.

MHE
icon7.gif  Re: not worked concat_all function [message #186509 is a reply to message #185258] Tue, 08 August 2006 05:08 Go to previous message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
hi barbar

thanx a lot

it is working ........

thanx again
Previous Topic: LIKE with variable
Next Topic: alter table drop column
Goto Forum:
  


Current Time: Fri Dec 09 19:23:44 CST 2016

Total time taken to generate the page: 0.06310 seconds