Converting SQL*Plus calls in shell scripts to ORDS calls

I develop a lot of shell scripts. I would not define myself an old dinosaur that keeps avoiding python or other modern languages. It is just that most of my scripts automate OS commands that I would normally run interactively in an interactive shell… tar, cp, expdp, rman, dgmgrl, etc… and of course, some SQL*Plus executions.

For database calls, the shell is not appropriate: no drivers, no connection, no statement, no resultset… that’s why I need to make SQL*Plus executions (with some hacks to make them work correctly), and that’s also why I normally use python or perl for data-related tasks.

Using SQL*Plus in shell scripts

For SQL*Plus executions within a shell scripts there are some hacks, as I have said, that allow to get the data correctly.

As example, let’s use this table (that you might have found in my recent posts):

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(50)
 OH_TYPE                                            VARCHAR2(10)
 VERSION                                            VARCHAR2(10)
 FULLPATH                                           VARCHAR2(200)
 CREATED                                            TIMESTAMP(6)
 DESCRIPTION                                        VARCHAR2(2000)

SQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb1', 'RDBMS', '18.3.0', '/test/path/', sysdate-10, 'First version 18.3.0');

1 row created.

SQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb2', 'RDBMS', '18.3.0', '/test/path/', sysdate-1, '18_3_0_cerndb1 + Patch XXX');

1 row created.

SQL> commit;

Commit complete.

In order to get, as example, the result of this query:

SELECT name, version, fullpath, TO_CHAR(created,'YYYY-MM-DD') as created
FROM oh_golden_images WHERE oh_type='RDBMS' order by created

and assign the values to some variables (in a shell loop), it is common to do something like this:

RESULT=`$ORACLE_HOME/bin/sqlplus -s $REPO_CREDENTIALS 2>&1 <<EOF | grep ";"
        set line 200 pages 1000
        set echo off feedback off heading off
        alter session set nls_timestamp_format='YYYY-MM-DD';
        SELECT name || ';' ||version || ';' || fullpath || ';' || created
          FROM oh_golden_images
        WHERE oh_type='RDBMS'
           order by created;

for line in $RESULT ; do
        L_GI_Name=`echo $line | awk -F\; '{print $1}'`
        L_GI_Version=`echo $line | awk -F\; '{print $2}'`
        L_GI_Path=`echo $line | awk -F\; '{print $3}'`
        L_GI_Date=`echo $line | awk -F\; '{print $4}'`
        echo "doing something with variables $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version"

As you can see, there are several hacks:

  • The credentials must be defined somewhere (I recommend putting them in a wallet)
  • All the output goes in a variable (or looping directly)
  • SQL*Plus formatting can be a problem (both sqlplus settings and concatenating fields)
  • Loop and get, for each line, the variables (using awk in my case)

It is not rock solid (unexpected data might compromise the results) and there are dependencies (sqlplus binary, credentials, etc.). But for many simple tasks, that’s more than enough.

Here’s the output:

$ sh
doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/ 18.3.0
doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/ 18.3.0


Using ORDS instead

Recently I have come across a situation where I had no Oracle binaries but needed to get some data from a table. That is often a situation where I use python or perl, but even in these cases, I need compatible software and drivers!

So I used ORDS instead (that by chance, was already configured for the databases I wanted to query), and used curl and jq to get the data in the shell script.

First, I have defined the service in the database:

    p_module_name    => 'ohctl',
    p_base_path      => 'ohctl/',
    p_pattern        => 'list/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT name, version, fullpath, TO_CHAR(created,''YYYY-MM-DD'') as created FROM oh_golden_images WHERE oh_type=''RDBMS'' order by created',
    p_items_per_page => 0);

At this point, a direct call gives this:

$ curl $rest_ep/ohctl/list/

How to parse the data?

jq is a command-line JSON processor that can be used in a pipeline.

I can get the items:

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[]'
  "created": "2018-08-19",
  "fullpath": "/test/path/",
  "version": "18.3.0",
  "name": "18_3_0_cerndb1"
  "created": "2018-08-28",
  "fullpath": "/test/path/",
  "version": "18.3.0",
  "name": "18_3_0_cerndb2"

And I can produce a csv output:

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[] | @csv "\([.created]),\([.fullpath]),\([.version]),\([.name])"'

But the best, is the shell formatter, that returns strings properly escaped for usage in shell commands:

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[] | @sh "L_GI_Date=\([.created]); L_GI_Path=\([.fullpath]); L_GI_Version=\([.version]); L_GI_Name=\([.name])"'
L_GI_Date='2018-08-19'; L_GI_Path='/test/path/'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb1'
L_GI_Date='2018-08-28'; L_GI_Path='/test/path/'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb2'

At this point, the call to eval is a natural step 🙂

for line in `curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[] | @sh "L_GI_Date=\([.created]); L_GI_Path=\([.fullpath]); L_GI_Version=\([.version]); L_GI_Name=\([.name])"'` ; do
        eval $line
        echo "doing something with values $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version"

The output:

$ sh
doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/ 18.3.0
doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/ 18.3.0





TEL/電話+86 13764045638
QQ 47079569