2 Replies Latest reply: Dec 6, 2012 6:44 AM by 978365 RSS

    getting ORA-01403:, when it shouldn't

    978365
      Greetings, i apologize beforehand for my spelling 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