/var/tmp/sh12145.1: Cannot find or open file.

[Olga Manna]

Hi,
I have a shell script which worked fine in HP-UX 11 and we upgraded to 11i. Now I get the above error. In my shell I call sqlplus (Oracle) to execute some database updates. I'm not sure, but I believe the shell creates temp files to run the sqls. It seems like these files somehow get lost because one day the script works fine, and the next I get the error, and, all but one sql work. Anyone seen this before ??
Thanks.

[Mark Grant]

Olga,

I think you might have to post the script on here.

Regards

[A. Clay Stephenson]

Best Guess: Sometime after the tmp files are created but before all the SQL scripts have completed a cron job which cleans up temp files fires up and removes the file(s).

[Olga Manna]

We verified for jobs removing the files. they are run much later than this job.

Here is the part of the script which fails:

bidon=`sqlplus -s $usager <= NVL( ec.Date_Transfert_Courant + 1, ec.Date_Debut_Trx )
AND cc.date_debut = NVL( ec.Date_Transfert_Courant + 1, ec.Date_Debut_Trx )
AND cc.date_fin <= tc.date_prochain_transfert ) ) )
WHERE ( c2.no_client, c2.no_trx ) IN
( SELECT
ec.no_client,
ec.no_trx
FROM
calendrier_comptable cc,
controle_transfert_comptable tc,
edi_client ec
WHERE ec.No_Trx IN ('810','880','882')
AND ec.Date_Debut_Trx <= NVL( ec.Date_Dernier_transfert, TO_CHAR( SYSDATE, 'J' ) )
AND NVL(ec.Date_Transfert_Courant + 1, Ec.Date_Debut_Trx) BETWEEN Cc.Date_Debut AND Cc.Date_Fin
AND ( ec.Frequence_Transmission = '0'
OR ( ec.Frequence_Transmission = '1'
AND tc.date_prochain_transfert > NVL( ec.Date_Transfert_Courant + 1, ec.Date_Debut_Trx ) )
OR ( ec.Frequence_Transmission = '2'
AND cc.date_fin >= NVL( ec.Date_Transfert_Courant + 1, ec.Date_Debut_Trx )
AND cc.date_debut = NVL( ec.Date_Transfert_Courant + 1, ec.Date_Debut_Trx )
AND cc.date_fin <= tc.date_prochain_transfert ) ) );
!`

Thanks.

[Mark Grant]

Olga,

This doesn't make things clearer to me, at least. However, there are few patches for oracle thet we found we had to install to get it working sanely. I assume you have all the oracle recommended patches installed. Oh and which version of oracle are you running?

[Olga Manna]

Our version of Oracle is 8.1.7.4.0.
I will check with my sysadmin and DBA to find out if all patches were installed.
Thanks.