1

In shell script, I used 2 different SQL query & output of these 2 files in HTML format like (eg- ab.html, cd.html). Shell script as ->

#/bin/ksh

ab=$(SQLPLUS -s <username>/<password>@DB <<EOF
set heading ON
set trimspool OFF
SET MARKUP HTML ON
set feedback off
spool ab.html
select col_1, col_2, col_3 from <tab>;
spool off;
exit;
EOF)

echo "$ab" > ab.html

----- DML operation perform

cd=$(SQLPLUS -s <username>/<password>@DB <<EOF
set heading ON
set trimspool OFF
SET MARKUP HTML ON
set feedback off
spool cd.html
select col_4 from <tab>;
spool off;
exit;
EOF)

echo "$cd" > cd.html

cat ab.html cd.html > output.html
exit 0

Then getting output.html file as ->

 ______________________________
| col_1     | col_2  |   col_3 |
| .....     | ...... |   ..... |
| .....     | ...... |   ..... |
|___________|________|_________|
 ______________________________              
|             col_4            |
|             ......           |
|______________________________|

But I want final output.html like col_1, col_2, col_3, col_4 column in one table like below ->

 ______________________________________
| col_1     | col_2  |   col_3  | col_4|
| .....     | ...... |   .....  | .....|
| .....     | ...... |   .....  | .....|
|___________|________|__________|______|

Could you please help me how to merge these 2 HTML file into one output HTML file so that we can able to see like above format. I just draw HTML table as example but internal border always comes in HTML table, so please don't look HTML table format that I used.

3
  • Is the table of 1st query different from table of 2nd query? Is performing only one query that outputs the four columns a possibility? Feb 27 '20 at 9:58
  • In your scripts is redundant the redirection to the HTML files since the spool command already does that job. Feb 27 '20 at 10:22
  • @PauloTomé DML operation is also performing. SO the 2nd query will run after DML operation perform. So we can.t use all 4 columns in same query.
    – Shahin P
    Feb 27 '20 at 10:24
-1

Joining html content after the tag is not easy and recommended. We could use SQL iteslf to generate the.

SELECT t1.owner, t1.object_id, t2.name
FROM (
   SELECT owner, object_id,
          ROW_NUMBER() OVER (ORDER BY owner) as rn
   FROM dba_objects where rownum < 10)  t1
FULL OUTER JOIN  (
   SELECT name, 
          ROW_NUMBER() OVER (ORDER BY name) as rn
   FROM v$database)  t2
ON t1.rn = t2.rn

We can use ROW_NUMBER function to create a calculated field that can be used to join the two tables together since we dont have anything in common and use FULL OUTER JOIN.

1
  • (1) As you can see, Stack Exchange doesn’t allow table markup (HTML).  It primarily uses Markdown with very limited HTML. (2) Please add a lot more explanation.  You are showing a table with three columns.  The OP already has that; how does your answer help?  Why are you doing a join between two tables?  Etc. … … … … … … … … … … … … … … … … … … … … … … Please do not respond in comments; edit your answer to make it clearer and more complete.
    – Scott
    Mar 30 '20 at 4:05

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.