Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old March 13th, 2005, 06:07 PM
btsan btsan is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2005
Posts: 2 btsan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 22 m 52 sec
Reputation Power: 0
Lightbulb Anonymous block and CURSOR

New to Oracle9i and in need of help? I am trying to added a column to the mm_movie table named stk_flag that will hold a value of '*' if stock is $75 or more .Othrwise the value is NULL I am to added the column and create a anonymous block tha contains a CURSOR FOR loop to accompish tha task.


DECLARE
CURSOR nmovie_qty IS
Select value, price
from mm_movie
FOR UPDATE NOWAIT;
lv_stock mm_movie.stk_flag%TYPE;
BEGIN
FOR rec_prod IN nmovie_qty LOOP
IF rec_prod.value >= 75 THEN lv_stock :=
rec.prod.price '*';
END IF;
IF rec_prod.vlaue < 75 THEN lv_stock :=
rec.prod.price null;
END IF;
UPDATE mm_movie
SET stk_flag = lv_stock
WHERE CURRENT OF nmovie_qty = 75;
END LOOP;
COMMIT;
END;
/
ERROR MESSAGE IS as :

rec.prod.price '*';
*

ERROR at line 10:
ORA-06550: line 10, column 40:
PLS-00103: Encountered the symbol "*" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol "." was substituted for "*" to continue.
ORA-06550: line 13, column 42:
PLS-00103: Encountered the symbol "NULL" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol "*" was substituted for "
ORA-06550: line 17, column 34:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
. ( % ;

Reply With Quote
  #2  
Old March 14th, 2005, 03:22 AM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Jan 2003
Location: Paris Uppland
Posts: 1,784 swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37
Code:
UPDATE mm_movie SET stk_flag = case when value >= 75 then '*' end

Reply With Quote
  #3  
Old March 14th, 2005, 04:19 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,917 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 3 Days 14 h 46 m 3 sec
Reputation Power: 279
Quote:
Originally Posted by swampBoogie
Code:
UPDATE mm_movie SET stk_flag = case when value >= 75 then '*' end

Bleah ... too easy

Probably he prefers writing queries like "Select ... where stk_flag = '*'" over "Select ... where value > 75"

Reply With Quote
  #4  
Old March 15th, 2005, 02:06 PM
btsan btsan is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2005
Posts: 2 btsan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 22 m 52 sec
Reputation Power: 0
Question ?

Quote:
Originally Posted by pabloj
Bleah ... too easy

Probably he prefers writing queries like "Select ... where stk_flag = '*'" over "Select ... where value > 75"


Pablo ? what am I doing wrong ?
UPDATE mm_movie
SET STK_FLAG = case
When value >= 75 then '*'
end

After making the change then my DECLARE statement has a * errors?

/
DECLARE
CURSOR nmovie_qty IS
Select value, price
from mm_movie
FOR UPDATE NOWAIT;
lv_stock mm_movie.stk_flag%TYPE;
BEGIN
FOR rec_prod IN nmovie_qty LOOP
IF rec_prod.value >= 75 THEN lv_stock :=
rec.prod.price '*';
END IF;
IF rec_prod.vlaue < 75 THEN lv_stock :=
rec.prod.price null;
END IF;
UPDATE mm_movie
SET stk_flag = lv_stock
WHERE CURRENT OF nmovie_qty = 75;
END LOOP;
COMMIT;
END;
/

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > Anonymous block and CURSOR


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway
Stay green...Green IT