Clob conflict in oracle DB - PLSQL function

Question

Hello I've designed this table:

CREATE TABLE "PUBLIC"."FORMS_UNITS" 
   (    "CODE" VARCHAR2(1024 BYTE) NOT NULL ENABLE, 
    "SUBTITLE" VARCHAR2(1024 BYTE), 
    "DESCRIPTION" CLOB, 
    "ELEMENTS" CLOB NOT NULL ENABLE, 
    "STATUS" VARCHAR2(1024 BYTE) DEFAULT 'Borrador' NOT NULL ENABLE, 
    "KEYWORDS" VARCHAR2(1024 BYTE), 
    "CREATED_AT" TIMESTAMP (6), 
    "UPDATED_AT" TIMESTAMP (6), 
    "CREATED_BY" NUMBER(19,0) NOT NULL ENABLE, 
    "UPDATED_BY" NUMBER(19,0), 
     CONSTRAINT "FORMS_UNITS_ID_PK" PRIMARY KEY ("CODE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APPSDATA"  ENABLE, 
     CONSTRAINT "FORMS_UNITS_USER_CB_ID_FK" FOREIGN KEY ("CREATED_BY")
      REFERENCES "PUBLIC"."USERS" ("ID") ON DELETE CASCADE ENABLE, 
     CONSTRAINT "FORMS_UNITS_USER_uB_ID_FK" FOREIGN KEY ("UPDATED_BY")
      REFERENCES "PUBLIC"."USERS" ("ID") ON DELETE CASCADE ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APPSDATA" 
 LOB ("DESCRIPTION") STORE AS BASICFILE (
  TABLESPACE "APPSDATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
 LOB ("ELEMENTS") STORE AS BASICFILE (
  TABLESPACE "APPSDATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

The problem is in the following plsql function, which returns this error:

SQL Error: ORA-06502: PL/SQL: numeric or value error ORA-06512: in "PUBLIC.FORMS_PKG", line 315 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values ​​do not violate constraints.

This is the PLSQL function:

FUNCTION GET_FORMS
RETURN CLOB
AS
     l_query      CLOB;
     l_result     CLOB;
     l_temp       SYS_REFCURSOR;
     l_row        FORMS_UNITS%ROWTYPE;
BEGIN
    l_query := 'SELECT CODE, TITLE, SUBTITLE, DESCRIPTION, KEYWORDS, ELEMENTS, STATUS, CREATED_AT, UPDATED_AT, CREATED_BY, UPDATED_BY  FROM FORMS_UNITS';    
    OPEN l_temp FOR l_query;
    l_result := '[ ';
    LOOP
        FETCH l_temp INTO l_row;
        EXIT WHEN l_temp%NOTFOUND;
        l_result := l_result || '{ "CODE": "' || l_row.CODE || '", "TITLE": "' || l_row.TITLE || '", "SUBTITLE": "' || l_row.SUBTITLE || '", "DESCRIPTION": "' || l_row.DESCRIPTION || '", "KEYWORDS": "' || l_row.KEYWORDS || '", "ELEMENTS": "' || l_row.ELEMENTS || '", "STATUS": "' || l_row.STATUS || '", "CREATED_AT": "' || l_row.CREATED_AT || '", "UPDATED_AT": "' || l_row.UPDATED_AT || '", "CREATED_BY": "' || l_row.CREATED_BY || '", "UPDATED_BY": "' || l_row.UPDATED_BY || '" }, ';
    END LOOP;
    IF l_temp%ISOPEN THEN
        CLOSE l_temp;
    END IF;
    l_result := RTRIM(l_result, ', ') || ' ]';

    RETURN l_result;
END;

It seems the problem is on the line FETCH l_temp INTO l_row;

As the CL.OB variable is insufficient in size because the problem lies in the table component element. The problem arises when what the function returns is 4,959 characters long. Now if I decrease the size, the function returns a correct value.

Answer 1

If you are using Oracle 12 or later then you should not be trying to generate JSON by hand; you should use the built-in JSON functions:

FUNCTION GET_FORMS
RETURN CLOB
AS
     l_result     CLOB;
BEGIN
  SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'CODE' VALUE CODE,
             -- KEY 'TITLE' VALUE TITLE, -- The table has no TITLE column
             KEY 'SUBTITLE' VALUE SUBTITLE,
             KEY 'DESCRIPTION' VALUE DESCRIPTION,
             KEY 'KEYWORDS'    VALUE KEYWORDS,
             KEY 'ELEMENTS'    VALUE ELEMENTS,
             KEY 'STATUS'      VALUE STATUS,
             KEY 'CREATED_AT'  VALUE TO_CHAR(CREATED_AT, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'),
             KEY 'UPDATED_AT'  VALUE TO_CHAR(UPDATED_AT, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'),
             KEY 'CREATED_BY'  VALUE CREATED_BY,
             KEY 'UPDATED_BY'  VALUE UPDATED_BY
             RETURNING CLOB
           )
           RETURNING CLOB
         )
  INTO   l_result
  FROM   FORMS_UNITS;

  RETURN l_result;
END;
/

---

If you do want to generate the JSON manually (don't) then your code could be:

FUNCTION GET_FORMS
RETURN CLOB
AS
  l_result CLOB := EMPTY_CLOB() || '[';
BEGIN
  FOR l_row IN (SELECT * FROM FORMS_UNITS)
  LOOP
    l_result := l_result
                || '{'
                || '"CODE": "' || l_row.CODE || '",'
             -- || '"TITLE": "' || l_row.TITLE || '",'
                || '"SUBTITLE": "' || l_row.SUBTITLE || '",'
                || '"DESCRIPTION": "' || l_row.DESCRIPTION || '",'
                || '"KEYWORDS": "' || l_row.KEYWORDS || '",'
                || '"ELEMENTS": "' || l_row.ELEMENTS || '",'
                || '"STATUS": "' || l_row.STATUS || '",'
                || '"CREATED_AT": "' || TO_CHAR(l_row.CREATED_AT, 'YYYY-MM-DD"T"HH24:MI:SS.FF6') || '",'
                || '"UPDATED_AT": "' || TO_CHAR(l_row.UPDATED_AT, 'YYYY-MM-DD"T"HH24:MI:SS.FF6') || '",'
                || '"CREATED_BY": "' || TO_CHAR(l_row.CREATED_BY) || '",'
                || '"UPDATED_BY": "' || TO_CHAR(l_row.UPDATED_BY) || '"'
                || '}, ';
  END LOOP;
  l_result := RTRIM(l_result, ', ') || ']';
  RETURN l_result;
END;
/

However, this does not properly encode characters in the strings that should be escaped (i.e. " should be \" and CHR(10) should be \n, etc.).

fiddle