0

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.

2
  • set SET LINESIZE and SET TRIMSPOOL OFF to avoid trim of rows Feb 26 '20 at 9:52
  • Is passing this through the shell's command substitution into a shell variable, and the consequent alterations that that entails, a necessary part of this question?
    – JdeBP
    Feb 26 '20 at 10:04
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
1
  • I used same query that you have mentioned but could not get proper output. When i use SET MARKUP HTML ON then getting result in HTML tags not in tabular format. Feb 26 '20 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.