bash oracle - ghdrako/doc_snipets GitHub Wiki

#!/bin/bash
RETVAL=`sqlplus -silent scott/tiger <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT * FROM emp;
EXIT;
EOF`
if [ -z "$RETVAL" ]; then
  echo "No rows returned from database"
  exit 0
else
  echo $RETVAL
fi

sqlplus /nolog @your_script.sql

#!/usr/bin/env bash
username="username"
password="XXXXXXXXXXX"
db_ip="ipaddress"
port="1521"
sid=""
schema="schema"


echo "

select * from $schema.$tablename ;
## you can place all your queries here.
commmit;
exit
" | sqlplus -s "$username/$password@$db_ip:$port/$sid"

file-with-sql-1.sql:
 select * from users where username='&1';

#!/bin/bash
MY_USER=bob
sqlplus <user>/<password> @file-with-sql-1.sql $MY_USER

sqlplus /nolog << EOF
CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
EOF

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

@pl_script_1.sql
@pl_script_2.sql

exit;
EOF

sqlplus -s /nolog << EOF
CONNECT admin/password;

whenever sqlerror exit sql.sqlcode;
set echo off 
set heading off

@pl_script_1.sql
@pl_script_2.sql

exit;
EOF 

sqlplus -s <<EOF>> LOG_FILE_NAME user/passwd@host/db
#Your SQL code
EOF

# Environment variables necessary for Oracle Instant Client
export LD_LIBRARY_PATH=/home/tomcat/scripts/instantclient_11_2
export PATH=$PATH:$LD_LIBRARY_PATH

function check_db {
  CONNECTION=$1
  RETVAL=`sqlplus -silent $CONNECTION <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT 'Alive' FROM dual;
EXIT;
EOF`

  if [ "$RETVAL" = "Alive" ]; then
    DB_OK=0
  else
    DB_OK=1
  fi
}

scriptPath=${0%/*}
source $scriptPath/check_db.sh

CONNECTION="up_check_user/password@//hostname:1523/service"

echo "Wait until DB is up"
check_db $CONNECTION
while [ $DB_OK = 1 ]
do
  echo "DB not up yet. Sleeping for 5 mins (CTRL+C to exit)"
  sleep 300
  check_db $CONNECTION
done

echo "Starting"
echo "DEV: /u01/dev"
/u01/dev/bin/tomcat start