Home » SQL & PL/SQL » Client Tools » how to add value from sqlplus
how to add value from sqlplus [message #584411] Tue, 14 May 2013 02:51 Go to next message
mmi78
Messages: 30
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 Go to previous messageGo to next message
Littlefoot
Messages: 19889
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 #584415 is a reply to message #584412] Tue, 14 May 2013 03:39 Go to previous messageGo to next message
mmi78
Messages: 30
Registered: April 2013
Location: dhaka
Member
can not possible with sql variable? do not want to use dblink.
Re: how to add value from sqlplus [message #584441 is a reply to message #584415] Tue, 14 May 2013 06:53 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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.
icon10.gif  Re: how to add value from sqlplus [message #584579 is a reply to message #584415] Wed, 15 May 2013 14:55 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 449
Registered: July 2003
Location: WPB, FL
Senior Member
mmi78 wrote on Tue, 14 May 2013 04:39
can 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 #584580 is a reply to message #584579] Wed, 15 May 2013 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
No dblink ncecessary.

So what is: "user_objects@' || tab_list (i)", if it is not a dblink?

Regards
Michel

[Updated on: Wed, 15 May 2013 15:10]

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 Go to previous message
LKBrwn_DBA
Messages: 449
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"

Shocked

[Updated on: Wed, 15 May 2013 16:05]

Report message to a moderator

Previous Topic: Generating tkprof from SqlDeveloper tool.
Next Topic: Where does DBMS_OUTPUT.PUT_LINE() output go (in Toad) ?
Goto Forum:
  


Current Time: Sat Dec 20 00:07:41 CST 2014

Total time taken to generate the page: 0.09053 seconds