how to add value from sqlplus [message #584411] |
Tue, 14 May 2013 02:51 |
|
mmi78
Messages: 31 Registered: April 2013 Location: dhaka
|
Member |
|
|
I want to add a value from sqlplus.
My scenario is like that
i want to add the following value
select count(*) x from tab1
i need to connect several database, and each time i need to add with previous one. like that
connect a/a@db1;
select count(*) x from tab1;
then when i connect another db
connect b/b@db2;
select count(*) x from tab1;
x will add with previous one.
|
|
|
Re: how to add value from sqlplus [message #584412 is a reply to message #584411] |
Tue, 14 May 2013 02:57 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
In one of these schemas create database links to other schemas (databases). Then you'd
with test as
(select count(*) x from tab1@dbl1 union all
select count(*) x from tab1@dbl2 union all
...
select count(*) x from tab1@dbln
)
select sum(x)
from test;
[EDITED by LF: typo]
[Updated on: Tue, 14 May 2013 04:09] Report message to a moderator
|
|
|
|
Re: how to add value from sqlplus [message #584441 is a reply to message #584415] |
Tue, 14 May 2013 06:53 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
sqlplus isn't designed to things like that, it's simple interface to the DB, not a scripting language.
You could write a shell script that calls sqlplus for each DB and gets the value back and stores and adds it in a variable in the shell script.
|
|
|
Re: how to add value from sqlplus [message #584579 is a reply to message #584415] |
Wed, 15 May 2013 14:55 |
|
LKBrwn_DBA
Messages: 487 Registered: July 2003 Location: WPB, FL
|
Senior Member |
|
|
mmi78 wrote on Tue, 14 May 2013 04:39can not possible with sql variable? do not want to use dblink.
No dblink ncecessary.
You could try something like this:
VAR sum_total NUMBER;
DECLARE
TYPE lst_typ IS VARRAY(10) OF VARCHAR2 (32);
tab_list lst_typ := lst_typ('DB1', 'DB2', 'DB3');
sql_qry VARCHAR2 (4000);
obj_cnt NUMBER;
BEGIN
:sum_total := 0;
FOR i IN 1 .. tab_list.COUNT
LOOP
sql_qry := 'SELECT COUNT (*) FROM user_objects@' || tab_list (i);
EXECUTE IMMEDIATE sql_qry INTO obj_cnt;
DBMS_OUTPUT.put_line ('DB= ' || tab_list (i) || ', cnt= ' || obj_cnt);
:sum_total := :sum_total + obj_cnt;
END LOOP;
DBMS_OUTPUT.put_line ('Sum_Total= ' || :sum_total);
END;
/
PRINT sum_total
[Updated on: Wed, 15 May 2013 15:08] by Moderator Report message to a moderator
|
|
|
|
Re: how to add value from sqlplus [message #584581 is a reply to message #584580] |
Wed, 15 May 2013 15:17 |
|
LKBrwn_DBA
Messages: 487 Registered: July 2003 Location: WPB, FL
|
Senior Member |
|
|
Ooops...true.
Well without db-link, then he must code a *nix script (if that is the OS) somewhat like this:
#!/bin/ksh
ORAENV_ASK=NO
. /usr/local/oraenv orcl
cat - <<! >sql_script.sql
set echo off ver off term on feed off pages 0 head off
VAR sum_total NUMBER;
VAR obj_cnt NUMBER;
EXEC :sum_total := 0;
!
PWD='myUser/pwd'
for db in DB1 DB2 DB3
do
{
echo "conn $PWD@$db"
echo "BEGIN SELECT COUNT(*) INTO :obj_cnt FROM USER_OBJECTS;\n :sum_total := :sum_total + :obj_cnt;\nEND;\n/"
} >>sql_script.sql
done
#echo "set term on"
echo "print :sum_total;\nexit" >>sql_script.sql
TOTAL=`sqlplus -s / @sql_script`
echo "Total is: $TOTAL"
[Updated on: Wed, 15 May 2013 16:05] Report message to a moderator
|
|
|