Pass sqlplus value to shell variable

Question

the image below shows what returns when i run sqlplus in shell

but when I run this from the run command:

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

with voicetrigger2.ps1 as follows:

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

I get this:

I have to wait 3 back. The problem is that I'm trying to set this as a variable, and if the integer is greater than zero, run the BAT file. But I don't think SQLPlus returns a JUST integer value. I think this really returns this:

 count(*) 3 

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

Answer 1

SQL * Plus does not return anything; it maps the result of the query to standard output. To get a direct call to only display 3 , you can set heading off in an SQL script, and also call SQL * Plus with the -s flag to suppress the banner. You probably also want to exit at the end of the SQL script so that it does not stay in the SQL> prompt.

The same applies to calling powershell, but something else happens there; 17 - line number, which means that he expects more input and did not execute the command in the SQL script, which implies either a query without a terminator ; either / or PL / SQL block without interruption. But if this is the same SQL that you ran in the first example, this is a little strange, as they should behave the same. You must add the contents of the SQL script to the question to find out what might be wrong.

The only thing I can think about is to change the behavior as if you had login.sql , which includes the set sqlterminator , but you have to collect different login.sql files from two calls ... which is plausible, if powershell has its own environment variables, maybe.

Answer 2

This will not work. As Alex said, sqlplus does not return anything as a function - it only writes all the output that you generate to standard output.

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

Something like that:

 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> 

Answer 3

 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 

Answer 4

try adding SET HEADING OFF to the top of your file. Mark this answer (mark the second best voted answer)