Avoid 4000 character Limitation on SQL

Avoid 4000 character Limitation on SQL Query

Topic Introduction: In SQL Query we often face the problem that a varchar column can not take more than 4000 characters (ORA-01489: result of string concatenation is too long). For this reason, we can't make a report by using SQL Query. to solve this problem, we can convert this column to clob for use in the report. here we will see how to solve this problem.

1. Table Create.
2. Data insert.
3. Listagg clob Type Create.
4. Listagg clob Function.
5. Query Test.

Table Creation:

CREATE TABLE LISTTAG_TEST

(

  ID            NUMBER(6),

  FIRST_NAME    VARCHAR2(20 BYTE),

  LAST_NAME     VARCHAR2(25 BYTE),

  EMAIL         VARCHAR2(25 BYTE),

  PHONE_NUMBER  VARCHAR2(20 BYTE),

  IDATE         DATE

)

Data Insert on Table:

begin

        for i in 1 .. 1000

        loop

            insert into LISTTAG_TEST (ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,IDATE)

values ( i,'SIDDHARTHREZA'||i,'reza',null,null,TO_CHAR(SYSDATE,'DD-MON-RR'));

        end loop;

        commit;

end;

/

Here if we execute this SQL using listagg there will show an error. ORA-01489: result of string concatenation is too long. This error indicates that a varchar2 can not accept more than 4000 characters on a SQL query.

  select idate,

         listagg (first_name, ', ') within group (order by idate desc) fname

    from listtag_test

group by idate

Type Creation: 

create or replace type listagg_clob_t as object

( t_varchar2 varchar2(32767)

, t_clob clob

, static function odciaggregateinitialize( sctx in out listagg_clob_t )

  return number

, member function odciaggregateiterate

    ( self in out listagg_clob_t

    , a_val varchar2

    )

  return number

, member function odciaggregateterminate

    ( self in out listagg_clob_t

    , returnvalue out clob

    , flags in number

    )

  return number

, member function odciaggregatemerge

    ( self in out listagg_clob_t

    , ctx2 in out listagg_clob_t

    )

  return number

)

/

create or replace type body listagg_clob_t

is

  static function odciaggregateinitialize( sctx in out listagg_clob_t )

  return number

  is

  begin

    sctx := listagg_clob_t( null, null );

    return odciconst.success;

  end;

--

  member function odciaggregateiterate

    ( self in out listagg_clob_t

    , a_val varchar2

    )

  return number

  is

    procedure add_val( p_val varchar2 )

    is

    begin

      if nvl( lengthb( self.t_varchar2 ), 0 ) + lengthb( p_val ) <= 4000

-- Strange limit, the max size of self.t_varchar2 is 29993

-- If you exceeds this number you get ORA-22813: operand value exceeds system limits

-- with 29993 you get JSON-output as large 58894 bytes

-- with 4000 you get JSON-output as large 1063896 bytes, probably max more

      then

        if self.t_varchar2 is null then

          self.t_varchar2 := self.t_varchar2 || p_val;

        else

          self.t_varchar2 := self.t_varchar2 || ',' || p_val;

        end if;

      else

        if self.t_clob is null

        then

          dbms_lob.createtemporary( self.t_clob, true, dbms_lob.call );

          dbms_lob.writeappend( self.t_clob, length( self.t_varchar2 ), self.t_varchar2 );

        else

          dbms_lob.writeappend( self.t_clob, length( self.t_varchar2 ), ','||self.t_varchar2 );

        end if;

        self.t_varchar2 := p_val;

      end if;

    end;

  begin

    add_val( a_val );

    return odciconst.success;

  end;

--

  member function odciaggregateterminate

    ( self in out listagg_clob_t

    , returnvalue out clob

    , flags in number

    )

  return number

  is

  begin

    if self.t_clob is null

    then

      dbms_lob.createtemporary( self.t_clob, true, dbms_lob.call );

    end if;

    if self.t_varchar2 is not null

    then

      dbms_lob.writeappend( self.t_clob, length( self.t_varchar2 ), self.t_varchar2 );

    end if;

    returnvalue := self.t_clob;

    return odciconst.success;

  end;

--

  member function odciaggregatemerge

    ( self in out listagg_clob_t

    , ctx2 in out listagg_clob_t

    )

  return number

  is

  begin

    if self.t_clob is null

    then

      dbms_lob.createtemporary( self.t_clob, true, dbms_lob.call );

    end if;

    if self.t_varchar2 is not null

    then

      dbms_lob.writeappend( self.t_clob, length( self.t_varchar2 ), self.t_varchar2 );

    end if;

    if ctx2.t_clob is not null

    then

      dbms_lob.append( self.t_clob, ctx2.t_clob );

      dbms_lob.freetemporary( ctx2.t_clob );

    end if;

    if ctx2.t_varchar2 is not null

    then

      dbms_lob.writeappend( self.t_clob, length( ctx2.t_varchar2 ), ctx2.t_varchar2 );

      ctx2.t_varchar2 := null;

    end if;

    return odciconst.success;

  end;

--

end;

/

Function Creation: 

CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob

PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;

SQL Statement for checking Query :

select IDATE, listagg_clob(FIRST_NAME)

from LISTTAG_TEST

group by IDATE