Sponsored Content
Top Forums Shell Programming and Scripting Connecting sqlplus from UNIX with multiple select statement Post 302773867 by lovelysethii on Friday 1st of March 2013 12:15:47 AM
Old 03-01-2013
Connecting sqlplus from UNIX with multiple select statement

hi,
i have a requirement where i need to connect sqlplus from unix and i am able to do so by following command:
Code:
cust_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID   << EOF
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                select count(*) from BCHISTEVENT b, bcuser u
                where b.type != 'com.avolent.apps.event.LogoutEvent'
                and b.actinguserid = u.identifier
                and u.type = 'CustomerView'
                /*and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between
                to_char((SYSTIMESTAMP - INTERVAL '30' MINUTE),'dd/mm/yy hh24:mi:ss') and to_char(SYSTIMESTAMP,'dd/mm/yy hh24:
mi:ss'))*/
                AND b.bucket = to_char(sysdate, 'YYYYMM')
                group by u.type;
EOF`


but i have one more select statement here and if i am using the same above command then i am getting no output while echoing the variabe value:
Code:
biller_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID   << EOF
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                select count(*) from BCHISTEVENT b, bcuser u
                where b.type != 'com.avolent.apps.event.LogoutEvent'
                and b.actinguserid = u.identifier
                and u.type = ' BillerAdminView'
                /*and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between
                to_char((SYSTIMESTAMP - INTERVAL '30' MINUTE),'dd/mm/yy hh24:mi:ss') and to_char(SYSTIMESTAMP,'dd/mm/yy hh24:
mi:ss'))*/
                AND b.bucket = to_char(sysdate, 'YYYYMM')
                group by u.type;
EOF`


echo "Total # of users Logged in @ last 30 minutes . Customer View    :$cust_count">>vmstat.txt;
echo "Total # of users Logged in @ last 30 minutes . Biller View      :$biller_count">>vmstat.txt;


plss suggest how to acheive this....thanks in advance

Last edited by Franklin52; 03-01-2013 at 03:10 AM.. Reason: Please use code tags for data and code samples
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

error connecting to sqlplus

Hi, I wrote a shell script to call oracle procedure. But when i am trying to connet sqlplus with the fallowing statement It is giving me error " callproce.sh : sqlplus: not found". What could be the problem. sqlplus -s $CONNECT_STRING >$LOGFILE <<!! thank u all papachi (2 Replies)
Discussion started by: papachi
2 Replies

2. Windows & DOS: Issues & Discussions

Want to use the output of Select statement in Unix script

Hi, I have a UNIX script which calls SQL Select statement: Now i want to use the output of that select statement within my UNIX script so as to call different shell script depending upon the output of the select statement. Can anyone help me in this regard. TIA Akhil Goel (4 Replies)
Discussion started by: akhilgoel9
4 Replies

3. UNIX for Dummies Questions & Answers

select count(*) in sqlplus into variable unix shell

Need to select count(*) from table to check for zero result in unix script (2 Replies)
Discussion started by: struggle
2 Replies

4. Shell Programming and Scripting

connecting through sqlplus

I am trying to connect to one of the oracle sever using uni through sqlplus command: sqlplus -s BOXI_ALPH_AUDITOR,Q078_audit$@Q047 But its not getting connected. I tried using some different server using same syntax its working. What differene i found is the password is having no special... (2 Replies)
Discussion started by: gander_ss
2 Replies

5. UNIX for Dummies Questions & Answers

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (1 Reply)
Discussion started by: shrutihardas
1 Replies

6. Shell Programming and Scripting

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (26 Replies)
Discussion started by: shrutihardas
26 Replies

7. UNIX for Advanced & Expert Users

Connecting once using sqlplus and doing multiple queries

Hello everyone, It's my first week using unix and shell scripting. I tried creating a script that has a function that execute SQL query. my script looks something like this: ---------------------------------------------------- #!/bin/sh tableName="myTable" secondTable="secondTable"... (2 Replies)
Discussion started by: edlin_r
2 Replies

8. Shell Programming and Scripting

