sqlplus: list query result one column per console row - bash way


#!/bin/bash
# transform horizontal sqlplus data in vertical
# vsqlplus "owner/password@alias" "SELECT * FROM TABLE_NAME;";
#
# PS1: DO NOT WORK WITH SH ONLY WITH BASH!
# PS2: USE ";" AT END OF QUERY
# PS3: ALWAYS USE NAMED COLUMNS
# SELECT COUNT(*) FROM TABLE - DO NOT WORK
# SELECT COUNT(*) AS TOTAL FROM TABLE - DO WORK

CONN_STR="$1";
SQL_QUERY="$2";

function vsplit(){
CONN_STR="$1";
SQL_QUERY="$2";
cn=0;
ROWNUM=-2;
# echo "EXECUTANDO QUERY \"${SQL_QUERY}\""
# echo "CONECTADO AO BANCO \"${CONN_STR}\""
# sqlplus -s "${CONN_STR}" < <( echo "select sysdate from dual;" );
sqlplus -s "${CONN_STR}" < <(
echo "set feedback off";
echo "set colsep \"'\"";
echo "set lines 32676";
echo "set pages 8000";
echo "set timing off";
echo "${SQL_QUERY}";
echo "EXIT" ) |
sed "/^$/d" |
tr -d '\t' |
tr -s ' ' |
while read cols;
do
((++ROWNUM == -1)) &&
eval "array_cols=( $(echo ${cols} | tr \' ' ') )";
(( ROWNUM > 0 )) && (
echo "ROWNUM=$ROWNUM";
for (( c = 0; c < ${#array_cols[*]}; c++ ));
do
echo " ${array_cols[(c) % ${#array_cols[*]}]}='$(echo "$cols"|cut -d\' -f$((c+1)) )'";
done;
)
done;
};

# vsplit "${CONN_STR}" "${SQL_QUERY}";

Comentários

Postagens mais visitadas