How to give to a powershell variable only the output of my sqlplus query?

Question

 $a = 'select 2 from dual' | sqlplus user/password@database.

expected result: 2

real result :

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 31 08:29:43 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Tue May 31 2022 08:24:55 +02:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> 2 SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0

I would like to get only what my query returns. How do I do that?

@vonPrynz

$a = 'select 2 from dual' | sqlplus -S user/password@database.

result is empty

Try the -s[ilent] switch to tell sqlplus not to print banners and such. That being said, consider using ODA.NET for programmatic access instead of passing parameters to a client app. — vonPryz
– vonPryz, Commented May 31, 2022 at 7:05
Oracle (clients) requires a semicolon for DML statements, so add one to the select query. That is, 'select 2 from dual;' — vonPryz
– vonPryz, Commented May 31, 2022 at 7:23

Dmitry Demin · Accepted Answer · 2022-05-31 07:35:14Z

3

You need add ';'

PS C:\> 'select 2 as result from dual;' | sqlplus -s user/password@DEV19

    RESULT
----------
         2

If you want only '2' then add this lines:

'SET FEEDBACK OFF 
set heading off
set termout OFF
SET FEEDBACK OFF
SET TAB OFF
set pause off
set verify off
SET UNDERLINE OFF
set trimspool on
set timing off
set echo off
set linesize 1000
set pagesize 100
select 2 as result from dual;' | sqlplus -s user/password@DEV19
     2

edited May 31, 2022 at 7:35

answered May 31, 2022 at 7:25

Dmitry Demin

2,1232 gold badges19 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

How to give to a powershell variable only the output of my sqlplus query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related