2

I'm trying to call a procedure from a shell script, that procedure makes a massive insert into my database with data of a remote database. So, what i'm doing here is querying tables through a dblink and inserting that information in my local tables.

When i do that directly from a DB IDE, like Quest Toad or Oracle SQL Developer, the procedure runs in less than 3 mins. When i run the procedure in sqlplus command line runs in less than 3 mins too. But when i call the procedure using a shell script, the process in unix hangs out, producing an high usage network event in oracle server and db session never ends.

So, the thing here is that i have another procedures running in the same way than this, and only this is causing issues while i execute in a ksh script.

Below, two approachs i'm using to call the procedure from sqlplus in the ksh script:

Original script:

#!/usr/bin/ksh

PROFILE=/home/user/config/my_profile.sh
. ${PROFILE}
yesterday=$(TZ=GMT+24 date "+%d/%m/%Y")
echo "Establishing a DB connection"
/home/oraclei/product/11.1.0/bin/sqlplus<<END_OF_SQL
$USER/$PASSWD
execute MY_PROCEDURE@DB('$yesterday');
exit;
END_OF_SQL

Alternative: 

#!/usr/bin/ksh

PROFILE=/home/user/config/my_profile.sh
. ${PROFILE}
echo "Establishing a DB connection"
yesterday=$(TZ=GMT+24 date "+%d/%m/%Y")
/home/oraclei/product/11.1.0/bin/sqlplus $USER/$PASSWD @/home/user/dblink_load/scripts/sql/load.sql $yesterday

and the sql script it uses 

exec MYSCHEMA.MY_PROCEDURE('&1');
quit;

The queries in the procedure are something like this: 

insert into MYSCHEMA.mytable
(id, date, stat1, stat2, stat3)
(select
    id, date, max(stat1), avg(stat2), avg(stat3)
from
    tableA@dblink table1,
    tableB@dblink table2,
    tableC@dblink table3
where
    table1.date >= parameter and
    table1.id = table2.id and
    table2.id = table3.id
group by
    table1.id,
    table1.date);
joeygs
  • 83
  • 5
  • guessing: your script does not have the same environment and is failing to connect to the db correctly? – Randy Nov 26 '12 at 21:59
  • Do all environments use the same date format? Your parameter may not be converted the same way each time. You can check your session settings with a query like this: `select sys_context('userenv', 'nls_date_format') from dual;` – Jon Heller Nov 27 '12 at 05:59
  • @Randy no, i'm using a copy of this script to call another procedure in the same database that query through same db link and it works fine – joeygs Nov 27 '12 at 15:31
  • @jonearles Both local and remote database returns "DD/MM/RR" as result – joeygs Nov 27 '12 at 15:37
  • @joeygs What about when you run it in Toad, SQL Developer, or SQL*Plus? In Oracle, the date format is set by the client, not the server. – Jon Heller Nov 27 '12 at 19:20
  • @jonearles SQL*PLUS: "DD/MM/RR", TOAD and SQL Developer "DD-MM-RRRR HH24:MI:SS" – joeygs Nov 27 '12 at 20:56

0 Answers0