How to get result of SQL query in shell script with same format that when we run on Oracle SQL developer/Toad/PLSQL developer

Question

In shell script I am using SQL query.

#/bin/ksh
var=$(sqlplus -s <user>/<password>@DB <<EOF
      set heading on
      set trimspool off
      set linesize 200
      set feedback off
      spool out.txt;
      select col_a, col_b, col_c  from <table>;

      spool off;
      exit;
      EOF)

echo "$var" > out.txt
exit 0

Then after I got output like below format in out.txt file ->

  col_a
  -------
  col_b
  -------
  col_c
  -------
    1
    abct
    23
  col_a
  -------
  col_b
  -------
  col_c
  -------
   2
   desf
   35
   ......

I want output like in CSV file->

  col_a    col_b     col_c
   1        abct      23
   2        desf      35
   ....     ....      ....

Could you please help me to got like above format or how can i convert SQL query result set into HTML in shell script. I want to get SQL query result same as we got when run on SQL developer/Toad/PLSQL developer.

Answer 1

To output to html insert line like this:

SET MARKUP HTML

so your script will be something like

#/bin/ksh
var=$(sqlplus -s <user>/<password>@DB <<EOF
      set heading on
      set trimspool off
      set linesize 200
      set feedback off
      SET MARKUP HTML ON
      spool out.html;
      select col_a, col_b, col_c  from <table>;

      spool off;
      exit;
      EOF)

echo "$var" > out.txt
exit 0