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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> desc OH_GOLDEN_IMAGES 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/18_3_0_cerndb1.zip', 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/18_3_0_cerndb2.zip', 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:
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
REPO_CREDENTIALS='scott/tiger@orcl' 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; exit; EOF ` 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" done |
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:
|
1 2 3 |
$ sh sqlplus_test.sh doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/18_3_0_cerndb1.zip 18.3.0 doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/18_3_0_cerndb2.zip 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BEGIN ORDS.DEFINE_SERVICE( 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); COMMIT; END; / |
At this point, a direct call gives this:
|
1 2 |
$ curl $rest_ep/ohctl/list/ {"items":[{"name":"18_3_0_cerndb1","version":"18.3.0","fullpath":"/test/path/18_3_0_cerndb1.zip","created":"2018-08-19"},{"name":"18_3_0_cerndb2","version":"18.3.0","fullpath":"/test/path/18_3_0_cerndb2.zip","created":"2018-08-28"}],"hasMore":false,"limit":0,"offset":0,"count":2,"links":[{"rel":"self","href":"https://rest_endpoint/ohctl/list/"},{"rel":"describedby","href":"https://rest_endpoint/metadata-catalog/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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$ curl -s $rest_ep/ohctl/list/ | jq --raw-output '.items[]' { "created": "2018-08-19", "fullpath": "/test/path/18_3_0_cerndb1.zip", "version": "18.3.0", "name": "18_3_0_cerndb1" } { "created": "2018-08-28", "fullpath": "/test/path/18_3_0_cerndb2.zip", "version": "18.3.0", "name": "18_3_0_cerndb2" } |
And I can produce a csv output:
|
1 2 3 |
$ curl -s $rest_ep/ohctl/list/ | jq --raw-output '.items[] | @csv "\([.created]),\([.fullpath]),\([.version]),\([.name])"' "2018-08-19","/test/path/18_3_0_cerndb1.zip","18.3.0","18_3_0_cerndb1" "2018-08-28","/test/path/18_3_0_cerndb2.zip","18.3.0","18_3_0_cerndb2" |
But the best, is the shell formatter, that returns strings properly escaped for usage in shell commands:
|
1 2 3 |
$ 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/18_3_0_cerndb1.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb1' L_GI_Date='2018-08-28'; L_GI_Path='/test/path/18_3_0_cerndb2.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb2' |
At this point, the call to eval is a natural step 🙂
|
1 2 3 4 5 6 |
IFS=" " 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" done |
The output:
|
1 2 3 |
$ sh ords_test.sh doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/18_3_0_cerndb1.zip 18.3.0 doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/18_3_0_cerndb2.zip 18.3.0 |
😉
—
Ludovico
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
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 😉
The formatting is gone, but you can use echo over multiple lines so you can format your SQL to be easier to read.
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.
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 🙂
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