This discussion is archived
2 Replies Latest reply: Dec 6, 2012 4:44 AM by 978365 RSS

getting ORA-01403:, when it shouldn't

978365 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points