Connecting to SQLPlus in Shell Scripts and Running SQL Scripts

Introduction

In automated operations and database management scenarios, there is often a need to execute Oracle database operations within Shell scripts. SQLPlus, as Oracle's official command-line tool, serves as a crucial instrument for connecting to Oracle databases and executing SQL scripts. This article delves into integrating SQLPlus within Shell scripts to achieve automated execution of PL/SQL commands.

Basic SQLPlus Connection Methods

SQLPlus offers various connection methods, with the most common approach in Shell scripts being the use of here documents to pass SQL commands. The basic syntax structure is as follows:

sqlplus -s username/password@server << EOF
SQL_command1;
SQL_command2;
EOF

The -s parameter enables silent mode, suppressing SQLPlus banners and prompts for cleaner output.

Executing External SQL Script Files

When executing SQL scripts stored in external files, the @ command proves particularly useful. This method is well-suited for running complex PL/SQL programs or batch SQL statements.

sqlplus -s admin/password@server << EOF
whenever sqlerror exit sql.sqlcode;
set echo off;
set heading off;
@pl_script_1.sql
@pl_script_2.sql
exit;
EOF

The above code demonstrates a complete execution workflow:

• whenever sqlerror exit sql.sqlcode: Configures error handling to automatically exit and return error codes upon SQL execution failures
• set echo off: Disables command echoing for clearer output
• set heading off: Suppresses column heading display
• @pl_script_1.sql: Executes external SQL script files
• exit: Exits SQLPlus upon completion

Secure Connection Methods

For enhanced security, using the /nolog parameter is recommended to avoid exposing usernames and passwords in process lists:

sqlplus -s /nolog << EOF
CONNECT admin/password@server;
whenever sqlerror exit sql.sqlcode;
set echo off;
set heading off;
@pl_script_1.sql
@pl_script_2.sql
exit;
EOF

This method initiates SQLPlus in no-login mode first, then establishes connections within the session, thereby improving security.

Error Handling and Output Control

Comprehensive error handling mechanisms are crucial in automated scripts. The whenever sqlerror command captures SQL execution errors and takes appropriate actions:

whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;

Output control commands help format result outputs:

• set pagesize 0: Disables paged display
• set feedback off: Turns off execution feedback messages
• set verify off: Disables variable substitution verification

Alternative Execution Methods

Beyond here documents, commands can be passed to SQLPlus via piping:

echo "exit" | sqlplus -s user/pass@server @script.sql

Or using input redirection:

sqlplus -S dbuser/dbpasswd@dbsid @scriptname </dev/null

These methods may offer greater simplicity and efficiency in specific scenarios.

Security Best Practices

Security is a critical consideration when handling database connections in Shell scripts:

• Avoid hardcoding passwords in scripts; consider using configuration files or environment variables
• Ensure appropriate permission controls when using OS authentication (/ as sysdba)
• Regularly review and update connection credentials
• Consider encrypted credential storage solutions

Practical Application Example

The following complete production environment script example demonstrates comprehensive best practice application:

#!/bin/bash

# Database connection parameters
DB_USER="admin"
DB_PASS="password"
DB_SERVER="server"

# Execute SQL script
sqlplus -s /nolog << EOF
CONNECT ${DB_USER}/${DB_PASS}@${DB_SERVER};

-- Set execution environment
whenever sqlerror exit sql.sqlcode;
set echo on;
set heading on;
set linesize 200;
set pagesize 50;

-- Execute main script
@main_script.sql

-- Record execution log
INSERT INTO execution_log VALUES (SYSDATE, 'SCRIPT_EXECUTION', 'SUCCESS');
COMMIT;

exit;
EOF

# Check execution result
if [ $? -eq 0 ]; then
    echo "SQL script execution successful"
else
    echo "SQL script execution failed"
    exit 1
fi

Conclusion

Executing Oracle database operations through SQLPlus within Shell scripts represents an efficient and reliable automation solution. By selecting appropriate connection methods, configuring comprehensive error handling mechanisms, and adhering to security best practices, robust database automation scripts can be constructed. In practical applications, it is recommended to choose the most suitable method based on specific requirements while fully considering security and maintainability factors.