sqlplus: export as csv - bash way


devtools:/srv/scripts # cat export_csv.sh
#!/bin/bash
# export a sql result as csv format
# source export_csv.sh
# export_csv "owner/password@database_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 export_csv(){
CONN_STR="$1";
SQL_QUERY="$2";
cn=0;
ROWNUM=-2;
array_cols=();
# 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 "alter session set nls_date_format='dd/mm/rrrr hh24:mi:ss';";
echo "${SQL_QUERY}";
echo "EXIT" ) | tee query_result |
sed "/^$/d" |
tr -d '\t' |
tr -s ' ' |
while read cols;
do
((++ROWNUM == -1)) &&
eval "array_cols=( $(echo ${cols} | tr -s ' '| tr \' ' ') )" &&
echo -n "ROWNUM,$(echo ${cols} | tr -s ' ' | tr \' ' ' | sed "s/ /,/g" )";
((ROWNUM == 0)) && (
echo ";";
)
(( ROWNUM > 0 )) && (
NCOLS=${#array_cols[*]};
(( NCOLS == 0 )) && NCOLS=1;
for (( c = 0; c < $NCOLS; c++ ));
do
(( c == 0 )) && echo -n "'${ROWNUM}','$(echo "$cols"|cut -d\' -f$((c+1)) )'" || echo -n ",'$(echo "$cols"|cut -d\' -f$((c+1)) )'";
done;
echo ";";
)
done;
};

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

Comentários

Postagens mais visitadas