Add wm_concat to Oracle XE

Source: Internet
Author: User
CREATE OR REPLACE TYPE wm_concat_impl   AUTHID CURRENT_USERAS OBJECT (   curr_str   VARCHAR2 (32767),   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)      RETURN NUMBER,   MEMBER FUNCTION odciaggregateiterate (      SELF   IN OUT   wm_concat_impl,      p1     IN       VARCHAR2   )      RETURN NUMBER,   MEMBER FUNCTION odciaggregateterminate (      SELF          IN       wm_concat_impl,      returnvalue   OUT      VARCHAR2,      flags         IN       NUMBER   )      RETURN NUMBER,   MEMBER FUNCTION odciaggregatemerge (      SELF    IN OUT   wm_concat_impl,      sctx2   IN       wm_concat_impl   )      RETURN NUMBER);/CREATE OR REPLACE TYPE BODY wm_concat_implIS   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)      RETURN NUMBER   IS   BEGIN      sctx := wm_concat_impl (NULL);      RETURN odciconst.success;   END;   MEMBER FUNCTION odciaggregateiterate (      SELF   IN OUT   wm_concat_impl,      p1     IN       VARCHAR2   )      RETURN NUMBER   IS   BEGIN      IF (curr_str IS NOT NULL)      THEN         curr_str := curr_str || ',' || p1;      ELSE         curr_str := p1;      END IF;      RETURN odciconst.success;   END;   MEMBER FUNCTION odciaggregateterminate (      SELF          IN       wm_concat_impl,      returnvalue   OUT      VARCHAR2,      flags         IN       NUMBER   )      RETURN NUMBER   IS   BEGIN      returnvalue := curr_str;      RETURN odciconst.success;   END;   MEMBER FUNCTION odciaggregatemerge (      SELF    IN OUT   wm_concat_impl,      sctx2   IN       wm_concat_impl   )      RETURN NUMBER   IS   BEGIN      IF (sctx2.curr_str IS NOT NULL)      THEN         SELF.curr_str := SELF.curr_str || ',' || sctx2.curr_str;      END IF;      RETURN odciconst.success;   END;END;/CREATE OR REPLACE FUNCTION wm_concat (p1 VARCHAR2)   RETURN VARCHAR2   AGGREGATE USING wm_concat_impl;/

SQL example:

Select Replace (wm_concat (name), ',') name from table where ID> 100;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.