Display DB value in table format using PowerShell - sqlplus command

Question

I need to write a query to display multiple rows from DB in tabular format using PowerShell script (sqlplus command).

Database: Oracle 12c

I wrote the below query, but it returns single row value. How can I get all the data? I am not understanding how to implement for loop to access all rows.

Expected output:

Script:

$username='root'
$password='password'
$tnsalias='localhost:1521/xe'

$sql1=@"
  set echo off;
  set pagesize 0
  set head off;
  set feedback off;
  set pause off;
  set verify off;
  set trimspool on;
  set linesize 300;
  set colsep ,;
  set termout off;
 SELECT * FROM employees  where dept_id =30;

"@
$output1 = $sql1 |sqlplus -silent $username/$password@$tnsalias 
$output2 =$output1.Split(",") 
 
               $eid=$output2[0] 
              $first_nm=$output2[1]
              $middl_nm=$output2[2]
              $last_nm=$output2[3]
              $sal= $output2[4]

               $HtmlTable3 += "<tr style='font-size:13px;background-color:#FFFFFF'>
                              <td>"+ $eid +"</td>
                              <td>" + $first_nm + "</td>
                              <td>"+ $middl_nm +"</td>
                              <td>"+ $last_nm+"</td>
                              <td>"+ $sal +"</td>
                              </tr> "
        
           $HtmlTable3 +="</table> </div>"
           write-host $HtmlTable3

Answer 1

what about shifting HTML tags into SQL - like :

$username='scott'
$password='tiger'
$tnsalias='foo:12102/DB1212'

$sql1=@"
  set echo off;
  set pagesize 0
  set head off;
  set feedback off;
  set pause off;
  set verify off;
  set trimspool on;
  set linesize 300;
  set colsep ,;
  set termout off;
 SELECT   
    '<tr style="font-size:13px;background-color:#FFFFFF">'||
    '<td>'||empno||'</td>'
  ||'<td>'||ename||'</td>' 
  ||'<td>'||job||'</td>' 
  ||'<td>'||mgr||'</td>' 
  ||'<td>'||hiredate||'</td>' 
  ||'<td>'||sal||'</td>' 
  ||'<td>'||comm||'</td>' 
  ||'<td>'||deptno||'</td>' 
  ||'</tr>'
  FROM EMP where DEPTNO=30;

"@
$output1 = $sql1 |sqlplus -silent $username/$password@$tnsalias 
$HtmlTable3 += "<table>"  
$HtmlTable3 += $output1
$HtmlTable3 += "</table>"
write-host $HtmlTable3

returning

7499    ALLEN   SALESMAN    7698    20-FEB-81   1600    300     30
7521    WARD    SALESMAN    7698    22-FEB-81   1250    500     30
7654    MARTIN  SALESMAN    7698    28-SEP-81   1250    1400    30
7698    BLAKE   MANAGER     7839    01-MAY-81   2850            30
7844    TURNER  SALESMAN    7698    08-SEP-81   1500    0       30
7900    JAMES   CLERK       7698    03-DEC-81   950             30