pass sqlplus value to shell variable

Question

the below picture shows what gets returned when I run sqlplus in shell

shell1

but when I run this from the "run" command:

powershell.exe -noexit c:\sqltriggers\voicetrigger2.ps1

with voicetrigger2.ps1 as this:

$(sqlplus user/pass@OMP1 '@C:\sqltriggers\VOICEBLOCKTRIG.SQL');

I get this:

shell2

I should expect a 3 back. The issue is, I try to set that as a variable, and if the integer is greater than zero, run a BAT file. But I don't think the SQLPlus is returning JUST an integer value. I think its actually returning this:

count(*)
      3

How do I get it to just return the integer value from the SQLplus command?

it's not returning anything. it's not executing the query. probably you are missing `/` at the end of your query. — Kirill Leontev, May 10 '13 at 07:58

score 1 · Answer 1 · answered May 10 '13 at 07:59

SQL*Plus isn't returning anything, it's displaying the result of the query on standard output. To get the direct call to only show 3 you can set heading off in the SQL script, and also call SQL*Plus with the -s flag to suppress the banner. You probably also want an exit at the end of the SQL script so it doesn't stay sitting at the SQL> prompt.

The same applies to the powershell call, but there's something else going on there; the 17 is a line number which means it's waiting for more input and hasn't executed the commands in the SQL script, which suggests either a query without a terminating ; or /, or a PL/SQL block without a terminating /. But if it's exactly the same SQL you ran in the first example then that is a bit odd as they should behave the same. You should add the SQL script contents to the question to see what might be wrong.

The only thing I can think of that would change behaviour like that is if you had a login.sql that includes a set sqlterminator command, but you'd have to be picking up different login.sql files from the two calls... which is plausible if powershell has its own environment variables, perhaps.

score 0 · Answer 2 · answered May 10 '13 at 08:31

It won't work this way. As Alex mentioned, sqlplus doesn't return anything as a function - it only writes whatever output you generate to standard output.

You can have variables in sqlplus, but there is no easy way to pass them to host environment. The only way I can think of is to use spool command to generate another batch file, which will actually set your host variables, and then process them the way you want in your host script.

Something like this:

16:30:20 SYSTEM@sandbox> get host.sql
  1  SET VERIFY OFF TRIMSPOOL ON TERMOUT OFF HEADING OFF LINESIZE 4000 PAGES 0 FEEDBACK OFF timing off
  2  spool s:\.tmp\host.ps1
  3  select '$env:MY_VAR="'||dummy||'"' from dual;
  4  spool off
  5* exit
16:30:25 SYSTEM@sandbox> @host.sql
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PS S:\.tmp> cat host.ps1
$env:MY_VAR="X"
PS S:\.tmp> .\host.ps1
PS S:\.tmp> $env:my_var
X
PS S:\.tmp>

score 0 · Answer 3 · answered Oct 02 '14 at 17:09

0

sqlplus -s /nolog <<EOF > query_result.txt 
set heading off feedback off pagesize 0 linesize 30000 trimout on ;

select 5 from dual;
exit;
EOF

for i in `cat query_result.txt `
do
exit $i
done

answered Oct 02 '14 at 17:09

Mike

20,010
25
97
140

score -1 · Answer 4 · edited May 23 '17 at 12:31

-1

try adding SET HEADING OFF in the beginning of your file. Check this answer (check the second best voted answer)

edited May 23 '17 at 12:31

Community

1
1

answered May 10 '13 at 03:54

cha

10,301
1
18
26

pass sqlplus value to shell variable

4 Answers4