Getting result from database in shell script

In shell script, there is no standard way of connecting and retrieving result from database. But, for Oracle we can use SQLPlus to connect and run operations on database. I tried my codes on bash for this post and codes may change according to different shells.

My first example will basically cover how to run a stored procedure from shell script and checking if the procedure finished successfully or not. I will create a basic hello world procedure on HR schema and edit it during this post. Here is a procedure with one varchar2 parameter:

CREATE OR REPLACE PROCEDURE HR.PRC_HELLO_WORLD(piv_name varchar2 default 'World')
AS 
BEGIN

  dbms_output.put_line('Hello ' || piv_name);

END PRC_HELLO_WORLD;

After I compiled this procedure, I created a shell script called ‘call_procedure.sh’ and give execute permission to my user. Here is the simple shell script file that calls procedure and check the run was successful or not.

#!/bin/bash
echo "Script started"
echo ""

sqlplus -s hr/hr@localhost:1521/orcl << end_sql
WHENEVER SQLERROR EXIT 1 ROLLBACK
SET SERVEROUTPUT ON
exec hr.prc_hello_world;
exit 0;
end_sql

if [ $? = 0 ]
then
  echo ""
  echo "Yayy, It worked :)"
  echo ""
else
  echo ""
  echo "Hmm, Something wrong happened :("
  echo ""
fi

SQLPlus has a lot of system variable that you can set. For example, we set ‘SERVEROUTPUT ON’ to catch standard output messages. In this link, you can find any other variables that may help you. When I run the script I got below output:

Shell Script Successful Result

Later that, I changed PRC_HELLO_WORLD procedure to throw exception after the output. With that way, my SQLPlus connection will exit with value 1 and I will understand something went wrong with using this value. For this example, I also add a parameter to my procedure call via shell script variable to show you how parameters can be used.

CREATE OR REPLACE PROCEDURE HR.PRC_HELLO_WORLD(piv_name varchar2 default 'World')
AS 
BEGIN

  dbms_output.put_line('Hello ' || piv_name);

  RAISE_APPLICATION_ERROR (-20001, 'Nicely prepared exception');

END PRC_HELLO_WORLD;
#!/bin/bash
echo "Script started"
echo ""

v_my_name="Aykut"

sqlplus -s hr/hr@localhost:1521/orcl << end_sql
WHENEVER SQLERROR EXIT 1 ROLLBACK
SET SERVEROUTPUT ON
exec hr.prc_hello_world('$v_my_name');
exit 0;
end_sql

if [ $? = 0 ]
then
  echo ""
  echo "Yayy, It worked :)"
  echo ""
else
  echo ""
  echo "Hmm, Something wrong happened :("
  echo ""
fi

And the result is changed like:

Shell Script Error Result

Well, this may cover most of the cases but what if we want to return a status code from database? Can we use the exit part on the SQLPlus? Yes, we can. To do that, I added an out parameter to my procedure and change the body to response for different cases.

CREATE OR REPLACE PROCEDURE HR.PRC_HELLO_WORLD(piv_name in varchar2 default 'World', pon_rc out number)
AS 
BEGIN

  IF upper(piv_name) = 'AYKUT'
  THEN
    dbms_output.put_line('Hello Master ' || piv_name);
    pon_rc := 1;
  ELSIF upper(piv_name) = 'DAVID'
  THEN
    RAISE_APPLICATION_ERROR(-20001, 'You are not welcome here');
  ELSE
    dbms_output.put_line('Hello ' || piv_name);
    pon_rc := 2;
  END IF;

EXCEPTION
  WHEN OTHERS
  THEN
    pon_rc := 0;
END PRC_HELLO_WORLD;

Then, I changed shell script to get name parameter from console and add an out parameter to my procedure call. I also changed the output control if statement and make it just show the output parameter.

#!/bin/bash
echo "Script started"
echo ""

v_my_name=$1

sqlplus -s hr/hr@localhost:1521/orcl << end_sql
WHENEVER SQLERROR EXIT 1 ROLLBACK
SET SERVEROUTPUT ON
variable rc NUMBER;
exec hr.prc_hello_world('$v_my_name', :rc);
exit :rc;
end_sql

echo "Your out parameter is: $?"

With these changes, I made three different call to my script:

Return Value 1

Return Value 2

Return Value 3

