I've tried to scour the internet and can't find the answer to this anywhere else. I have the use case where I'd like to be able to see the IP_ADDRESS of a user requesting data from an RESTful Service endpoint. Upon my testing, the only "Source Type" I can use to not encounter an error (error at the bottom of the post) when using the OWA_UTIL package is "PL/SQL". The "PL/SQL" Source Type does not work for my use case as I am returning an Image, therefore I am using the "Media Resource" source type although the "Collection Query" and "Collection Query Item" encounter the same issue.
I've attached a livesql of the most basic use case for my issues. I've also provided the ORDS commands to create the module, template and handler to showcase this error in your environment. Using "Statement 2" in a "PL/SQL" Source Type will illustrate that the OWA_UTIL package works there.
I have tried creating a Pipelined Function and using the owa.init, as detailed in the below SO post, in the Pipelined Function PL/SQL block and all the environment variables are null. I assume this has something to do with ORDS? From my understanding, the OWA_UTIL package should be available when the code is called from a gateway, in my case ORDS. https://stackoverflow.com/questions/25084103/why-this-ora-06502-error-message
Here is the error I receive when using the owa_util.get_cgi_env('REMOTE_ADDR') in a query. I copied this from the ORDS Stack Trace as we have ORDS errors printing to the screen in our development environment.
ResourceGeneratorEvaluationException [statusCode=500, reasons=[The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
Ultimately, I am just looking for a solution where I can use the OWA_UTIL package and serve an image. If there is a way I can serve images from the "PL/SQL" source type I will go that route however I feel the Media Resource Source type is better served for this and wouldn't like to lose it's advantages (if any).
Logging IP addresses for incoming calls to a webserver normally lives in the domain of the webserver, not the facilities implemented underneath it.
OWA_UTIL being able to do it stems from a requirement where the database itself would *be* the webserver (via the embedded gateway) but the world (and us) is moving away from that model.
Simiarly, if you are using ORDS standalone mode, then you can activate logging for the http server running out of ords itself
See here http://krisrice.blogspot.com/2017/01/how-to-add-ncsa-style-access-log-to.html