Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Problem in quitting/exiting from sqlplus

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problem in quitting/exiting from sqlplus
# 8  
Old 08-28-2012
Quote:
Originally Posted by JuanPerez
---------- Post updated at 07:48 AM ---------- Previous update was at 07:42 AM ----------




It works!!!! SmilieSmilie

And what if I needed to save more than one count value? I mean if I used "gorup by"... Would I have to use cursors or something like that?

Thank you and thanks everybody!
And what exactly do you want to do with those values?
# 9  
Old 08-29-2012
Quote:
Originally Posted by radoulov
And what exactly do you want to do with those values?
I'm not sure if I know what you mean... I need those values for my job! Smilie

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:

Code:
#!/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...

Does anybody know why?

Thanks!
# 10  
Old 08-29-2012
Quote:
Originally Posted by JuanPerez
I'm not sure if I know what you mean... I need those values for my job! Smilie

I mean, I need them to be saved in certain different files...
More we know, better advices you get. In certain cases all can be done with SQL (PL/SQL) without the shell, for example.

Quote:
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:

Code:
#!/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...
You could save the output in a file and then read the file:

Code:
#!/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

# 11  
Old 08-29-2012
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)
# 12  
Old 08-29-2012
Hi vbe,

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! Smilie

Thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Problem exiting a WHILE loop in ksh

Hi I am having a problem exiting a WHILE loop. I am on a Sun server using ksh. I am running a Veritas Cluster Software (High Availablity) command to obtain a group status and grepping the command output for status "G" which means that the filesystem is frozen and therefore not available to... (3 Replies)
Discussion started by: bigbuk
3 Replies

2. AIX

SQLPLUS problem

Hi guys, Here is the error i get by running a "sqlplus -v" after installing an oracle client 10.2.0.5 on an AIX 5.3.9 server. Could not load program sqlplus: Symbol resolution failed for sqlplus because: Symbol __pthread (number 307) is not exported from dependent ... (3 Replies)
Discussion started by: Chapel
3 Replies

3. Shell Programming and Scripting

SQLplus and Shell script problem

sql_rows=`sqlplus -s / <<EOF set heading off set pagesize 1000 set tab off set linesize 120 wrap off column "Path" format a15 --column "No_Of_files" format a10 select tablespace_name, substr(file_name,1,instr(file_name,'/',1,2)) as "Path" , count(*) as "No_Of_files" from dba_data_files ... (7 Replies)
Discussion started by: desibabu
7 Replies

4. Shell Programming and Scripting

problem in quitting from sqlplus

Hi all, I have a scenario where im connecting to sqlplus executing a query,redirecting the output to a file and manipulating the file MY CODE IS AS FOLLOWS sqlplus -s /nolog << EOF > $UTILITIES_HOME/temp/analyze.temp.log & set linesize 3000 set trimspool on set pagesize... (2 Replies)
Discussion started by: niteesh_!7
2 Replies

5. Shell Programming and Scripting

Quitting a bash script... any alternatives to exit?

Folks, Below is a basic synopsis of the problem. I have a script that I need to check for some env vars and fail (exit the script) if they are not there. At the same time I need to set some default env vars. To do this I must run the script from the parent shell or source the script. Doing... (3 Replies)
Discussion started by: bashN00b
3 Replies

6. Shell Programming and Scripting

Quitting from a script, either sourced or not

This is a very simple problem, I am wondering why I can find no answer anywhere... I have a script that can be run either sourced or not. This script has some place where it needs to quit execution (e.g., when an error is found) If I "exit", the sourced call would exit the parent shell, but... (7 Replies)
Discussion started by: MadMage
7 Replies

7. Shell Programming and Scripting

Problem in exiting a loop

Hi my code looks like: if test $STEP -le 10 then . . ls -1d AM*-OUT|while read MYDIR do cd $MYDIR ls |tail -n1| while read MYFILE do . . if test -s $MYFILE then sqlldr .... rc=$? if test $rc -ne 0 (3 Replies)
Discussion started by: anijan
3 Replies

8. Solaris

Emacs changes the term colors after quitting

Ok. So I've finally compiled the latest version of GNU emacs on Solaris 5.8. I've set putty up so that it sets TERM=xtermc. emacs now has syntax highlighting. The problem is, whenever I quit emacs, it changes the screen colors. The background is now the same as the color of the minibuffer. It... (1 Reply)
Discussion started by: m9dhatter
1 Replies

9. UNIX for Advanced & Expert Users

Problem while calling Oracle 10g SQLPLUS files

Hi all, Iam facing a lot of problem while calling Oracle 10g SQLPLUS files from shell. What is the standard procedures to be taken care. Any help would be useful for me. Thanks in advance, Ganapati. (2 Replies)
Discussion started by: ganapati
2 Replies

10. Shell Programming and Scripting

KSH Sqlplus problem

Hi, trying this =============================== Temp=`sqlplus -s user/passwd <<EOF WHENEVER SQLERROR EXIT 1 set serverout on set feedback off set heading off select nam from tabel1 where x=y; EOF` echo Temp>>$logfile ================================= The select statement is... (2 Replies)
Discussion started by: amitkr
2 Replies
Login or Register to Ask a Question