connecting to table to extract multiple rows into file from unix script

I need to extract the data from oracle table and written the below code. But it is not working.There is some problem with the query and output is shown is No rows selected" . If I run the same query from sql developer there is my required output. And if I run the shell script with simple sql... (7 Replies)
Discussion started by: giridhar276
7 Replies

9. Shell Programming and Scripting

SQLPLUS command with more than 1 select statement

Hi all, I'm using below code processId=`sqlplus -s ${sysuser}/${syspwd} <<CHK_PROCESS whenever sqlerror exit sql.sqlcode; set head off feedback off echo off pages 0 SELECT PROCESS_ID FROM LSHADMIN.DATA_DOMAIN WHERE DOMAIN_NAME = '${tabname}' ... (8 Replies)
Discussion started by: Pratiksha Mehra
8 Replies

10. Shell Programming and Scripting

UNIX Sqlplus - Capture the sql statement about to run and execution status

Greetings Experts, I am on AIX using ksh. Created a unix script which generates the CREATE OR REPLACE VIEW ... and GRANT .. statements, which are placed in a single .txt file. Now I need to execute the contents in the file (there are around 300 view creation and grant statements) in Oracle and... (4 Replies)
Discussion started by: chill3chee
4 Replies
VMSTAT(1)						      General Commands Manual							 VMSTAT(1)

NAME
vmstat - report virtual memory statistics SYNOPSIS
vmstat [ -fsi ] [ drives ] [ interval [ count ] ] DESCRIPTION
Vmstat delves into the system and normally reports certain statistics kept about process, virtual memory, disk, trap and cpu activity. If given a -f argument, it instead reports on the number of forks and vforks since system startup and the number of pages of virtual memory involved in each kind of fork. If given a -s argument, it instead prints the contents of the sum structure, giving the total number of several kinds of paging related events which have occurred since boot. If given a -i argument, it instead reports on the number of inter- rupts taken by each device since system startup. If none of these options are given, vmstat will report in the first line a summary of the virtual memory activity since the system has been booted. If interval is specified, then successive lines are summaries over the last interval seconds. ``vmstat 5'' will print what the system is doing every five seconds; this is a good choice of printing interval since this is how often some of the statistics are sampled in the system; others vary every second, running the output for a while will make it apparent which are recomputed every second. If a count is given, the statistics are repeated count times. The format fields are: Procs: information about numbers of processes in various states. r in run queue b blocked for resources (i/o, paging, etc.) w runnable or short sleeper (< 20 secs) but swapped Memory: information about the usage of virtual and real memory. Virtual pages are considered active if they belong to processes which are running or have run in the last 20 seconds. A ``page'' here is 1024 bytes. avm active virtual pages fre size of the free list Page: information about page faults and paging activity. These are averaged each five seconds, and given in units per second. re page reclaims (simulating reference bits) at pages attached (found in free list) pi pages paged in po pages paged out fr pages freed per second de anticipated short term memory shortfall sr pages scanned by clock algorithm, per-second up/hp/rk/ra: Disk operations per second (this field is system dependent). Typically paging will be split across several of the available drives. The number under each of these is the unit number. Faults: trap/interrupt rate averages per second over last 5 seconds. in (non clock) device interrupts per second sy system calls per second cs cpu context switch rate (switches/sec) Cpu: breakdown of percentage usage of CPU time us user time for normal and low priority processes sy system time id cpu idle If more than 4 disk drives are configured in the system, vmstat displays only the first 4 drives, with priority given to Massbus disk drives (i.e. if both Unibus and Massbus drives are present and the total number of drives exceeds 4, then some number of Unibus drives will not be displayed in favor of the Massbus drives). To force vmstat to display specific drives, their names may be supplied on the command line. FILES
/dev/kmem, /vmunix SEE ALSO
systat(1), iostat(1) The sections starting with ``Interpreting system activity'' in Installing and Operating 4.2bsd. 4th Berkeley Distribution March 15, 1986 VMSTAT(1)
All times are GMT -4. The time now is 05:27 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy