Home » SQL & PL/SQL » SQL & PL/SQL » Deleting Problem using PL/SQL
Deleting Problem using PL/SQL [message #329422] Wed, 25 June 2008 06:59 Go to next message
basildon
Messages: 2
Registered: June 2008
Junior Member
Ran the following succesfully except when I added the piece of script at the bottom to DELETE, I was given error message saying D.Mission_date needed to be declared.

I'm new to this could someone please enlighten?

Thanks

DECLARE

      CURSOR incsec IS
        SELECT d.mission_id id,
	       mt.description descript,  d.mission_date
        FROM   DAY2_EX1 d
        INNER JOIN mission_types mt 
        ON     d.mission_type_id = mt.mission_type_id;

BEGIN

      FOR m_scan IN incsec LOOP
        IF m_scan.descript Like 'Assa%' THEN
           UPDATE DAY2_EX1 d
           SET    d.security_level = d.security_level +1
           WHERE  d.mission_id = m_scan.id;

        ELSIF d.mission_date > '31/Dec/2001' THEN 
          DELETE FROM  DAY2_EX1 d        
          WHERE  d.mission_id = m_scan.id;
        END IF; [/B]                
     END LOOP;
END;  
/


[Make post less unreadable]

[Updated on: Wed, 25 June 2008 07:13] by Moderator

Report message to a moderator

Re: Deleting Problem using PL/SQL [message #329425 is a reply to message #329422] Wed, 25 June 2008 07:14 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
d.mission_date > '31/Dec/2001' is not valid.


Instead of d.mission_date it should be m_scan.mission_date.
Moreover '31/Dec/2001' is not valid.Use conversion data type


Re: Deleting Problem using PL/SQL [message #329426 is a reply to message #329422] Wed, 25 June 2008 07:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Compare your two IF statements:
IF m_scan.descript Like 'Assa%' THEN

and
ELSIF d.mission_date > '31/Dec/2001' THEN 


Now, given that your loop statement is like this:
FOR m_scan IN incsec LOOP
, an you think of any reason why previxing a loop record variabl with MSCAN. would result in a valid piece of SQL, but prefixing it with D. would not?
Deleting Problem using PL/SQL [message #329450 is a reply to message #329422] Wed, 25 June 2008 08:28 Go to previous messageGo to next message
basildon
Messages: 2
Registered: June 2008
Junior Member
Ran the following succesfully except when I added the piece of script at the bottom to DELETE, I was given error message saying D.Mission_date needed to be declared.

I'm new to this could someone please enlighten?

Thanks

DECLARE

CURSOR incsec

IS

SELECT

d.mission_id id,
mt.description descript, d.mission_date

FROM

DAY2_EX1 d

INNER JOIN mission_types mt ON

d.mission_type_id = mt.mission_type_id

;

BEGIN

FOR m_scan IN incsec

LOOP

IF m_scan.descript Like 'Assa%' THEN

UPDATE DAY2_EX1 d

SET

d.security_level = d.security_level +1

WHERE

d.mission_id = m_scan.id;


ELSIF d.mission_date > '31/Dec/2001'

THEN DELETE FROM DAY2_EX1 d[/B]

WHERE

d.mission_id = m_scan.id;

END IF; [/B]

END LOOP;

END;

/

Re: Deleting Problem using PL/SQL [message #329452 is a reply to message #329450] Wed, 25 June 2008 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

'this is a string 2008-12-31 NOT a date'

use TO_DATE()
Re: Deleting Problem using PL/SQL [message #329454 is a reply to message #329450] Wed, 25 June 2008 08:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Duplicate post.

Locked.
Previous Topic: difference between pipelined table function and table function
Next Topic: SQL Query Rewrite
Goto Forum:
  


Current Time: Tue Dec 06 04:19:18 CST 2016

Total time taken to generate the page: 0.14038 seconds