[sqr-users] Réf. : [sqr-users] AW: [sqr-users] Réf. : [sqr -users ] Does OR-54 Raise an error in aBegin-SQL Block ??

["Thaî Le Tat" <thai.le-tat@creditlyonnais.fr>]

Some questions :

- Is the error message "Error on line 266" related to CASE 2 or CASE 1 ?
- "Data is locked by an SELECT FOR UPDATE xy NOWAIT by a other Terminal" :
is the other Terminal a programm or SQLPLUS ? If it's with SQLPLUS, why do
you use NOWAIT mode ?
- Is "buc_bucid" an unique ident in table "abuc_buchung" (PK or index) ?
- Are you managing Transaction manually in your SQR ? How many times is
called the "CASE 2" ?


If I understand, you want to perform a UPDATE in NOWAIT mode.
Maybe can you :
      - add in your code a "SELECT FOR UPDATE NOWAIT" before the "UPDATE",
      - check this SELECT with a "Begin-SQL ON-ERROR=ORA_54_Fehler",


Regards,
Thai





"Steinkellner, Michael" <michael.steinkellner@transflow.com>@sqrug.org le
18/11/2002 09:38:37

Veuillez répondre à sqr-users@sqrug.org

Envoyé par :      sqr-users-admin@sqrug.org


Pour :
cc :    (ccc : Thaî Le Tat/DED/DSTI/CLY)

Objet :     [sqr-users] AW: [sqr-users] Réf. : [sqr-users] Does OR-54 Raise
       an error in aBegin-SQL Block ??


Hello Thaî,

this is what i want:

Prevent the SQR to stop with an error, if ORA-54 occurs.
SQR stops with following error ( sometimes ;-) ):

<SNIP>
<+++++ START ExportDFUE +++++>
MIST
MIST
MIST
WEKLchuessler Heppenheim
WEKLBurger King
(SQR 5528) ORACLE OEXEC error -54 in cursor 96:
   ORA-00054: resource busy and acquire with NOWAIT specified

Error on line 266:
   (SQR 3723) Problem executing cursor.

SQR: Program Aborting.
<SNIP>

In this case the update should not executed, but the other code in my sqr
... ;_)
After the BEGIN-SQL - Block ....

Sorry for my bad english ...

TIA, Michael




-----Ursprüngliche Nachricht-----
Von: Thaî Le Tat [mailto:thai.le-tat@creditlyonnais.fr]
Gesendet: Freitag, 15. November 2002 15:12
An: sqr-users@sqrug.org
Betreff: [sqr-users] Réf. : [sqr-users] Does OR-54 Raise an error in
aBegin-SQL Block ??


The record is looked.
So SQR will wait for the unlock and will not call the "ORA_54_Fehler"
procedure ...
This is the normal behaviour ...

What do you want to do exactly ? To test your "ORA_54_Fehler" procedure or
to catch timeout ?

Regards,
Thai


"Steinkellner, Michael" <michael.steinkellner@transflow.com>@sqrug.org le
15/11/2002 14:53:55

Veuillez répondre à sqr-users@sqrug.org

Envoyé par :      sqr-users-admin@sqrug.org


Pour :
cc :   "Klocker, Werner" <werner.klocker@transflow.com> (ccc : Thaî Le
       Tat/DED/DSTI/CLY)

Objet :     [sqr-users] Does OR-54 Raise an error in a Begin-SQL Block ??


Hello SQR-Experts,
I've a problem with SQR:

CASE 1:
! **********************************************************************

Begin-SQL   ON-ERROR=ORA_54_Fehler
begin
RAISE_APPLICATION_ERROR(-20999,'EIn Kleiner Testfehler1',TRUE);;
end;;
End-SQL

Oracle raises an error and SQR jumps in the  ON-ERROR - Procedure
OK!
! **********************************************************************

CASE 2:           ( Data is locked by an SELECT FOR UPDATE xy NOWAIT by a
other Terminal)
! **********************************************************************
Begin-SQL   ON-ERROR=ORA_54_Fehler
UPDATE abuc_buchung
SET   buc_dfueaus = $Sequence
WHERE buc_bucid = &AVIAUF_BUC_BUCID
End-SQL

Oracle raises an error (ORA-54) ?? and SQR jumps NOT in the  ON-ERROR -
Procedure??
It seems ORACLE is WAITING..., and WAITING......, and W...., and .......
Not ok ??????????

! **********************************************************************


! **********************************************************************
Begin-Procedure ORA_54_Fehler
! **********************************************************************
      SHOW '***************IN DER PROCEDURE
ORA_54_Fehler******************'
      SHOW '***************(misslungener Versuch
buc_dfueaus***************'
      SHOW '***************upzudaten
***************'
      SHOW '***************$pLAFID:'$pLAFID
      SHOW '**buc_dfueaus**$Sequence:'$Sequence
      SHOW '***************&AVIAUF_LAF_LAFID:'&AVIAUF_LAF_LAFID
      SHOW '***************&AVIAUF_BUC_BUCID:'&AVIAUF_BUC_BUCID
      SHOW '***************$pSATZART:'$pSATZART

      LET $TMP_ORA_54_Fehler = ''
      LET $TMP_ORA_54_Fehler = $TMP_ORA_54_Fehler ||  ' Fehler beim Sperren
( SQLERROR -> ' ||
                                    $sql-error || ' SQLSTATUS -> ' ||
TO_CHAR( #sql-status ) ||
                                    ' SQLCOUNT -> ' || TO_CHAR( #sql-count
)
      DO Print_Err($TMP_ORA_54_Fehler)
End-Procedure

Have somebody an explanation for this behaviour ??

TIA, Michael


Mit freundlichen Grüßen

Michael Steinkellner
Logistics Management
Software-Engineering

TRANSFLOW
Informationslogistik GmbH
Am Kehlerpark 1

A-6850 Dornbirn

T +43 (0) 5572 . 56 500 - 55
F +43 (0) 5572 . 56 500 - 9
mailto:msteinkellner@transflow.com



Ce message contient des informations confidentielles ou appartenant au
Crédit Lyonnais et est établi à l'intention exclusive de ses
destinataires. Toute divulgation, utilisation, diffusion ou reproduction
(totale ou partielle) de ce message, ou des informations qu'il contient,
doit être préalablement autorisée. Tout message électronique est
susceptible d'altération et son intégrité ne peut être assurée.
Le Crédit Lyonnais décline toute responsabilité au titre de ce
message s'il a été modifié ou falsifié. Si vous n'êtes pas
destinataire de ce message, merci de le détruire immédiatement et
d'avertir l'expéditeur de l'erreur de distribution et de la destruction
du message.
Crédit Lyonnais, SA au capital de Euros 1.807.874.827 - RCS Lyon B 954 509 741
Siège Central : 19, boulevard des Italiens. 75002 Paris. France

This e-mail contains confidential information or information belonging
to Crédit Lyonnais and is intended solely for the addressees.
The unauthorised disclosure, use, dissemination or copying (either whole
or partial) of this e-mail, or any information it contains, is prohibited.
E-mails are susceptible to alteration and their integrity cannot be guaranteed.
Crédit Lyonnais shall not be liable for this e-mail if modified or falsified.
If you are not the intended recipient of this e-mail, please delete it
immediately from your system and notify the sender of the wrong delivery
and the mail deletion.
Crédit Lyonnais SA.
Share Capital of Euros 1.807.874.827.
Registered Office : Lyon (B 954 509 741)
Central and administrative Office : 19, boulevard des Italiens. 75002 Paris. 
France