Cursor%FOUND problem

[veledrom]

Hi,
I can't find the missing thing in this code. "05-MAY-08" is in the table but, ( CURSOR_TARIH%FOUND ) never returns TRUE. When i do manuel query it is ok.
Thanks

create or replace
PROCEDURE A_SIPARIS_EKLE_SP 
IS

CURSOR CURSOR_TARIH IS 
SELECT TARIH FROM A_ACCOUNT WHERE TARIH = to_date('05-MAY-08', 'DD-MM-YY');

BEGIN

OPEN CURSOR_TARIH;

INSERT INTO A_SIPARIS (MID,STATUS) VALUES (1, 'NO');

IF ( CURSOR_TARIH%FOUND ) THEN
  UPDATE A_ACCOUNT SET PRICE = '10' WHERE TARIH = to_date('05-MAY-08', 'DD-MM-YY');  
ELSE
  INSERT INTO A_ACCOUNT( TARIH, PRICE) values ( SYSDATE,  '1' );
END IF;

COMMIT;

CLOSE CURSOR_TARIH;

END;

[peter_budo]

Can you please share your solution?

[veledrom]

I don't insert date with SYSDATE anymore. I use VARCHAR for it and get date manual.

[A Hill]

> TO_DATE('05-MAY-08', 'DD-MM-YY')

Isn't it because of the mismatch between "MAY" and "MM"?
Try replacing "MM" with "MON".

[babyDBA]

You must enter the date in the format that you specified in the script ('DD.MM.YY' or 'DD-MON-YY') and it looks like you forgot to do fetch

I added comments to the lines that I corrected / added.
I hope it will be helpful

CREATE OR REPLACE
PROCEDURE A_SIPARIS_EKLE_SP 
IS
 
CURSOR CURSOR_TARIH IS 
--SELECT TARIH FROM A_ACCOUNT WHERE TARIH = TO_DATE('05-MAY-08', 'DD-MM-YY');
SELECT TARIH FROM A_ACCOUNT WHERE TARIH = TO_DATE('05-MAY-08', 'DD-MON-YY'); -- correct

tarihValue date;  -- add
 
BEGIN
 
OPEN CURSOR_TARIH;
 
INSERT INTO A_SIPARIS (MID,STATUS) VALUES (1, 'NO');
 
FETCH CURSOR_TARIH INTO tarihValue;  -- add
IF ( CURSOR_TARIH%FOUND ) THEN
  --UPDATE A_ACCOUNT SET PRICE = '10' WHERE TARIH = TO_DATE('05-MAY-08', 'DD-MM-YY');  
  UPDATE A_ACCOUNT SET PRICE = '10' WHERE TARIH = TO_DATE('05-MAY-08', 'DD-MON-YY');  -- correct  
ELSE
  --INSERT INTO A_ACCOUNT( TARIH, PRICE) VALUES ( SYSDATE,  '1' );
  INSERT INTO A_ACCOUNT( TARIH, PRICE) VALUES ( TRUNC(SYSDATE),  '1' );  -- correct
END IF;
 
COMMIT;
 
CLOSE CURSOR_TARIH;
 
END;