running PLSQL scripts through shell script

Applications Programming
1

I am running the following ealth checks on my server

there are two databases in my server . MODEL1 and MODEL2

i connect with the first database as

 
sqlplus model1/password

Then i exceute a query

 
select x from table

now supose i get the value as 788 and i have to roud this value to 788/100=8

then i change this value in the PLSQL scrit and run that this script as

 
@script
 
commit;

then i connect to the other database as

 
conn model2/password
 
commit;

then i run the same script

 
@script
 
commit;

now i want that this all stuff should be done in a single script

such that i just run that script from the unix shell and all work s done.

any help will be appreciated

2

the program can be written in perl, using the DBI module, for more information refer this URL.

DBI - Database independent interface for Perl - search.cpan.org

3

Hi.

MODEL1 and MODE2 are schemas or databases? There's no PL/SQL in your examples...

Test.sh
==========
sqlplus test/test << !
@test
@test2
!
 
 
test.sql
==========
show user
select round(788/100) "Value" from dual;

test2.sql
==========
conn scott/tiger@DB1
show user
select round(788/100) "Value" from dual;
 
 
 
> ./Test.sh
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 21 14:53:40 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL> USER is "TEST"
     Value
----------
         8
Connected.
USER is "SCOTT"
     Value
----------
         8
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
4

Thanks for the rely guys.I will soon give the comlete PLSQL block . sorry for incomlete information .

5

here is the complete scenario

i run this command


sqlplus model1/ncm_mm1

then this

select count(*) from equipment where floor is null and suite like 'GND%';

Based on the above count i modify the number of
times the for loop is executed in upd.sql


nwncms$ vi  upd.sql

DECLARE

i NUMBER := 0;

BEGIN


FOR i IN 1..12 LOOP----Modify the number of iterations based on step2. if step2 returns 1526 make the number of iterations as 16

update equipment set floor=substr(suite ,1,3), suite=substr(suite ,4), CREATE_DATE=sysdate+1

where floor is null and suite like 'GND%' and rownum < 101;

COMMIT;

END LOOP;


EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE)||'-'||SQLERRM);

END;

Save the upd.sql file after modifying the number of iterations.

then i run the following steps

set serveroutput on
SQL>@/ncm/user/ncm_ops/upd.sql
SQL>update equipment set floor='001',suite='01' where code='BNC' and suite='001F01';
SQL>commit;

SQL> conn model2/ncm_mm2
SQL>set serveroutput on
SQL>@/ncm/user/ncm_ops/upd.sql
SQL>update equipment set floor='001',suite='01' where code='BNC' and suite='001F01';
SQL>commit;

now i want that all these things done through a single script . As this is a live server , I do not try my hand at modifying the scripts and i also do not have any server to practice .

Please help me out in finding the solution.

Please specify if any information is needed from my side.

Many Thanks