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

Is this a question about powershell or HTML? Based on your title I assume powershell. If so you should remove the HTML stuff as it’s unrelated. — Doug Maurer
– Doug Maurer, Commented Oct 19, 2020 at 18:25
Hi, this is related with powershell and oracle db. i just need data in table format. can you help me? — Ganesh
– Ganesh, Commented Oct 19, 2020 at 18:59
I don’t know what you mean exactly by table format. Are you saving this to a file? First you should get it outputting the correct data in powershell, then work on next piece. — Doug Maurer
– Doug Maurer, Commented Oct 19, 2020 at 19:05
Hi, I just need to fetch the all the data from oracle table using sqlplus command — Ganesh
– Ganesh, Commented Oct 19, 2020 at 19:19

devnull · Accepted Answer · 2020-10-19 23:42:43Z

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

answered Oct 19, 2020 at 23:42

devnull

5803 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ganesh Over a year ago

Thank you @devnull, this solution has worked for me.

Collectives™ on Stack Overflow

Display DB value in table format using PowerShell - sqlplus command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related