Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01403: no data found
ORA-01403: no data found [message #199043] Thu, 19 October 2006 19:15 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

hello:

i get the follow error with the procedure

CREATE OR REPLACE PROCEDURE DM08_PROCEDURE_CP_TIPOASEN IS
v_id_base NUMBER(10) :=0;
v_id_orig NUMBER(10) :=0;
v_id_sepo NUMBER(10) :=0;
v_tasen_orig VARCHAR2(40) :=null;
v_tasen_sepo VARCHAR2(40) :=null;
v_valida NUMBER(10):=0;

CURSOR C_TIPOASEN_ORIGEN IS
select ID_BASE_ORIGEN, ID_TIPO_ASEN, LTRIM(RTRIM(UPPER(REPLACE(NOM_TIPO_ASEN,'-','')))) FROM DM08_TIPO_ASEN;
--CURSOR C_TIPOASEN_SEPOMEX IS
--select ID_TIPO_ASENTAMIENTO, LTRIM(RTRIM(UPPER(TIPO_ASENTAMIENTO))) FROM DM08_SEPOMEX_TIPO_ASENTAMIENTO;

BEGIN

OPEN C_TIPOASEN_ORIGEN;
LOOP
FETCH C_TIPOASEN_ORIGEN INTO v_id_base, v_id_orig, v_tasen_orig;
EXIT WHEN C_TIPOASEN_ORIGEN %NOTFOUND;
v_tasen_orig := UPPER(LTRIM(RTRIM(v_tasen_orig)));
SELECT NVL(ID_TIPO_ASENTAMIENTO,0), UPPER(LTRIM(RTRIM(TIPO_ASENTAMIENTO))) INTO v_id_sepo, v_tasen_sepo FROM DM08_SEPOMEX_TIPO_ASENTAMIENTO where LTRIM(RTRIM(UPPER(TIPO_ASENTAMIENTO))) like v_tasen_orig;
IF v_id_sepo <> 0 then
DBMS_OUTPUT.PUT_LINE (v_id_sepo||' '||v_tasen_sepo);
-- INSERT INTO DM08_CP_TEMPORAL_TIPOASEN VALUES (v_id_base, v_id_orig, v_id_sepo, v_tasen_sepo);
-- COMMIT;
END IF;
END LOOP;
CLOSE C_TIPOASEN_ORIGEN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
Procedure created.

SQL> exec DM08_PROCEDURE_CP_TIPOASEN
2 AEROPUERTO
3 BARRIO
4 BASE NAVAL
5 CAMPAMENTO
6 CAMPO MILITAR
7 CENTRO
8 CENTRO URBANO
ORA-01403: no data found

PL/SQL procedure successfully completed.

both table has data

please help
Re: ORA-01403: no data found [message #199069 is a reply to message #199043] Fri, 20 October 2006 00:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First remove this
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);

These two lines add absolutely NOTHING to your code. It only obfuscates which line the actual error occurs.

For a record fetched from the cursor, there is no corresponding record to be retrieved from your 'select into' query.
Re: ORA-01403: no data found [message #199137 is a reply to message #199069] Fri, 20 October 2006 08:40 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

But the result above shown are in my table;

I can solve it

Thanks
Alex

[Updated on: Fri, 20 October 2006 11:49]

Report message to a moderator

Re: ORA-01403: no data found [message #199452 is a reply to message #199137] Tue, 24 October 2006 07:48 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SELECT NVL(ID_TIPO_ASENTAMIENTO,0), UPPER(LTRIM(RTRIM(TIPO_ASENTAMIENTO))) INTO v_id_sepo, v_tasen_sepo FROM DM08_SEPOMEX_TIPO_ASENTAMIENTO where LTRIM(RTRIM(UPPER(TIPO_ASENTAMIENTO))) like v_tasen_orig;

It is definitely failing in this statement. Try running your procedure by commenting out this statement or try to put this statement inside a begin exception block and print out the record for which it is failing.

Also what i am not able to understand is why do you need to use "like " when you are not including any wildcards in your search criteria ?

As frank has mentioned earlier it doesn't make any sense to catch an exception called "WHEN OTHERS". If you don't know what the exception is going to be, leave it to oracle to manage it. Have a look in the website "asktom.oracle.com" and search for exception handling.

cheers
Previous Topic: ORA-06502: PL/SQL: numeric or value error (merged)
Next Topic: DBMS JOB SCHEDULING
Goto Forum:
  


Current Time: Thu Dec 08 22:08:06 CST 2016

Total time taken to generate the page: 0.27558 seconds