ora-01086 : save point was not established or invalid

Question

Ora-01086 : save point was not established or invalid. KRD_UPD_BORCTAHSILATYAP_SP this SP throws errors . When I test this loop below, I get the error: ora-01086

Normally it works without calling an external sp, I test it with an inline error and I rolledback to the save point. What am I missing ?

FOR rec IN (...records.....
               )
   LOOP
      SAVEPOINT odemeIslemiBaslangic;
      BEGIN


         CASE rec.prosedur_ad
            WHEN 'KRD' THEN

               KRD_UPD_BORCTAHSILATYAP_SP(rec.musterino, rec.urundegeri, rec.taksitno, v_MuhasebeReferans, v_IslemReferans, v_Tarih);
               IF v_MuhasebeReferans IS NOT NULL THEN
                  v_SonucKd  := 10;
                  v_Aciklama := 'Başarılı işlem';
               ELSE
                  v_SonucKd  := 9;
                  v_Aciklama := 'Borç bulunamadı';
               END IF;

         END CASE;
         cll_ins_tahsilatislem_sp(p_odemeno        => rec.odemeno,
                                  p_islemtarihi    => v_Tarih,
                                  p_musterino      => rec.musterino,
                                  p_urundeger      => rec.urundegeri,
                                  p_islemref       => v_IslemReferans,
                                  p_muhasebesubekd => rec.sube_kd,
                                  p_muhaseberef    => v_MuhasebeReferans,
                                  p_aciklama       => v_Aciklama,
                                  p_sonuc          => v_SonucKd,
                                  p_kayityapan     => v_KayitYapan,
                                  p_kayittrxkod    => v_KayitTrxKod);
         UPDATE cll_gecikmisbankaalacak u
            SET u.sonuc_kd = v_SonucKd
          WHERE u.odemeno = rec.odemeno
            AND u.kayit_drm = 'A';
      EXCEPTION
         WHEN OTHERS THEN
            ROLLBACK TO SAVEPOINT odemeIslemiBaslangic;
            v_SonucKd  := 1;
            v_Aciklama := 'İşlem Hata: ' || substr(SQLERRM, 1, 400);
            cll_ins_tahsilatislem_sp(p_odemeno        => rec.odemeno,
                                     p_islemtarihi    => v_Tarih,
                                     p_musterino      => rec.musterino,
                                     p_urundeger      => rec.urundegeri,
                                     p_islemref       => v_IslemReferans,
                                     p_muhasebesubekd => rec.sube_kd,
                                     p_muhaseberef    => v_MuhasebeReferans,
                                     p_aciklama       => v_Aciklama,
                                     p_sonuc          => v_SonucKd,
                                     p_kayityapan     => v_KayitYapan,
                                     p_kayittrxkod    => v_KayitTrxKod);
            UPDATE cll_gecikmisbankaalacak u
               SET u.sonuc_kd = v_SonucKd
             WHERE u.odemeno = rec.odemeno
               AND u.kayit_drm = 'A';
      END;

   END LOOP;

Liven

i'm not sure ,but i think that it is better that use your SAVEPOINT out of the loop – Millham

Is KRD_UPD_BORCTAHSILATYAP_SP rolling back on error; and if so, where too? – Catenoid

Yes it rolls back itself. or commits. @AlexPoole – Sanfordsanfourd

It rolls back the whole transaction, not to its own savepoint? Then the savepoint you establish in the calling procedure will have been wiped out. – Catenoid

Answer 1

Your comment suggests that the procedure you are calling, KRD_UPD_BORCTAHSILATYAP_SP, is rolling back the whole transaction, i.e. issuing a simple ROLLBACK.

From the documentation for ROLLBACK:

Using ROLLBACK without the TO SAVEPOINT clause performs the following operations:

• Ends the transaction
• Undoes all changes in the current transaction
• Erases all savepoints in the transaction
• Releases any transaction locks

The savepoint you established in your calling block is therefore being erased, so you can no longer roll back to that.

Catenoid

Answer 2

This generally happens when your inner code has commit a change, then you are trying to rollback to savepoint , which is technically not possible

Rhythmical

Not an anwser, should be a comment. – Famulus