Home » SQL & PL/SQL » SQL & PL/SQL » procedure
procedure [message #36522] Mon, 03 December 2001 03:12 Go to next message
Frederik
Messages: 8
Registered: December 2001
Junior Member
Iīm working on a procedure, but I keep getting this error msg: SQL Error: ORACLE - ORA-20100: ORA-01403: no data found.

I believe the error comes from the following:

--> Variables
wEmpId NUMBER;
wFecha DATE;
wDiaEnt VARCHAR2(10);
wDiaSal VARCHAR2(10);
wEnt NUMBER;
wSal NUMBER;
--> Cursor
CURSOR Cursor1 IS
SELECT empid, fecha
FROM MY_TABLE_1
WHERE SessionId = wSessionId;

BEGIN
OPEN Cursor1;
FETCH Cursor1 INTO wEmpId, wFecha;
WHILE (Cursor1%FOUND) LOOP
SELECT TO_CHAR(ent,'Day'),TO_CHAR(sal,'Day'),(60*TO_CHAR(ent,'HH24')+TO_CHAR(ent,'MI')),(60*TO_CHAR(sal,'HH24')+TO_CHAR(sal,'MI'))
INTO wDiaEnt, wDiaSal, wEnt, wSal
FROM MY_TABLE_2
WHERE empid = wEmpId AND
TO_DATE(ent,'DD/MM/YY') = wFecha; --<< is the error here??

Any help would be apprecitated.
TIA,

Fred.-

----------------------------------------------------------------------
Re: procedure [message #36527 is a reply to message #36522] Mon, 03 December 2001 04:44 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
ent is date or character datatype?. because one place you are using to_Char and other place to_date. i think it is date.
if so, try this in where clause
1) both are date fields, so you can simply write like
ent=wfecha

2)if you have time part in date, you have to change where clause

trunc(ent)=trunc(wfecha)

3) alternative to method 2 is

to_char(ent,'mm/dd/yyyy')=to_char(wfecha,'mm/dd/yyyy')

----------------------------------------------------------------------
Re: procedure [message #36532 is a reply to message #36527] Mon, 03 December 2001 05:31 Go to previous messageGo to next message
Frederik
Messages: 8
Registered: December 2001
Junior Member
Thx for the reply.
Yep, ent is date with a time part ('DD/MM/YY HH:MM:SS').
I tried method 2 & 3, but it didnīt work...
ent is a Field from MY_TABLE_2 and wFecha is a variable I fetch from MY_TABLE_1 (itīs only date; format 'DD/MM/YY').

----------------------------------------------------------------------
Re: procedure [message #36536 is a reply to message #36527] Mon, 03 December 2001 08:25 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
i think some of records in my_table1 are not there in my_table2
look at following code and set serveroutput on before executing procedure.

OPEN Cursor1;
FETCH Cursor1 INTO wEmpId, wFecha;
WHILE (Cursor1%FOUND) LOOP
BEGIN
SELECT TO_CHAR(ent,'Day'),TO_CHAR(sal,'Day'),(60*TO_CHAR(ent,'HH24')+TO_CHAR(ent,'MI')),(60*TO_CHAR(sal,'HH24')+TO_CHAR(sal,'MI'))
INTO wDiaEnt, wDiaSal, wEnt, wSal
FROM MY_TABLE_2
WHERE empid = wEmpId AND
TO_DATE(ent,'DD/MM/YY') = wFecha;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Empid : '||wempid ||' wfecha : '||wfecha||' : record not found in my_table2');
end;
end loop

----------------------------------------------------------------------
Previous Topic: Trigger to operate on multiple tables, via OR
Next Topic: add_months
Goto Forum:
  


Current Time: Wed Apr 08 23:31:01 CDT 2020