Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tyler.

Asked: June 25, 2020 - 12:07 am UTC

Answered by: Connor McDonald - Last updated: June 29, 2020 - 5:45 am UTC

Category: Application Express - Version: Application Express 20.1.0.00.13 & Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production & ORDS 19.2

Viewed 100+ times

You Asked

Hello everyone,

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).

Thanks!!

with LiveSQL Test Case:

and we said...

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


and you rated our response

  (2 ratings)

Reviews

The gateway should forward the CGI environment variables to the database

June 26, 2020 - 8:13 am UTC

Reviewer: Morten

I don't really agree with the statement "OWA_UTIL being able to [log IP address of client] 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."

See http://www.cgi101.com/book/ch3/text.html

REMOTE_ADDR is defined as the standard environment variable that holds the IP address of the visitor.

Fair enough, the CGI protocol is very old, but so is the HTTP protocol... Now, the PL/SQL Web Toolkit of which OWA_UTIL is a part, works on the assumption that the gateway that forwards the web request to the database will populate the CGI variables, so that PL/SQL code in the database can access those values via owa_util.get_cgi_env ().

ORDS clearly does this for regular PL/SQL calls, and APEX (itself being a PL/SQL Web Toolkit application) depends on this to work.

So I see no reason why ORDS can't populate the CGI environment for all source types, including "Media Resource" or "Collection Query", when it is already doing it for "PL/SQL" blocks. Saying this can't be done because "the world is moving away from that model" seems like a weak excuse.
Connor McDonald

Followup  

June 29, 2020 - 5:45 am UTC

I respectfully disagree.

If I was running a standard (say) apache web server, then I have the *choice* of whether to include the mod_cgi module or not.

So I totally agree that if when my webserver serves a CGI request, I will have access to the CGI related variables. But if I'm not serving a CGI requests, then why should there be a compunction to add them.

But please if you want them, throw an ER on the ORDS forum.

Webserver should forward Headers to ORDS

June 26, 2020 - 12:10 pm UTC

Reviewer: Tyler (trangelier) from Valdosta GA

"Logging IP addresses for incoming calls to a webserver normally lives in the domain of the webserver, not the facilities implemented underneath it."

While I agree with this statement, the ability to *read* HTTP Headers from the gateway is still a requirement for many applications/developers (see APEX?). This is apparent by every other web language allows this type of functionality, even if it is behind a WebServer acting as a proxy (see the NodeJS + Nginx below). In the situation where ORDS is proxied to behind a WebServer, it is still expected to be able to read the HTTP Headers that Apache/Nginx proxy to ORDS. As Morten stated, ORDS clearly does this for regular PL/SQL calls, and APEX (itself being a PL/SQL Web Toolkit application) depends on this to work.

"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."

I'm quite confused by this statement. A main function of ORDS is to act as a intermediary between HTTP requests and the database. ORDS is commonly deployed behind a web server whether it is ran on an application server (WL/Tomcat) or in standalone mode. In both of those situations OWA_UTIL is available and required to be so for APEX and the RESTful Services available from APEX to function. Past that, if your statement is true how would OWA_UTIL be available from the "PL/SQL" Source Type? While I agree the world is moving away from directly exposing the database itself, to say the OWA_UTIL is available *ONLY* if the DB is exposed itself if blatantly false.

It more seems to me that how the OWA_UTIL package becomes available (OWA.INITIALIZE ?) is simply not configured for any Source Type other than "PL/SQL". Maybe there is a reason the Oracle ORDS Team does this - but I surely cannot find it anywhere.

NodeJS + Nginx: https://stackoverflow.com/questions/30943112/get-ip-user-with-nginx-and-node
PHP + Apache: https://www.virendrachandak.com/techtalk/getting-real-client-ip-address-in-php-2/