Sqlplus API
A series of functions used for interact directly with sqlplus client program.
API
sqlplus_set_sqlplus_auth_var
Set sqlplus_auth variable with authentication string like: USER/PASSWD@TNSNAME.
Parameters:
$1: (db) Name of the schema to use$2: (user) User to use on authentication$3: (pwd) Password to use on authentication.
Returns:
(Show/Hide)sqlplus_set_sqlplus_auth_var () {
local db=$1
local user=$2
local pwd=$3
export sqlplus_auth="$user/$pwd@$db"
[[ $DEBUG && $DEBUG == true ]] && echo "Use $sqlplus_auth"
return 0
}
sqlplus_file
Compile a file and save output to input variable.
Parameters:
$1: (var) Name of the variable where is save output command.$2: (f) File to compile.
Returns:
0: on success1: on error
(Show/Hide)sqlplus_file() {
local var=$1
local f=$2
local v=""
v=$($SQLPLUS -S -l $sqlplus_auth 2>&1 <<EOF
@$f;
exit;
EOF
)
local ans=$?
[[ $DEBUG && $DEBUG == true ]] && echo -en "Compile $f ==> $v ($ans)\n"
#v=`echo $v | sed 's/\n/\r/g'`
eval "$var=\$v"
#read -r "$var" <<< "$v"
return $ans
}
sqlplus_cmd_4var
Execute an input statement/command to configured schema.
Parameters:
$1: (var) Name of the variable where is save output command.$2: (cmd) Command/statement to execute on configured schema$3: (rm_lf) If string length is not zero than from output command are remove LF.$4: (feedback) Set feedback option value.
If equal to empty string default value is "off".
Returns:
0: on success1: on error
(Show/Hide)sqlplus_cmd_4var() {
set -f
local var=$1
local cmd="$2"
local rm_lf=$3
local v=""
local feedback="$4"
local custom_opts="$5"
local opts=""
if [[ -n "${custom_opts}" ]] ; then
opts="${custom_opts}; "
else
if [[ $feedback == '' ]] ; then
feedback="off"
fi
opts="set echo off heading off feedback $feedback;"
fi
[[ $DEBUG && $DEBUG == true ]] && echo "(sqlplus: opts = $opts)"
v=$($SQLPLUS -S -l $sqlplus_auth 2>&1 <<EOF
$opts
$cmd;
exit;
EOF
)
local ans=$?
[[ $DEBUG && $DEBUG == true ]] && echo "$cmd ==> $v ($ans)"
if [[ $ans -eq 0 && -z $v ]] ; then
return 1
fi
if [[ -n $rm_lf ]] ; then
v=`echo $v | sed 's/\n//g'`
fi
# declare -x "$var"="$v"
#read -r "$var" <<< "$v"
eval "$var=\$v"
set +f
return $ans
}