Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    25

    Unanswered: WM_CONCAT(Distinct Col_Name) Not Work within Stored Procedure.

    HI,

    Create Table



    when I use WM_CONCAT(distinct column_Name) , It work fine in Query ,
    But IF i write same Statement in Stored Procedure then It giving Error .


    Error: PL/SQL: ORA-30482: DISTINCT option not allowed for this function


    CREATE TABLE city( city_name VARCHAR(20));

    INSERT INTO city(city_name) VALUES ('ABC');

    INSERT INTO city(city_name) VALUES ('PQR');

    INSERT INTO city(city_name) VALUES ('XYZ');

    INSERT INTO city(city_name) VALUES ('ABC');


    4 row inserted


    SELECT city_name FROM city;

    CITY_NAME
    --------------------
    ABC
    PQR
    XYZ
    ABC


    Select WM_CONCAT( city_name) AS city_name FROM city;

    CITY_NAME
    -----------------------------------------------------
    ABC,PQR,XYZ,ABC




    -- This is not Work in SP

    Select WM_CONCAT( DISTINCT city_name) AS city_name FROM city;

    CITY_NAME
    -----------------------------------------------------
    ABC,PQR,XYZ


    __________________________________________________ _______________________________________
    Stroed Procedure Is AS(This is only Example)
    __________________________________________________ _______________________________________


    create or replace
    PROCEDURE "TEST_CONCAT"
    (
    OUT_RESULTSET1 OUT SYS_REFCURSOR
    )

    AS
    BEGIN
    OPEN OUT_RESULTSET1 FOR
    Select WM_CONCAT( DISTINCT city_name) AS city_name FROM city; -- NOT Work

    /* Select WM_CONCAT( city_name) AS city_name FROM city;*/ -- This Work

    END TEST_CONCAT;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What release of the database are you running? WM_CONCAT is an undocumented, unsupported function and not being able to use distinct in a procedure is a known problem. Depending on your version, there are alternatives that work fine.
    Last edited by beilstwh; 12-13-11 at 10:22.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Mr.Vivek,

    I fully agree with beilstwh concerning the usage of unsupported, undocumented functions in productin environments.

    But, just for the science:

    Code:
    Connected.
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE    11.2.0.2.0      Production
    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
    
    SQL> select wm_concat(distinct city_name) from city;
    
    WM_CONCAT(DISTINCTCITY_NAME)
    --------------------------------------------------------------------------------
    ABC,PQR,XYZ
    
    SQL> set serveroutput on
    SQL> declare
      2     vOutStr  varchar2(1024);
      3  begin
      4     select wm_concat(distinct city_name) 
      5        into vOutstr 
      6        from city;
      7     dbms_output.put_line(vOutStr);
      8  end;
      9  /
       select WM_CONCAT(distinct city_name) into vOutstr from city;
              *
    ERROR at line 4:
    ORA-06550: line 4, column 11:
    PL/SQL: ORA-30482: DISTINCT option not allowed for this function
    ORA-06550: line 4, column 4:
    PL/SQL: SQL Statement ignored
    
    
    SQL> declare
      2     vOutStr  varchar2(1024);
      3  begin
      4     select WM_CONCAT(city_name) 
      5        into vOutstr 
      6        from (select distinct city_name from city);
      7     dbms_output.put_line(vOutStr);
      8  end;
      9  /
    PQR,ABC,XYZ
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Last edited by magicwand; 12-14-11 at 06:35. Reason: highlighting
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •