An ORDS GET request to retrieve CGI Environment variables from the PRINT_CGI_ENV PL/SQL procedure

by 
ALERT: This is going to seem extremely out of context! But this post actually loosely relates to the ORDS Pre-hook functions section of our docs. I'm in the process of (1) working on a presentation and (2) updating this section of the docs as well (productivity trifecta for the win!), hence why we are here.

Hypothetical scenario

Hypothetically speaking, let’s say you were interested in learning more about Common Gateway Interface (CGI) Environment variables1, what they are, and how to use ORDS to REST-enable a function to produce these variables. If that is the case, you are in luck, my friend!

What follows is a quick way for you to learn more about these variables (as they relate to the Oracle database) and use ORDS in the process!

An excerpt from another “work in progress”

For this example, we’ll rely on the OWA_UTIL PL/SQL package, specifically the PRINT_CGI_ENV procedure (an HTML utility; one of three utility subprograms in the OWA_UTIL package). First, create a Resource Module and Template. Then, when creating a Handler, choose plsql/block as the Source Type and use the PRINT_CGI_ENV procedure in the Handler code.

Like this:

Begin 
  OWA_UTIL.PRINT_CGI_ENV;
End;
I created this Resource Module on my “localhost;” your URI will differ in an Autonomous Database – Always Free account (sign-up here).
Remember you can export PL/SQL definitions.
In case you want to reproduce the Resource Module.

Save your code and Handler.

Sample PL/SQL code here 
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'ORDSDEMO',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'ordsdemo',
      p_auto_rest_auth      => FALSE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'scratch.pad',
      p_base_path      => '/v1/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'scratch.pad',
      p_pattern        => 'oga-cgi-example',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'scratch.pad',
      p_pattern        => 'oga-cgi-example',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'begin
    OWA_UTIL.PRINT_CGI_ENV;
end;');

        
COMMIT;

END;

From there, either copy and paste this Handler’s URI (in the above example, that is https://localhost:8443/ords/ordstest/v1/api) into a new terminal session (if using a tool like a curl), or Postman (or a similar testing tool), or navigate to the URI in a new browser tab or window. You’ll see all the CGI Environment variables that are sent back (in an unauthenticated server response) to you, a client, or an application. Pretty neat trick, eh?

Here is an example of the response from an Autonomous Database – Always Free tenancy:

Here is a curl command response from a development configuration (i.e., A locally installed ORDS instance running in Standalone mode and a 23ai database in a Podman container). 

About using the -k --insecure option in this curl command (HINT: to circumvent TLS for development purposes).
LEARN: Learn more about ORDS and Podman.

As you can see, there is tons of data to work with; something to remember if you want to use CGI Environment variables with your ORDS pre-hook (YOU DO NOT HAVE TO; I’m just showing you an example of one of the countless possibilities!).

Start small

You might want to start small by implementing a security policy using something as simple as the QUERY_STRING variable (e.g., where perhaps your ORDS prehook function calls upon an underlying function or procedure that uses a query string as a parameter). Our pre-hook example does something like this, actually 😀.

Check this out; look what happens when I append ?chris to the end of this URI:

And like magic, the QUERY_STRING CGI Environment variable now has a value assigned to it! See how simple and automatic this is?

Something to think about: even if you don’t care about CGI Environment variables today, I guarantee this will be useful in the future. I bet you’ve been in a position where at least some of this is relevant to you on any given week. So, if nothing else, maybe REST-enable this PRINT_CGI_ENV procedure, so you have it ready whenever you need it!

The end

That’s all for now, folks. This is a quick post that hopefully will come in handy one day 😎. Until next time, keep calm and query on.

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

  1. All I could find (that was reputable and not simply Wikipedia) on CGI: CGI draft 1.1, W3.org references, CGI Programming on the WWW, mod_cgi (oh, the irony of me referencing the Apache HTTP server 🤣, please migrate if you haven’t already 🙏🏼) ↩︎

Leave a Comment