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

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

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

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:

 

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:

At this point, a direct call gives this:

How to parse the data?

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

I can get the items:

And I can produce a csv output:

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

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

The output:

😉

Ludovico

The following two tabs change content below.

Ludovico

Oracle ACE Director and Computing Engineer at CERN
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Computing Engineer at CERN, the European Organization for Nuclear Research, in Switzerland.

5 thoughts on “Converting SQL*Plus calls in shell scripts to ORDS calls

  1. Hi Ludovico, is there a specific reason to use “here commands”? I find it way cleaner, simpler and less cumbersome to simply use:
    printf/echo “select * from dual;” | sqlplus -S / as sysdba

  2. Hi Frits, good point. I sometimes use echoes and pipelines, but for multiple calls (e.g. executing a few alter session), transactions or for anonymous pl/sql blocks, “here commands” are easier to read. After using them a lot I am more comfortable reading them 🙂

  3. At least in the past I had issues when there were multiple here commands in the same file. Plus, you can use something like:
    echo ” set echo off
    select * from dual;
    ” | sqlplus / as sysdba
    Here commands require the the “here symbol” to be on the beginning of the line, which breaks indention. (plus you can use printf and use a oneliner that and use line ends with \n)
    Also consider using $( command ) instead of backticks ; you can nest $(), which is not possible with backticks. It helped me to write better formatted and more flexible code, that is why I advise it.

  4. LOL, my backticks habit comes from the old ksh on digital, I guess, no real reason to stick on it. But I wrote this blog post not to argue about code styling and SQL*plus, but for the ORDS thingy 🙂
    Anyway thanks for the suggestions 😉

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.