Knowledge Warehouse: Unix-Oracle Scripts

In this article we will see how Oracle database interact with Unix. We will see how to query the Oracle database and other Database operation using Unix shell scripts. Below are some of the important point which we will cover other than the normal operations.

How to Connect Unix with Oracle
How to Create Log file from Unix to Oracle Interface
How to Create a Spool File
How to Execute a Procedure Through Unix Script
How to pass parameter to Procedure/Function in Unix Script
How to Execute a Procedure with Out Parameter in Unix Script

*************************************

How To Connect Unix With Oracle

*************************************

Let say we have a shell script DbConnection.sh and below is the content of the script.

$ cat DbConnection.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL

select * from dual;

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh DbConnection.sh

Output: Execution of the script will produce below output.

Dum

----

Explanation:

First 3 lines of the script are used to initialise the User name, Password & Database.
sqlplus utility is used to connect oracle database from Unix scripts.
Anything other than SQL/PLSQL will not be allowed inside the ENDSQL tags. This area belongs to oracle, we have to provide code related to oracle only.

***********************************************
How To Create Log File Of Oracle Execution

************************************************

$ cat DbConnection.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

V_Execution_Log="Oracle_Execution.log"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} >${V_Execution_Log} << ENDSQL

select * from dual;

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh DbConnection.sh

Output: Will not display anything on terminal but will create a log file in directory where script is executed.

$ cat Oracle_Execution.log

Dum

----

Observe closely, earlier the output which was displayed on terminal is now available in the log file. We have directed the output of sqlplus command to a log file (>${V_Execution_Log}) Log file helps in case of any failure, we can read the log file and can see the error messages in case of any failure.

*********************************************

How To Get Data Without Column Names

*********************************************

Some times, after certain rows columns header keeps on repeating in the data extract that is something which we do not want at least in data feed. Let see how to avoid the repetition of columns(header) in data extract.

$ cat DbConnection.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
SET PAGESIZE 0;

select * from dual;

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh DbConnection.sh
X

Output: Will display only X on terminal not column name (Dum). This is because of the property SET PAGESIZE 0;

***********************************************

How To Get The Rows Data In A Single Line

***********************************************

Let say we have below table database KW_Example and we want to fetch the data of this table through unix script. Let see how can we do the same and what kind of issue we may face.

Emplid	Ename	DeptNo
1001	A1	HR
1002	A2	FIN

$ cat DbConnection.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
SET PAGESIZE 0;

select * from kw_example;

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh DbConnection.sh

1001
A1
HR

1002
A2
FIN

You may or may not get output like above, it depends on the linesize. If your linesize is smaller than your columns total length, it will break into multiple lines as shown above. So if you want your data appear correctly, add set linesize attribute.

$ cat DbConnection.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
SET PAGESIZE 0;
SET LINESIZE 1000;

select * from kw_example;

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh DbConnection.sh

1001 A1 HR
1002 A2 FIN

The spaces which we are seeing between the two columns is due to the length of the columns.

******************************

How To Create A Spool File

******************************

Let say we want to create a delimited file a.txt from DB table kw_example. We will use spool functionality of oracle to achieve the same.

$ cat Spool_File.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
spool /home/kwetl/kw_example.txt
SET PAGESIZE 0;
SET LINESIZE 1000;

select emplid||','||ename||','||dept_no from kw_example;
spool off;

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh Spool_File.sh

Output: It will create file kw_example.txt at location /home/kwetl

and will contain data like below.

$ cat kw_example.txt

1001,A1,HR

1002,A2,FIN

We are seeing an empty line between the records, to avoid such line in feed we should use SET TRIMSPOOL ON property. Let see how it works.

$ cat Spool_File.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
spool /home/kwetl/kw_example.txt
SET PAGESIZE 0;
SET LINESIZE 1000;
SET TRIMSPOOL ON;

select emplid||','||ename||','||dept_no from kw_example;
spool off;

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh Spool_File.sh

Output: This time we have received expected output.

$ cat kw_example.txt

1001,A1,HR

1002,A2,FIN

****************************************************************

How To Pass IN Parameter To An Oracle Stored Procedure

****************************************************************

Lets create a small procedure with IN parameter.

CREATE OR REPLACE PROCEDURE PROC_EXAMPLE (P_INPUT IN VARCHAR2)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome..!!'||P_INPUT);
END;

We have a shell script Execute_Proc.sh which will call the stored procedure.

