Home » Infrastructure » Linux » sqlplus process dont ends (SunOS 5.10 Oracle client 11.1.0)
sqlplus process dont ends [message #625459] Wed, 08 October 2014 06:32 Go to next message
haythem
Messages: 2
Registered: October 2014
Junior Member
Hi,
platform:SunOS 5.10
Oracle client 11.1.0
we launch shell scripts that calls sqlplus to connect to a distant database server and launch sql traitment.
the problem is that if we monitor the session via toad we see it working and doing everything (inset, update and finally commit and exit). we see the session ends (via toad) but the sqlplus in the main server still wait for response. this is the way we do it :
1/ In the shell script :
Execute_sql  $CONNECT $SQLFILE $SQLLOG $COMITINTV >> ${SCRIPTLOG}
if [ $? -ne 0 ]
then update KO
fi
update OK

function Execute_sql
function Execute_sql
{
 CONNECT=$1
 SQLFILE=$2
 LOGFILE=$3
 FREQCOMMIT=$4

`sqlplus -s /NOLOG > /dev/null <<END
 connect ${CONNECT}
 set serveroutput on
 set verify on
 set feedback on
 spool ${LOGFILE}
 @${SQLFILE} ${FREQCOMMIT}  
 spool off
 exit;
END`
}


the sqlfile executed :
set serveroutput on
DECLARE

V1....
V2....
BEGIN

  DBMS_OUTPUT.ENABLE( 1000000 );

    USER.PKG.P_PROCEDURE(v1,v2,err_code,err_msg);

   IF err_code !=  CSTE_OK THEN
        dbms_output.put_line('---> ERROR    : '||err_msg);
    END IF;


EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line('---> ERR CODE   : '||to_char(SQLCODE));
 dbms_output.put_line('---> ERR MSG : '||SUBSTR(SQLERRM, 1, 250));
END;
/
EXIT; 

Nothing is spooled in ${LOGFILE}

we have this probleme when sql traitement spend more one hour. In case of speed traitement dont exceed one hour it works well.

thks a lot for your help
Re: sqlplus process dont ends [message #625465 is a reply to message #625459] Wed, 08 October 2014 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what is in ${SCRIPTLOG}?

But FIRST of all remove the stupid WHEN OTHERS part (and read the link).

Re: sqlplus process dont ends [message #625661 is a reply to message #625465] Sun, 12 October 2014 15:34 Go to previous messageGo to next message
haythem
Messages: 2
Registered: October 2014
Junior Member
hi,
thx for your answer , the ${SCRIPTLOG} is the log of the shell script that calls ths sql.every scritp (shell or sql) generates its log.
Re: sqlplus process dont ends [message #625667 is a reply to message #625661] Mon, 13 October 2014 00:09 Go to previous message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is its content?
You said "Nothing is spooled in ${LOGFILE}" so "and in ${SCRIPTLOG}?"

Previous Topic: /sbin/start_udev
Next Topic: Oracle linux
Goto Forum:
  


Current Time: Sun Sep 19 13:17:05 CDT 2021