Can't find why NO_DATA_FOUND is being thrown
<p>I am trying to delete some registrations in a table using an external table (<strong>tClean</strong>), a cursor initialized with this tClean table, and a loop. The SELECT is supposed to find the id (<strong>LabelId</strong>) of the line corresponding to the cursor values in a view (<strong>SetLabelListVW</strong>) and put it into my variable <strong>labelToDelete</strong>. The DELETE after is supposed to delete the line with the id corresponding to labelToDelete in the table <strong>SetLabel</strong>.</p>

<p>The problem is that it raises a NO_DATA_FOUND exception for EVERY line fetched by the cursor. By the way, some lines that I'm supposed to delete do appear in my database, and others have been already deleted by simple SQL instruction.</p>

<p>Variable names, column names have been verified. I've tried a lot of differents things to find a solution and now I suspect lines :</p>

<pre><code>WHERE "LabelCode" = lcode
AND "TypeLabelCode" = ltype
AND "LabelText" = ltext;
</code></pre>

<p>Maybe there is an error in this part of the code but I don't know what to do now.</p>

<p>This is the code :</p>

<pre><code>SET SERVEROUTPUT ON

DECLARE

labelToDelete       NUMBER(9);
labelUsed           EXCEPTION;
PRAGMA EXCEPTION_INIT(labelUsed, -2292);
CURSOR  c IS SELECT * FROM tClean;
lcode   VARCHAR2(8);
ltype   VARCHAR2(8);
ltext   VARCHAR2(50);

BEGIN

OPEN c;
LOOP
    FETCH c INTO lcode, ltype, ltext;
    EXIT WHEN c%NOTFOUND;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('lcode : ' || lcode);
        DBMS_OUTPUT.PUT_LINE('ltype : ' || ltype);
        DBMS_OUTPUT.PUT_LINE('ltext : ' || ltext);
        SELECT DISTINCT "LabelId" INTO labelToDelete
        FROM "SetLabelListVW"
        WHERE "LabelCode" = lcode
        AND "TypeLabelCode" = ltype
        AND "LabelText" = ltext;

        DELETE FROM "SetLabel"
        WHERE "SetLabelId" = labelToDelete;

        DBMS_OUTPUT.PUT_LINE('Suppression du label Id : ' || labelToDelete || '  Label : ' || ltext);
        EXCEPTION

        WHEN labelUsed THEN
            DBMS_OUTPUT.PUT_LINE('Exception ORA-2292 : le label ne peut être supprimé.');
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Exception ORA-01403 (NO_DATA_FOUND) : le label n''existe pas.' || ltext);
    END;
END LOOP;
CLOSE c;
--COMMIT;

 END;
</code></pre>

<p>And this is the result :</p>

<pre><code>lcode : 00000195
ltype : MODSET
ltext : prout
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.prout
lcode : 00000189
ltype : MODSET
ltext : pouet
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.pouet
lcode : 00000169
ltype : MODSET
ltext : POUET POUET
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.POUET POUET
lcode : 00000181
ltype : MODSET
ltext : pouet
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.pouet
lcode : 00000182
ltype : MODSET
ltext : pouet
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.pouet
lcode : 00000191
ltype : MODSET
ltext : pouet
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.pouet
lcode : 00000183
ltype : MODSET
ltext : pouet 123
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.pouet 123
lcode : CHARTEST
ltype : ACTPARAM
ltext : Caract�stique �ester
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.Caract�stique     �ester
lcode : 00000022
ltype : MODSET
ltext : TEST bf
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.TEST bf
lcode : 00000323
ltype : MODSET
ltext : test fp
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.test fp
lcode : 00000008
ltype : MODSET
ltext : testghdgklh
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.testghdgklh
lcode : CARAC
ltype : STEPCO
ltext : Caract�stique test
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.Caract�stique test
lcode : TEST
ltype : CARACT
ltext : Dossier de test
Exception ORA-01403 (NO_DATA_FOUND) : le label n'existe pas.Dossier de test
</code></pre>

<p>Why am I getting that exception when the data does exist?</p>

<p>Edit : description of tClean, SetLabelListW, and creation request of tClean.</p>

<pre><code>desc tClean;
Nom                                       NULL ?   Type
----------------------------------------- -------- ----------------------------
LABELCODE                                          VARCHAR2(8)
TYPELABELCODE                                      VARCHAR2(8)
LABELTEXT                                          VARCHAR2(50)


desc "SetLabelListVW";
Nom                                       NULL ?   Type
----------------------------------------- -------- ----------------------------
SetLabelLanguageId                        NOT NULL NUMBER(9)
LabelId                                            NUMBER(9)
TypeLabelId                                        NUMBER(9)
TypeLabelCode                                      VARCHAR2(8 CHAR)
SetLabelTypeId                            NOT NULL NUMBER(9)
CategoryLabelId                                    NUMBER(9)
OrdipatLabelCode                                   VARCHAR2(8 CHAR)
LabelCode                                          VARCHAR2(8 CHAR)
LabelText                                          VARCHAR2(50 CHAR)
CommentText                                        VARCHAR2(300 CHAR)
LongLabelText                                      VARCHAR2(100 CHAR)
SetLanguageId                             NOT NULL NUMBER(9)
SortNumber                                         NUMBER(5)
LanguageLabelCode                                  VARCHAR2(8 CHAR)
OrigineLanguageId                         NOT NULL NUMBER(9)
LastUpdateDate                                     DATE


CREATE TABLE tClean (
LabelCode VARCHAR2(8), 
 TypeLabelCode VARCHAR2(8), 
 LabelText VARCHAR2(50)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY SYNCFILES
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1
    CHARACTERSET UTF8
    DNFS_ENABLE
    DNFS_READBUFFERS 10
    FIELDS TERMINATED BY ';'
    MISSING FIELD VALUES ARE NULL
    (
      LabelCode,
      TypeLabelCode,
      LabelText     
    )
  )
  LOCATION ('cleanLabel.csv')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
</code></pre>

<p>I suspect an encoding problem.
My csv is UTF-8, my external table is UTF-8 and Oracle bdd is ALT32UTF8.
Or maybe, some access parameters in the external table request make a problem in the pl/sql code.
I don't know. I'm a little bit lost.</p>

<p>New edit :</p>

<p>It seems that following simple request works on SQL Developer but doesn't on SQL Plus.</p>

<pre><code>SELECT DISTINCT "LabelId"
        FROM "SetLabelListVW"
        WHERE trim("LabelCode") = 'CHARTEST'
        AND trim("TypeLabelCode") = 'ACTPARAM'
        AND trim("LabelText") = 'Caractéristique à tester';
</code></pre>

<p>SQL Developer : LabelId = 100172209
SQL Plus : aucune ligne sÚlectionnÚe (encodage problem)</p>

<p>The whole code on the top of the question does'nt work neither on SQL Dev neither on SQL Plus.</p>