This is my first post and I would be very thankful if you can help me. I've already searched in the forum and I've found a very similar thread in wich my problem is solved, but the thread is closed and the solution given in it doesn't work in my shell:
153194-problem-quitting-sqlplus
(Sorry but the system doesn't allow me to post complete URLs).
My script is the following:
#!/bin/bash
COUNT1=0
echo $COUNT1
if [[ ("$COUNT1" -eq "0") ]]; then
echo 'hello'
fi
export ORACLE_SID=TCR
sqlplus -S /nolog << EOF > output &
set linesize 3000
set trimspool on
set pagesize 55
connect / as sysdba
select 'COUNT1='||count(*) from reporter.reporter_status where alarma_rpv=8 and LASTOCCURRENCE > (sysdate - 5/1440);
quit;
EOF
if [[ ("$COUNT1" -gt "0") ]]; then
echo 'hello'
fi
The first "if" works properly but the second one doesn't, and "COUNT1" is greater than zero. Of course, the query is also redirected to the file "output". I've also tried using "EXIT" instead of "quit" but it neither works.
Further information: If I delete "-s" argument to "sqlplus" command and I open the output file, I have the following:
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 28 14:28:57 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> SQL> SQL> SQL> Conectado.
SQL>
'COUNT1='||COUNT(*)
-----------------------------------------------
COUNT1=483
SQL> Desconectado de Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
So it means it exists saccessfully from sqlplus, but I don't know why it doesn't execute the remaining shell...
Thanks!
---------- Post updated at 07:36 AM ---------- Previous update was at 07:32 AM ----------
Hi!
I mean I have a value greater than zero because it is written in the output file. I've also tried with another commands like "rm output" instead of a conditional clause, but it neither works, because the output file is still there. What I mean is the execution stops when it exits sqlplus...
If I understand correctly, you need something different.
Try this:
COUNT1=$(
sqlplus -S / as sysdba <<EOF
set trimspool on
set pagesize 0 feed off head off
select count(*) from reporter.reporter_status
where alarma_rpv=8 and LASTOCCURRENCE > (sysdate - 5/1440);
EOF
)
instead of:
sqlplus -S /nolog << EOF > output &
set linesize 3000
set trimspool on
set pagesize 55
connect / as sysdba
select 'COUNT1='||count(*) from reporter.reporter_status where alarma_rpv=8 and LASTOCCURRENCE > (sysdate - 5/1440);
quit;
EOF
Something like this:
#!/bin/bash
COUNT1=0
echo "$COUNT1"
if [[ $COUNT1 -eq 0 ]]; then
echo 'hello'
fi
export ORACLE_SID=TCR
COUNT1=$(
sqlplus -S / as sysdba <<EOF
set trimspool on
set pagesize 0 feed off head off
select count(*) from reporter.reporter_status
where alarma_rpv=8 and LASTOCCURRENCE > (sysdate - 5/1440);
EOF
)
if [[ $COUNT1 -gt 0 ]]; then
echo 'hello'
fi
Is your goal to assign the result of a sqlplus-query to a variable? The syntax for that is wrong and I wouldn't start the query in the background. quit or exit are not needed btw. Try this:
#!/bin/bash
COUNT1=0
echo $COUNT1
if [[ ("$COUNT1" -eq "0") ]]; then
echo 'hello'
fi
export ORACLE_SID=TCR
COUNT1=$(sqlplus -S /nolog << EOF > output
set linesize 3000
set trimspool on
set pagesize 55
set heading off
connect / as sysdba
select count(*) from reporter.reporter_status where alarma_rpv=8 and LASTOCCURRENCE > (sysdate - 5/1440);
EOF
)
if [[ ("$COUNT1" -gt "0") ]]; then
echo 'hello'
fi
I'm not sure if I know what you mean... I need those values for my job!
I mean, I need them to be saved in certain different files...
I have another question if you don't mind to answer me:
Your code was very useful, but I cannot use it to save the information in a file. I mean, if I modify your code to the following, I get the same than before, the second if clause does not execute itself after the file is created:
#!/bin/bash
COUNT1=0
if [[ ("$COUNT1" -eq "0") ]]; then
echo 'Hello'
fi
export ORACLE_SID=TCR
COUNT1=$(
sqlplus -S / as sysdba << EOF > output
set trimspool on
set pagesize 0 feed off head off
select count(*) from reporter.reporter_status
where alarma_rpv=8 and LASTOCCURRENCE > (sysdate - 5/1440);
EOF
)
if [[ ("$COUNT1" -gt "0") ]]; then
echo $COUNT1
fi
I can't understand it because supposedly << redirection works till EOF clause is found, so file "output" should be created (as it happens) and after that the execution should continue, but it stops...
More we know, better advices you get. In certain cases all can be done with SQL (PL/SQL) without the shell, for example.
You could save the output in a file and then read the file:
#!/bin/bash
COUNT1=0
if [[ $COUNT1 -eq 0 ]]; then
echo 'Hello'
fi
export ORACLE_SID=TCR
COUNT1=$(
sqlplus -S / as sysdba << EOF > output
set trimspool on
set pagesize 0 feed off head off
select count(*) from reporter.reporter_status
where alarma_rpv=8 and LASTOCCURRENCE > (sysdate - 5/1440);
EOF
)
COUNT1=$(< output)
if [[ $COUNT1 -gt 0 ]]; then
echo "$COUNT1"
fi
You did not get my question... this is why radoulov's solution works and not yours...
You cant say that COUNT1 is greater only because "I have a value greater than zero because it is written in the output file.", did you check its content? I tell you its 0...
In other words when you call SQL you are opening a new shell/process at end it closes...
And where did you assign COUNT1 its new value? the first shell knows nothing of what its child was up to... But you have it if output... Radoulov's solution is by far more elegant...(assign the result value of the SQL execution to your COUNT1 variable)
I understand what you mean and I've tried it by modifying the second if clause and comparing to zero value instead of comparing to a greater than zero value and you are right but...
Ampersand character was already removed, so it makes no sense sql query executes in a child process and COUNT1 variable is equal to zero in the father process... because there should not be a father process but just one only process!
