6 Replies Latest reply: Dec 6, 2012 8:25 AM by 978365 RSS

    getting ORA-01403:, when it shouldn't

    978365
      Greetings, i apologize beforehand for my spelling, name(takes 6 hours to change) and the headache you migth get, however,
      i bring you the following code, and test results:
      -----------------------------------------------------------
      ------Procedure wich throws the error-------
      -----------------------------------------------------------
      create or replace
      procedure P_COLEGAS(x in number) as
      ctipo varchar2(20);
      asd varchar2(20);
      
      cursor curnombre is
      select nombre from unidad,elemento where (elemento.id_elem=unidad.id_elem and unidad.tipo=ctipo and elemento.ciudad=asd);
      
      begin
      select unidad.tipo, elemento.ciudad into ctipo,asd from unidad,elemento where unidad.id_elem=x and elemento.id_elem=x;
      for blah in curnombre loop
      DBMS_OUTPUT.PUT_LINE('nombre unidad: '||blah.nombre||' ');
      end loop;
      end;
      -what i get when executing the procedure-
      Error que empieza en la línea 1 del comando:
      exec p_colegas(19)
      Informe de error:
      ORA-01403: no data found
      ORA-06512: at "BD00.P_COLEGAS", line 9
      ORA-06512: at line 1
      01403. 00000 - "no data found"
      *Cause:
      *Action:
      -----------------------------------------------------------
      -----------------the real problem--------------------
      -----------------------------------------------------------
      if in that procedure i were to write
      (1)
      select unidad.tipo into ctipo from unidad where unidad.id_elem=x;
      (2)
      select elemento.ciudad into asd from elemento where elemento.id_elem=x;
      instead the single query i wrote, we get the following:
      (1) works wonderfull, only gets the error when there are no matches for x.
      (2) throws the error i showed before.

      however when i do the following query in the worksheet and execute it:
      (3)
      select elemento.ciudad from elemento where elemento.id_elem=x;
      i get what i expected to get 1 row 1 column.(yes it has data)
      note: in (3) the only difference is that i remove the into clause, and x is the same number i used when i execute the procedure.

      -----------------------------------------------------------
      --------------------the question------------------------
      -----------------------------------------------------------
      why in the procedure, the query (2) fail to fetch the data, the same data wich the query(3) does not fail to fetch?
      i'm getting ORA-01403, when i shouldn't?
      is there a work around to this problem?

      -----------------------------------------------------------
      --------------------what i try------------------------------
      -----------------------------------------------------------
      nested the query with it's own error handle exception, getting the same results, just catches the error with a different handling.

      tool used: sql developer

      ----------
      -Example data--
      ----------
      tested the procedure with the following example data in a brand new workspace getting the same error.
      --------------------------------------------------------
      --  DDL for Table ELEMENTO
      --------------------------------------------------------
      
        CREATE TABLE "ELEMENTO" 
         (     "ID_ELEM" NUMBER, 
           "CIUDAD" VARCHAR2(20), 
           "TIPO" CHAR(1), 
           "X" NUMBER, 
           "Y" NUMBER, 
           "FECHAHORA_CREACION" TIMESTAMP (6)
         ) ;
      /
      --------------------------------------------------------
      --  DDL for Table UNIDAD
      --------------------------------------------------------
      
        CREATE TABLE "UNIDAD" 
         (     "ID_ELEM" NUMBER, 
           "PORCENTAJE_SALUD" NUMBER, 
           "NOMBRE" VARCHAR2(20), 
           "TIPO" VARCHAR2(20)
         ) ;
      /
      REM INSERTING into ELEMENTO
      SET DEFINE OFF;
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (12,'Infernalia','U',10,10,to_timestamp('12-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (15,'Infernalia','U',10,7,to_timestamp('12-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (19,'Infernalia','U',15,9,to_timestamp('12-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (23,'Infernalia','U',16,8,to_timestamp('12-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (27,'Infernalia','C',15,10,to_timestamp('12-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (52,'Humania','U',26,10,to_timestamp('22-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (58,'Humania','U',24,9,to_timestamp('22-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (62,'Humania','U',27,11,to_timestamp('22-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (64,'Humania','C',25,8,to_timestamp('22-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (78,'GruntVille','U',47,32,to_timestamp('29-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (84,'GruntVille','U',42,28,to_timestamp('29-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (89,'GruntVille','U',43,29,to_timestamp('29-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (91,'GruntVille','C',44,37,to_timestamp('29-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (29,'Infernalia','C',16,7,to_timestamp('12-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into ELEMENTO (ID_ELEM,CIUDAD,TIPO,X,Y,FECHAHORA_CREACION) values (90,'GruntVille','U',49,36,to_timestamp('29-NOV-20 12.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      REM INSERTING into UNIDAD
      SET DEFINE OFF;
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (12,100,'Grang','Soldado');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (15,100,'Krout','Médico');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (19,100,'Warf','Obrero');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (23,100,'Puaj','Obrero');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (52,100,'Marcelus','Soldado');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (58,100,'Claudius','Soldado');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (62,100,'Arturius','Obrero');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (78,100,'Klaknot','Médico');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (84,100,'Staisht','Médico');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (89,100,'Bjorkson','Soldado');
      Insert into UNIDAD (ID_ELEM,PORCENTAJE_SALUD,NOMBRE,TIPO) values (90,100,'Sknot','Médico');
      --------------------------------------------------------
      --  Constraints for Table ELEMENTO
      --------------------------------------------------------
      
        ALTER TABLE "ELEMENTO" ADD CONSTRAINT "ELEMENTO_CHK1_TIPO" CHECK (TIPO IN ('U', 'C')) ENABLE;
       
        ALTER TABLE "ELEMENTO" ADD CONSTRAINT "ELEMENTO_PK" PRIMARY KEY ("ID_ELEM") ENABLE;
       
        ALTER TABLE "ELEMENTO" MODIFY ("ID_ELEM" NOT NULL ENABLE);
       
        ALTER TABLE "ELEMENTO" MODIFY ("CIUDAD" NOT NULL ENABLE);
       
        ALTER TABLE "ELEMENTO" MODIFY ("TIPO" NOT NULL ENABLE);
       
        ALTER TABLE "ELEMENTO" MODIFY ("X" NOT NULL ENABLE);
       
        ALTER TABLE "ELEMENTO" MODIFY ("Y" NOT NULL ENABLE);
       
        ALTER TABLE "ELEMENTO" MODIFY ("FECHAHORA_CREACION" NOT NULL ENABLE);
      /
      --------------------------------------------------------
      --  Constraints for Table UNIDAD
      --------------------------------------------------------
      
        ALTER TABLE "UNIDAD" MODIFY ("ID_ELEM" NOT NULL ENABLE);
       
        ALTER TABLE "UNIDAD" MODIFY ("PORCENTAJE_SALUD" NOT NULL ENABLE);
       
        ALTER TABLE "UNIDAD" MODIFY ("NOMBRE" NOT NULL ENABLE);
       
        ALTER TABLE "UNIDAD" MODIFY ("TIPO" NOT NULL ENABLE);
       
        ALTER TABLE "UNIDAD" ADD CONSTRAINT "UNIDAD_PK" PRIMARY KEY ("ID_ELEM") ENABLE;
      /
      --------------------------------------------------------
      --  Ref Constraints for Table ELEMENTO
      --------------------------------------------------------
      
      --------------------------------------------------------
      --  Ref Constraints for Table UNIDAD
      --------------------------------------------------------
      
        ALTER TABLE "UNIDAD" ADD CONSTRAINT "UNIDAD_ELEMENTO_FK1" FOREIGN KEY ("ID_ELEM")
             REFERENCES "ELEMENTO" ("ID_ELEM") ENABLE;
       
      /
      Edited by: 975362 on 06-12-2012 04:47 AM

      Edited by: BluShadow on 06-Dec-2012 12:51
      added {noformat}
      {noformat} tags for readability of code/data.  Please read {message:id=9360002} and learn to do this yourself in future.
      
      Edited by: 975362 on 06-12-2012 05:44 AM
      added example data.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: getting ORA-01403:, when it shouldn't
          Solomon Yakobson
          975362 wrote:
          exec p_colegas(19)
          Mist likely:
          select unidad.tipo, elemento.ciudad from unidad,elemento where unidad.id_elem=19 and elemento.id_elem=19;
          raises no data found. Issue the above statement and make sure it return one and only one row.

          SY.
          • 2. Re: getting ORA-01403:, when it shouldn't
            978365
            no, the query you wrote gives no problem and returns one and only one row with the data expected in this example is:
            select unidad.tipo, elemento.ciudad from unidad,elemento where unidad.id_elem=19 and elemento.id_elem=19;
            TIPO                 CIUDAD             
            -------------------- --------------------
            Obrero               Infernalia     
            as mentioned the problem starts when using the query in a procedure where i have to use the into clause,
            rigth now i'm formating the tables this procedure uses and example data to provide it here for more information.

            Edited by: 975362 on 06-12-2012 05:49 AM
            Edited first post, adding example data
            • 3. Re: getting ORA-01403:, when it shouldn't
              ascheffer
              That's because you use X as parameter for the procedure, and X is also a column in the table :)

              So use a table alias in the query or use another name for the parameter.

              Edited by: ascheffer on Dec 6, 2012 3:04 PM
              • 4. Re: getting ORA-01403:, when it shouldn't
                978365
                Thank you, i really missed that reference and got me trapped,
                the procedure:
                create or replace 
                PROCEDURE "P_COLEGAS" (prueba00 in number) as
                 ctipo varchar2(20);
                 asd varchar2(20);
                
                 cursor curnombre is
                 select nombre from unidad,elemento where elemento.id_elem=unidad.id_elem and unidad.tipo=ctipo and elemento.ciudad=asd;
                
                 begin
                  select unidad.tipo, elemento.ciudad  into ctipo,asd from unidad,elemento where unidad.id_elem=prueba00 and elemento.id_elem=prueba00;
                  for blah in curnombre loop
                  DBMS_OUTPUT.PUT_LINE('nombre unidad: '||blah.nombre);
                  end loop;
                  
                 end;
                now works without problem.
                • 5. Re: getting ORA-01403:, when it shouldn't
                  Solomon Yakobson
                  Oops, I mi9ssed table ELEMENTO has column X. When you use:
                  where unidad.id_elem=x and elemento.id_elem=x;
                  column names take precedence over PL/SQL variables wnd X is resolved as table ELEMENTO column X. not as PL/SQL procedure parameter X. Change PL/SQL procedure parameter name:
                  SQL> create or replace
                    2  procedure P_COLEGAS(x in number) as
                    3  ctipo varchar2(20);
                    4  asd varchar2(20);
                    5   
                    6  cursor curnombre is
                    7  select nombre from unidad,elemento where (elemento.id_elem=unidad.id_elem and unidad.tipo=ctipo
                   and elemento.ciudad=asd);
                    8   
                    9  begin
                   10  select unidad.tipo, elemento.ciudad into ctipo,asd from unidad,elemento where unidad.id_elem=x 
                  and elemento.id_elem=x;
                   11  for blah in curnombre loop
                   12  DBMS_OUTPUT.PUT_LINE('nombre unidad: '||blah.nombre||' ');
                   13  end loop;
                   14  end;
                   15  /
                  
                  Procedure created.
                  
                  SQL> exec p_colegas(19)
                  BEGIN p_colegas(19); END;
                  
                  *
                  ERROR at line 1:
                  ORA-01403: no data found
                  ORA-06512: at "SCOTT.P_COLEGAS", line 9
                  ORA-06512: at line 1
                  
                  
                  SQL> create or replace
                    2  procedure P_COLEGAS(p_x in number) as
                    3  ctipo varchar2(20);
                    4  asd varchar2(20);
                    5   
                    6  cursor curnombre is
                    7  select nombre from unidad,elemento where (elemento.id_elem=unidad.id_elem and unidad.tipo=ctipo
                   and elemento.ciudad=asd);
                    8   
                    9  begin
                   10  select unidad.tipo, elemento.ciudad into ctipo,asd from unidad,elemento where unidad.id_elem=p_
                  x and elemento.id_elem=p_x;
                   11  for blah in curnombre loop
                   12  DBMS_OUTPUT.PUT_LINE('nombre unidad: '||blah.nombre||' ');
                   13  end loop;
                   14  end;
                   15  /
                  
                  Procedure created.
                  
                  SQL> exec p_colegas(19)
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> 
                  SY.
                  • 6. Re: getting ORA-01403:, when it shouldn't
                    978365
                    helpful information SY, thank you.