Getting error while clob data from oracle

Question

Getting the below error while querying from oracle:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1

Here is my query:

with my_clob as ( select ID,SUBJECT,EMAIL_LAYOUT,EMAIL_SENDER_TYPE from BMS_EMAIL_TEMPLATE where ID = 26 ) select ID,SUBJECT,EMAIL_SENDER_TYPE, dbms_lob.substr(EMAIL_LAYOUT, 4000, (level - 1) * 4000 + 1) AS EMAIL_LAYOUT from my_clob connect by level <= ceil(dbms_lob.getlength(EMAIL_LAYOUT) / 4000)

Can anyone please help me what I am missing here?

Thomas Strub Thomas Strub · Accepted Answer · 2018-07-12T07:46:16

Could you please provide the description of the table BMS_EMAIL_TEMPLATE? Is EMAIL_LAYOUT clob or is it a large varchar2?

I can't reproduce your error with the following code

with bs as (    
    select to_clob( lpad('x',37,'x')) f1 from dual),
bs1 as (select f1||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 as f1 from bs),
bs2 as (select f1||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 as f1 from bs1),
bs3 as (select f1||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 as f1 from bs2),
cl as ( select f1||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 ||f1 as f1 from bs3),
my_clob AS (
    SELECT
        26 id,
        'subj' subject,
        f1 email_layout,
        length(f1) email_length,
        'est' email_sender_type
    FROM
        cl
) SELECT
    id,
    subject,
    email_sender_type,
    email_length,
    dbms_lob.substr(email_layout,4000, (level - 1) * 4000 + 1) AS email_layout
  FROM
      my_clob
CONNECT BY
    level <= ceil(dbms_lob.getlength(email_layout) / 4000);

Getting error while clob data from oracle

1 Answers