plsqloracle-ords

How to return large text in ORDS using JSON (more than 32k)


I'm creating a Rest API in Oracle ORDS, and I need to return a base64 text that is larger than 32k. The Source Type is PL/SQL.

Original data is in a CLOB variable, but ORDS doesn't support this kind of return. I tried to use LONG, but I can't move the string to LONG when it's larger than 32k.

--This is the variable that has the large text (about 40k characters)
out_hexa        CLOB;

-- :boleto is the OUT parameter in ORDS (OUT, RESPONSE, LONG)

--This wont work:
:boleto := out_hexa;
--This wont work:
:boleto := substr(out_hexa, 1, 32765) || substr(out_hexa, 32765, LENGTH(out_hexa));

--This works, but the Json output is not the way I wanted, since it creates a second level in Json Importante: In this case, :boleto is a ResultSet, and not a Long

OPEN :boleto FOR
     SELECT out_hexa as dados from dual;
In this case the output is:
{
    "boleto": [
        {
            "dados": "JVBERi0xLjQKMSAwIG9iago8PAovVGl0bGUgKP7..."
        }
     ]
}


What I need is a Json in this format:
{
    "boleto": "JVBERi0xLjQKMSAwIG9iago8PAovVGl0bGUgKP7..."
}

Solution

  • Couldn't find a way to do it automatically, so I'm writing the Json myself. I'm reading the CLOB in chunks and writing it using HTP.prn:

          OWA_UTIL.mime_header('application/json', FALSE);
          OWA_UTIL.http_header_close;         
          htp.prn('{');
          htp.prn('"return_code" : "' ||out_status || '",');
          htp.prn('"return_message" : "' ||out_msg_retorno || '",');
          htp.prn('"boleto" : "');
    
          IF(out_status = '001') THEN
            :http_status      := 200;                    
            WHILE counter < length(out_hexa)+chunk_size LOOP                     
                htp.prn(substr(out_hexa, counter, chunk_size));
                counter := counter+chunk_size;
            END LOOP;        
          ELSE
            :http_status      := 404;
          END IF;
          htp.prn('"}');