4 Replies Latest reply on Jul 4, 2012 10:23 AM by Paul Horth

    ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30

    Suhail Faraaz
      select ne,wm_concat(cleared) as clr from association_1 group by ne;

      this is what i get at the end;

      ERROR:
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30


      even if i use the 2nd method by creating type i still get the same error

      select ne,row_col(cleared) as clr from association_1 group by ne;


      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "ALI.T_ROW_COL", line 22

      REATE OR REPLACE TYPE t_row_col AS OBJECT
      (
        g_string  VARCHAR2(32767),
      
        STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_row_col)
          RETURN NUMBER,
      
        MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_row_col,
                                             value  IN      VARCHAR2 )
           RETURN NUMBER,
      
        MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_row_col,
                                               returnValue  OUT  VARCHAR2,
                                               flags        IN   NUMBER)
          RETURN NUMBER,
      
        MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_row_col,
                                           ctx2  IN      t_row_col)
          RETURN NUMBER
      );
      /
      
      
      CREATE OR REPLACE TYPE BODY t_row_col IS
        STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_row_col)
          RETURN NUMBER IS
        BEGIN
          sctx := t_row_col(NULL);
          RETURN ODCIConst.Success;
        END;
      
        MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_row_col,
                                             value  IN      VARCHAR2 )
          RETURN NUMBER IS
        BEGIN
          SELF.g_string := self.g_string || '/' || value;
          RETURN ODCIConst.Success;
        END;
      
        MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_row_col,
                                               returnValue  OUT  VARCHAR2,
                                               flags        IN   NUMBER)
          RETURN NUMBER IS
        BEGIN
          returnValue := RTRIM(LTRIM(SELF.g_string, '/'), '/');
          RETURN ODCIConst.Success;
        END;
      
        MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_row_col,
                                           ctx2  IN      t_row_col)
          RETURN NUMBER IS
        BEGIN
          SELF.g_string := SELF.g_string || '/' || ctx2.g_string;
          RETURN ODCIConst.Success;
        END;
      END;
      /
      
      
      CREATE OR REPLACE FUNCTION row_col (p_input VARCHAR2)
      RETURN VARCHAR2
      PARALLEL_ENABLE AGGREGATE USING t_row_col;
      /
      ************************************************************************************
      how can i rectify this erros
        • 1. Re: ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
          BluShadow
          That's because there is a limit on how big a varchar2 result can be (4000 bytes), and you are exceeding that.

          What you need is to use a CLOB based version instead e.g.
          create or replace
            type clobagg_type as object(
                                        text clob,
                                        static function ODCIAggregateInitialize(
                                                                                sctx in out clobagg_type
                                                                               )
                                          return number,
                                        member function ODCIAggregateIterate(
                                                                             self  in out clobagg_type,
                                                                             value in     clob
                                                                            )
                                          return number,
                                        member function ODCIAggregateTerminate(
                                                                               self        in     clobagg_type,
                                                                               returnvalue    out clob,
                                                                               flags       in     number
                                                                              )
                                          return number,
                                        member function ODCIAggregateMerge(
                                                                           self in out clobagg_type,
                                                                           ctx2 in     clobagg_type
                                                                          )
                                          return number
                                       );
          / 
          create or replace
            type body clobagg_type
              is
                static function ODCIAggregateInitialize(
                                                        sctx in out clobagg_type
                                                       )
                  return number
                  is
                  begin
                      sctx := clobagg_type(null) ;
                      return ODCIConst.Success ;
                end;
                member function ODCIAggregateIterate(
                                                     self  in out clobagg_type,
                                                     value in     clob
                                                    )
                  return number
                  is
                  begin
                      self.text := self.text || value ;
                      return ODCIConst.Success;
                end;
                member function ODCIAggregateTerminate(
                                                       self        in     clobagg_type,
                                                       returnvalue    out clob,
                                                       flags       in     number
                                                      )
                  return number
                  is
                  begin
                      returnValue := self.text;
                      return ODCIConst.Success;
                  end;
                member function ODCIAggregateMerge(
                                                   self in out clobagg_type ,
                                                   ctx2 in     clobagg_type
                                                  )
                  return number
                  is
                  begin
                      self.text := self.text || ctx2.text;
                      return ODCIConst.Success;
                  end;
          end;
          / 
          create or replace
            function clobagg(
                             input clob
                            )
              return clob
              deterministic
              parallel_enable
              aggregate using clobagg_type;
          / 
          
          
          
          SQL> select trim(',' from clobagg(ename||',')) as enames from emp;
          
          ENAMES
          -----------------------------------------------------------------------------------
          SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
          
          SQL>
          • 2. Re: ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
            Suhail Faraaz
            well right now i am using the code that you provided but ....its taking a long time and it is still in the execution process
            • 3. Re: ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
              947394
              I too have very bad performance issue with the function call. Any one can help
              • 4. Re: ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
                Paul  Horth
                Do not re-open an old thread.

                Start a new one of your own.