Response with Clob paramaters on ORDS

Question

I've a post method that response with user data, name, id, etc.

{
    "allowed": "SI",
    "id": 2112,
    "name": "Manuela Merlo",
    "age": "23"
}

Now, I need add picture information on base 64 over the same response. When the picture is the small size the method work fine.

{
    "allowed": "SI",
    "id": 2112,
    "name": "Manuela Merlo",
    "age": "23",
    "picture":"/9j/4AAQSkZJRgABAQEAeAB4AAD/....all base 64 here..." 

}

but when the image is long size then fails with error ORA-06502: PL/SQL: numeric or value error.

ORDS not support clob parameters at response, only string and long variable is available for store char data.

This is the source code of the Post module on ords

declare
 l_user     varchar2(100);

begin
l_user    :=:username;
:l_allowed:=pkg_users.validate_user(l_user,:password);
:l_id     :=l_user;
:l_name   :=pkg_users.Get_Name(l_user);
:l_age    :=pkg_users.Get_Age(l_user);
:l_picture:=pkg_users.Get_picture(l_user);
end; 

I'm not able to create a separete method to reponse on another media type because the client app is not updateable.

Are there any workaourond to responde long size on Ords?

Answer 1

If you return the CLOB as part of a SELECT clause it will work, but returning it as part of the RESPONSE doesn't work, it's limited to 32K, and this make me think that in that case htp.prn() is used behind the scene. So if the only solution for you to return a RESPONSE because you get the results from a procedure, then you should build the whole JSON yourself and use a function that outputs the CLOB using http.prn() by chunk of 32K max.