SQLcl

A useful tool when dealing with Oracle data and automation is SQLcl. It allows you to connect to Oracle without launching a GUI and execute sql on the command line.

Pre-requisites

You will need Oracle SQLcl and a Linux OS with bash installed.

INSTALLATION

Download SQLcl and extract files.

USAGE

By embedding sqlcl commands inside bash shell scripts you can automate processes such as building test data.

In this tutorial we'll create a basic script that connects to Oracle and reads data into a variable called result and then echo’s it out to the screen.

---

#!/bin/bash
ORACLE_CON_STR=USERNAME/PASSWORD@//LOCALHOTS:1521/DATABASE

result=`sql -S ${ORACLE_CON_STR} <<-EOF
SET PAGES 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SELECT /*+ PARALLEL(S,30) FULL(S) */ ITEM FROM TABLE ORDER BY ITEM DESC fetch first 1 row only;
exit;
EOF`

echo "Oracle returned the following result = $result"

---

Should to want to read multiple rows of data you can use a bash array. This next code block requests the first 100 rows of data from Oracle and saves it to the result variable.

Within BASH there is a command called mapfile. It can take the result variable which contains all of the data returned from Oracle and can map it to array elements that can be accessed individually.

Here we iterate around each of the elements and echo them out to screen.

---

#!/bin/bash
ORACLE_CON_STR=USERNAME/PASSWORD@//LOCALHOTS:1521/DATABASE

result=`sql -S ${ORACLE_CON_STR} <<-EOF
SET PAGES 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SELECT /*+ PARALLEL(S,30) FULL(S) */ ITEM FROM TABLE ORDER BY ITEM DESC fetch first 100 row only;
exit;
EOF`

mapfile RESULTS < <(echo $result | sed 's/" "/"\n"/g' | sed 's/"//g')
for i in "${!RESULTS[@]}"
do
   echo "Oracle returned the following result = ${RESULTS[i]}"
done

---

This are simple examples but shows how easy it is to automate data extraction from Oracle within shell scripts. You could create your own build.sh to extract the data you need to test and push it to CSV or Redis.

FURTHER READING

Bash Arrays

SQLcl