Home » SQL & PL/SQL » SQL & PL/SQL » calling a procedure within unix shell script
calling a procedure within unix shell script [message #204356] Mon, 20 November 2006 05:17 Go to next message
wkrcr
Messages: 3
Registered: November 2006
Junior Member
Hi,

does anyone know if exists a timeout for sqlplus connections that execute package procedures?

I´m calling a UNIX shell script that calls two Oracle procedures within a sqlplus script.

The procedures seem to do its job OK (in database), but don´t return the control to the shell script. So, the sqlplus still appears in psv command (still appears as a runnig process in UNIX machine) and never ends.

Possibly, sqlplus lost its connection (due to timeout?) but the procedure kept executing in database server. When procedure is done, sqlplus does not know it because connection is lost.

#!/bin/ksh

export ORACLE_HOME="/internet/oracle"
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export PATH=${ORACLE_HOME}/bin:${PATH}

USR_BD=...
PWD_BD=...
BD=...

${ORACLE_HOME}/bin/sqlplus ${USR_BD}/${PWD_BD}@${BD} @myscript.sql >mylog.log

where

myscript.sql
============

set serveroutput on size 999999
set verify off
set echo on
whenever sqlerror exit failure;
begin
pck.MYPROCEDURE1;
pck.MYPROCEDURE2;
end;
/
exit

Thanks in advance.
Re: calling a procedure within unix shell script [message #204692 is a reply to message #204356] Tue, 21 November 2006 13:17 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It sounds like some issue in your code or environment. Look for trace files in udump or bdump. Run a query to get the session and process details.

http://asktom.oracle.com/pls/ask/f?p=4950:8:165844::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:497421739750,
Re: calling a procedure within unix shell script [message #204871 is a reply to message #204356] Wed, 22 November 2006 07:50 Go to previous messageGo to next message
wkrcr
Messages: 3
Registered: November 2006
Junior Member
thanks very much, i'll run the query to get more information.

I've continued doing tests, and don't get the "disconnection" when there is no firewall between database server and the machine executing sqlplus. Could it be a firewall configuration issue?

The problem is not executing the package procedure (is executing OK), but telling the sqlplus (and the UNIX script who launched it), that job is done and may continue doing things.

Thnaks again.
Re: calling a procedure within unix shell script [message #204896 is a reply to message #204871] Wed, 22 November 2006 09:34 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
yes, firewalls can definitely kill connections which have no activity after a certain amount of time.
Re: calling a procedure within unix shell script [message #204899 is a reply to message #204896] Wed, 22 November 2006 09:42 Go to previous message
wkrcr
Messages: 3
Registered: November 2006
Junior Member
is then possible that a sql connection is killed by the firewall, but the package procedure it launched, continues executing in database until its normal termination?

thanks again.
Previous Topic: In trouble with the date format
Next Topic: get numbers out of a char
Goto Forum:
  


Current Time: Thu Dec 08 02:18:32 CST 2016

Total time taken to generate the page: 0.17190 seconds