Well, we have done good so far. And there is one more topic that I want to mention in this post, and it is how you can use database table as a parameter for shell script variables. I don’t know that the following way is the best practice for this but, in my recent project we have created a parametric shell script that changes according to values on the database table using this method. The core technique is actually using standard output for parameters and parsing the values on shell script.

Let’s say, we need to copy files, that have some kind of pattern, from some source folders to different target folders for processing and the source path and target path are different from each other. We want to decide which file to where to put using ‘datatype’. Considering these, we can create a parameter table like below and fill it with values for example:

CREATE TABLE HR.SHELL_SCRIPT_PARAMS (
  DATATYPE VARCHAR2(200),
  SOURCE_DIR VARCHAR2(4000) NOT NULL,
  TARGET_DIR VARCHAR2(4000) NOT NULL,
  FILE_PATTERN VARCHAR2(4000) NOT NULL,
  CONSTRAINT PK_SHELL_SCRIPT_PARAMS PRIMARY KEY (DATATYPE)
);

INSERT INTO HR.SHELL_SCRIPT_PARAMS (DATATYPE, SOURCE_DIR, TARGET_DIR, FILE_PATTERN) VALUES ('filetype1', '/home/source/type1', '/home/target/type1', 'file1*.txt');
INSERT INTO HR.SHELL_SCRIPT_PARAMS (DATATYPE, SOURCE_DIR, TARGET_DIR, FILE_PATTERN) VALUES ('filetype2', '/home/source/type2', '/home/target/type2', 'file2*.txt');
INSERT INTO HR.SHELL_SCRIPT_PARAMS (DATATYPE, SOURCE_DIR, TARGET_DIR, FILE_PATTERN) VALUES ('filetype3', '/home/source/type3', '/home/target/type3', 'file3*.txt');
INSERT INTO HR.SHELL_SCRIPT_PARAMS (DATATYPE, SOURCE_DIR, TARGET_DIR, FILE_PATTERN) VALUES ('filetype4', '/home/source/type4', '/home/target/type4', 'file4*.txt');

commit;

Then, we can create a procedure that write necessary parameters to standard output for a given datatype. Optionally, I added a delimiter parameter to the procedure to manage delimiter only in one place. For best practice, exception must be logged in exception block, but for this example I skipped this.

CREATE OR REPLACE PROCEDURE HR.PRC_SHELL_SCRIPT_PARAMS(piv_datatype IN VARCHAR2, piv_delimiter IN VARCHAR2)
AS
    v_params VARCHAR2(4000);
BEGIN
    SELECT source_dir || piv_delimiter 
        || target_dir|| piv_delimiter
        || file_pattern
    INTO v_params
    FROM HR.SHELL_SCRIPT_PARAMS t
    WHERE DATATYPE = piv_datatype;

    DBMS_OUTPUT.PUT_LINE(v_params);
END PRC_SHELL_SCRIPT_PARAMS;

Then I changed shell script to call this procedure with given datatype and specified delimiter. I also added a lot of SQLPlus parameter to manage standard output nicely. As an example, I didn’t add copy part to below script. I just echo variables to console.

#!/bin/bash
echo "Script started"
echo ""

DATATYPE=$1
DELIMITER="#"

  params=`sqlplus -s hr/hr@localhost:1521/orcl << end_sql
WHENEVER SQLERROR EXIT 1 ROLLBACK
SET SERVEROUTPUT ON
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET ECHO OFF
SET LINESIZE 2000
SET TRIMSPOOL ON
SET TRIMOUT ON
SET TERMOUT OFF
SET WRAP OFF
exec hr.prc_shell_script_params('$DATATYPE', '$DELIMITER');
exit 0;
end_sql`

if [ $? != 0 ]
then
  echo ""
  echo "Failed to load parameters from database"
  echo ""
  exit -1
fi

SOURCE_DIR=`echo $params | cut -d$DELIMITER -f1`
TARGET_DIR=`echo $params | cut -d$DELIMITER -f2`
FILE_PATTERN=`echo $params | cut -d$DELIMITER -f3`

echo ""
echo "DATATYPE: $DATATYPE"
echo "SOURCE_DIR: $SOURCE_DIR"
echo "TARGET_DIR: $TARGET_DIR"
echo "FILE_PATTERN: $FILE_PATTERN"
echo ""

After the changes, I made two different call for one of them successful and the other is unsuccessful. Results are on below:

Shell Script Success Result

Shell Script Error Result

Developers Rock!!!

Advertisements
This entry was posted in Linux, Oracle, Shell Script and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s