Hi Experts,
I'm using ORDS 19.1 and Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production and i would like to return a complex data type from the function which is inside a package.
FUNCTION get_record(
p\_b IN VARCHAR2,
p\_s IN VARCHAR2,
p\_p OUT VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
IF p\_b IS NULL THEN
jo := json\_object\_t.parse('{"Id":"0","vMessage ":"B must be entered"}');
RETURN jo.to\_string;
END IF;
EXCEPTION
WHEN OTHERS THEN
error\_message := 'get\_next\_parcel ' || sqlerrm;
jo := json\_object\_t.parse('{"Id":"0",
"vMessage ":error\_message}');
RETURN jo.to\_string;
END get\_record;
I am successfully able to run in postman but I am not liking the output format and not sure if it is the best way to achieve the requirement.
Since complex objects were not supported i made use of json_object_t.parse API to create a number and string complex object and once my output was ready i return the json as string to a primitive type.
Is this the only way of building a complex response ?
Also, what i can see from the postman output is that it has '\' being prefixed and suffixed to key and a value which doesn't make the output formatted.
I also made use of jo json_object_t;
jo.put('Id','1'); return jo.to_string and it has the same output. My expectation from the out put is
{
"p\_parcel": null,
"~ret": "{"sResultId ":"0","vMessage":"block must be entered"}"
}
OR if it returns an array of record
{
"p\_parcel": null,
"~ret": "{"sResultId ":"0","vMessage":"Sucess"},
{"recordList":\[{"id":1,"Name":AB },
{"id":1,"Name":AB }
\]
}"
}
Can someone advice if i am heading in right direction and how can i achieve the complex custom response from plsql block via ords.
Cheers, Ajax