Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Tyler.

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

Last updated: February 23, 2021 - 6:06 am UTC

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

Viewed 1000+ 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 Connor 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


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The gateway should forward the CGI environment variables to the database

Morten, June 26, 2020 - 8:13 am UTC

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

Tyler (trangelier), June 26, 2020 - 12:10 pm UTC

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

Opinion from a back-end service provider

Ólafur Tryggvason, February 20, 2021 - 2:53 pm UTC

Hi,
After reading the answers from the AskTom team, I couldn't stop myself from throwing in my 2 cents.

I wholeheartedly disagree all the answers/comments that the data from the OWA_UTIL package is outdated or that the database server doesn't need the data.

But, I agree that the term CGI and most of the procedures and functions in the OWA_UTIL package are dated.

If ORDS want's to compete with any other languages out there producing http responses, it definitely must give access to all the request headers + added request information like the IP, without the need to define each and every one as a request parameter in the handler level.

Having to move some services to Java Spring or Python, just because there is a requirement to log the IP of the request (in the database), just doesn't make sense, if ORDS is a serious alternative.

I create services that offer access to data that have a price tag. Being able to log the IP of the request along with the authentication is a vital bit of information if the client decides to question the invoice a few months later. And of course any vital bit of information belongs in a database not some service logs on a web server that may or may not have been rotated and purged, when you finally need it.

I am aware that, with current versions, you can only access the "CGI" variables with source type PL/SQL. Which is why I have a lot of services implemented in PL/SQL that could be implemented much simpler with a Query type and a call to an autonomous transaction function.

Authentication process: you may want to add an extra verification process if the IP and/or User Agent are new to the user.

Accept request header: When you want to follow the rules and support different client needs via a single service call.

Custom implementations where you need access to custom request headers (as part of authentication or just to support some requirement).

The host being called, when you are creating services that are deployed on multiple servers. (For logging purposes, link building, SAML creation or decoding, etc..)

The query string, the request cookie, accept-language, etc.. can all have valid reasons being served to the backend.

Any good proxy front webserver can add all those values to the proxy request header, so at minimum, being able to access all request headers is a must for any programming environment serving HTTP requests (REST or other)

Best solution would be if ORDS supplied access to a function/package/array, that gave you access the same information you get from OWA_UTIL.get_cgi_env/print_gci_env.

Then owa_util would not be needed in any ORDS service anymore.

This also goes for any custom response headers you need to send. Much better to use htp.prn and knowing how HTTP response headers work than using the owa_util wrappers.

Regards
Ólafur



Connor McDonald
February 23, 2021 - 6:06 am UTC

Thank you for your input. I'll pass it on to the ORDS team.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library