script help

yusufs · 12-11-2007, 07:06 AM

Hai all,

am using a script like this to get the no of sessions in the oracle..

like

sqlplus username/password
select count(*) from V$session where status='ACTIVE';

am having the above command in a script ..

I want to store this count(*) in a variable and print it such that when the script is executed, it should print

25 sessions are active now at date and time

Please guide

Yusuf

cmnorton · 12-12-2007, 05:23 AM

The scripting or programming language you are using needs to support embedded SQL. I'm using Informix 4gl, and while I can write .sql file directives to load, delete, drop, insert, or whatever to tables in my database, I have to write a program either in C, ESQL/C, 4GL, or another language that supports embedded SQL. In that way, I can store variables.

I know of now way a SQL script (.sql) on its own can save a value.

Disillusionist · 12-12-2007, 02:06 PM

create a sql script called test.sql:

Code:

set heading off;
select count(*) from V$session where status='ACTIVE';
exit;

Create a shell script that calls this and processes the output:

Code:

#!/bin/bash

echo "$(sqlplus -S '/ as sysdba' @test.sql|grep -v '^$'|sed 's/^\t\t*//g') sessions are active now at $(date)"

You may want to structure the date format, and I am going on the assumption that you are running the script as the oracle account and the default '/ as sysdba' works (as it would normally given the script running as oracle)

yusufs · 12-12-2007, 02:10 PM

Quote:

Originally Posted by Disillusionist

create a sql script called test.sql:

Code:

set heading off;
select count(*) from V$session where status='ACTIVE';
exit;

Create a shell script that calls this and processes the output:

Code:

#!/bin/bash

echo "$(sqlplus -S '/ as sysdba' @test.sql|grep -v '^$'|sed 's/^\t\t*//g') sessions are active now at $(date)"

You may want to structure the date format, and I am going on the assumption that you are running the script as the oracle account and the default '/ as sysdba' works (as it would normally given the script running as oracle)

Thanks Disillunioist and Norton.. will check and let you know about this.

Thanks
Yusuf