ORA-06502: PL/SQL: numeric or value error while using CLOB

0
votes

For Oracle 11g doesn't support json as you know, i am creating json data using CLOB due to varchar2 (32767) size but in this time i am getting ORA-06502: PL/SQL: numeric or value error. Meanwhile, the data size is 68075. Why i am getting the error despite of Clob is support 4 gb data?

procedure course
(
  p varchar2 default null
)
as

    cursor cr_course(cp_param varchar2)
    is
        select 
            m.code, 
            m.title 
        from t_course m
            where 
                and type = cp_param;

    jobject clob;
    jitem   varchar2(200);

begin

  dbms_lob.createtemporary(jobject, false);

  for n in cr_course(p) loop
      jitem := '{"key":"' || n.code || '", "value":"'|| n.title || '"},';
      dbms_lob.append(jobject, jitem);
  end loop;

  htp.p(substr(jobject, 0, (length(jobject)-1)));

exception when others then
  dbms_lob.freetemporary(jobject);
  htp.p(sqlerrm);

end;
1
jsonplsqlclobvarchar2oas

1 Answers

1
votes

I think the error is because of htp.p(substr(jobject, 0, (length(jobject)-1)));

I don't think substr works on clob type. You will have to convert it to VARCHAR2 datatype to use substr on it.