$ cat Execute_Proc.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

V_Input_Data="KnowledgeWarehouse"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
EXEC PROC_EXAMPLE('${V_Input_Data}');

ENDSQL
#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh Execute_Proc.sh

Output: Will return below output.
Welcome..!!, KnowledgeWarehouse

******************************************************************

How To Pass OUT Parameter To An Oracle Stored Procedure

******************************************************************

Lets create a small procedure with OUT parameter.

CREATE OR REPLACE PROCEDURE PROC_EXAMPLE (P_OUTPUT OUT VARCHAR2)
AS
BEGIN
P_OUTPUT :='Welcome, user..!!';
END;

We have a shell script Execute_Proc.sh which will call the stored procedure.

$ cat Execute_Proc.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
DECLARE
V_OUTPUT VARCHAR2(100);
BEGIN
PROC_EXAMPLE(V_OUTPUT);
DBMS_OUTPUT.PUT_LINE(V_OUTPUT);
END;
/

ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

$ sh Execute_Proc.sh

Output: Will return below output.
Welcome, user..!!

We can use below syntax as well

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
SET SERVEROUTPUT ON;

VAR V_OUTPUT VARCHAR2(100);
PROC_EXAMPLE(:V_OUTPUT);
print(V_OUTPUT);
ENDSQL

In first syntax we have used standard anonymous block syntax while in other we have used bind variable syntax. Please note, in case of anonymous block, block must be end by forward slash (/).

*********************************************
How To Execute A .sql File In Unix Script
*********************************************

We can execute a .sql file in Unix script. We can save our query in a .sql file and can use the same in Unix script. Let see how it works. Let say we have a file Execute_Sql.sql

$ cat Execute_Sql.sql

SELECT * FROM KW_EXAMPLE;

We have below shell script to execute Execute_Sql.sql
$ cat Execute_Proc.sh

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh

V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
@Execute_Sql.sql
ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------
Output: Shell script will return all the record available in kw_example table. Observe carefully we have used @ symbol to execute .sql file.
Note: If your query id too long always put that sql in a .sql file and then use it as explained above instead of using that query directly in sqlplus.

******************************************************
How To Execute An Anonymous Block In .sql File
******************************************************

We can not only execute sql query in .sql file but also execute an anonymous block inside a .sql file.We have a file Execute_Block.sql

$ cat Execute_Block.sql

set serveroutput on;
DECLARE
V_Count NUMBER;
BEGIN
select count(1) into V_Count from kw_example;
dbms_output.put_line(V_Count);
END;
/

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh
V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"
V_Execution_Log="Oracle_Execution.log"

sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} >${V_Execution_Log} << ENDSQL

@Execute_Block.sql
ENDSQL

#-----------------------<<<END OF SCRIPT>>>--------------------

Output: The output of anonymous block is available in log file Oracle_Execution.log

$ cat Oracle_Execution.log
2
PL/SQL procedure successfully completed

        **************************************************************************
        How To Get The Returned Value From Pl/SQL Block Into Shell Script
        **************************************************************************
$ cat Execute_Block.sql
SET SERVEROUTPUT ON;
DECLARE
V_Count NUMBER;
BEGIN
        select count(1) into V_Count from kw_example;
        dbms_output.put_line(V_Count);
END;
/
Always use slash(/) in the end of PL/SQL block or else you will not get the desired output and bad thing is you will not get the error as well.

#-----------------------<<<START OF SCRIPT>>>------------------

#!bin/sh
V_Oracle_User="scott"

V_Password="tiger"

V_Oracle_Sid="kwdev"

V_Record_Count=`sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
@Execute_Block.sql
ENDSQL`
echo ${V_Record_Count}
#-----------------------<<<END OF SCRIPT>>>--------------------
$ sh Execute_Block.sh

Output: We will receive below output as a result of script execution.

2 PL/SQL procedure successfully completed.

If you want only number of records and not feed ("PL/SQL procedure successfully completed") then use SET FEED OFF property as shown below.

V_Record_Count=`sqlplus -s ${V_Oracle_User}/${V_Password}@${V_Oracle_Sid} << ENDSQL
SET FEED OFF;
@Execute_Block.sql
ENDSQL`
echo ${V_Record_Count}

Note: We can use similar approach to get the return value from an oracle function in Unix variable.

Knowledge Warehouse

Pages

Unix-Oracle Scripts

1 comment: