Martkos IT Ltd

View Original

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.


See this content in the original post

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.


See this content in the original post

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