We have a package that contains two functions, that we use in a clinical system:
allergies(:patient) takes our patient identifier and uses apex_web_service to call an API that returns a list of the patient's allergies as a JSON payload.
get_allergies(:patient) is a pipelined function. It calls the above function, loops through the results and pipes each one as a row.
This way our application can use select * from get_allergies(:patient) to retrieve the data and display it.
If I was writing this now we'd probably use JSON_QUERY or JSON_TABLE but this was written in an older version of the database so uses the APEX_JSON API to retrieve the data and pipe it out.
Here's the problem:
Calling allergies(:patient) works absolutely fine, until we call get_allergies(:patient). That fails with ORA-29024: Certificate validation failure, and then subsequent calls to allergies(:patient) also fail with the same error, unless they are made from the same database connection that allergies(:patient) worked with originally. Disconnecting and reconnecting means the function will consistently fail with the certificate validation error.
It seems bizarre that running a pipelined function would change the certificate validation status of the API the data came from, but I've managed to replicate the problem consistently - to the point we've now run out of servers that provide this API and can no longer use it!
Is this a known bug? Any suggested fixes?