Home » SQL & PL/SQL » SQL & PL/SQL » Need Help With Simple PL/SQL Script (Oracle 8i, Windows XP)
Need Help With Simple PL/SQL Script [message #328732] Sun, 22 June 2008 09:00 Go to next message
Veronica316
Messages: 4
Registered: June 2008
Junior Member
Hello Everyone,

I am completely new to PL/SQL Embarassed , we have Oracle 8i and I am trying to create a script to eliminate the words "City" and "Town" from all entries in a certain field. Here's what I did :

DECLARE
CURSOR MyCursor IS
SELECT COUNTRY, COUNTY_STATE, TOWN_CITY
FROM BSEC.COUNTERFEIT
FOR UPDATE OF COUNTRY, COUNTY_STATE, TOWN_CITY;
MyRecord MyCursor%ROWTYPE;

BEGIN
OPEN MyCursor;
LOOP
FETCH MyCursor INTO MyRecord;
EXIT WHEN MyCursor%NOTFOUND;

IF UPPER(MyRecord.TOWN_CITY) LIKE '%CITY'
THEN UPDATE BSEC.COUNTERFEIT
SET TOWN_CITY = REPLACE(TOWN_CITY, 'City');
ELSE IF UPPER(MyRecord.TOWN_CITY) LIKE '%TOWN'
THEN UPDATE BSEC.COUNTERFEIT
SET TOWN_CITY = REPLACE(TOWN_CITY, 'Town');
End IF;
END IF;
END LOOP;
CLOSE MyCursor;
END;

..only when executing this script it seems to be caught in some loop (it eventually gives up with the Oracle 'unable to extend segment' error). So what is wrong with the above script ?, and more importantly, can anyone suggest an alternate method to the above script (perhaps something more efficient, I wanted to use CASE statements, but I understand this is used in 9i onwards, so I have to use IF THEN ELSE).

Thanks in advance for your help !,
Veronica
Re: Need Help With Simple PL/SQL Script [message #328733 is a reply to message #328732] Sun, 22 June 2008 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 25038
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
Re: Need Help With Simple PL/SQL Script [message #328734 is a reply to message #328732] Sun, 22 June 2008 09:10 Go to previous messageGo to next message
Veronica316
Messages: 4
Registered: June 2008
Junior Member
The formatted code is as follows :

DECLARE
  CURSOR MyCurSor IS 
    SELECT Country,
           County_State,
           Town_City
    FROM   bsec.Counterfeit
    FOR UPDATE OF Country,
                  County_State,
                  Town_City;
   MyRecord  MyCurSor%ROWTYPE;
BEGIN
  OPEN MyCurSor;
  
  LOOP
    FETCH MyCurSor INTO MyRecord;
    
    EXIT WHEN MyCurSor%NOTFOUND;
    
    IF Upper(MyRecord.Town_City) LIKE '%CITY' THEN
      UPDATE bsec.Counterfeit
      SET    Town_City = REPLACE(Town_City,'City');
    ELSE
      IF Upper(MyRecord.Town_City) LIKE '%TOWN' THEN
        UPDATE bsec.Counterfeit
        SET    Town_City = REPLACE(Town_City,'Town');
      END IF;
    END IF;
  END LOOP;
  
  CLOSE MyCurSor;
END;
Re: Need Help With Simple PL/SQL Script [message #328735 is a reply to message #328732] Sun, 22 June 2008 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>(it eventually gives up with the Oracle 'unable to extend segment' error).
If you say so.
Perhaps the tablespace needs to be increased in size.
Re: Need Help With Simple PL/SQL Script [message #328736 is a reply to message #328735] Sun, 22 June 2008 09:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Plus, you don't have a where clause in your updates.

Basically you could just run

UPDATE bsec.Counterfeit
   SET Town_City = REPLACE(Town_City,'City');

UPDATE bsec.Counterfeit
   SET Town_City = REPLACE(Town_City,'Town');


and the result would be the same.
Re: Need Help With Simple PL/SQL Script [message #328738 is a reply to message #328736] Sun, 22 June 2008 09:29 Go to previous messageGo to next message
Veronica316
Messages: 4
Registered: June 2008
Junior Member
ThomasG wrote on Sun, 22 June 2008 09:23
Plus, you don't have a where clause in your updates.

Basically you could just run

UPDATE bsec.Counterfeit
   SET Town_City = REPLACE(Town_City,'City');

UPDATE bsec.Counterfeit
   SET Town_City = REPLACE(Town_City,'Town');


and the result would be the same.

I do realise I could just run update statements to have the desired effect.

The code I enclosed is a simplified version of a series of updates that would need to be run regularly. Hence my reason for having this in a script, as opposed to having to call individual update statements for 20 plus columns in different tables.
Re: Need Help With Simple PL/SQL Script [message #328739 is a reply to message #328738] Sun, 22 June 2008 09:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When you have to do it in a cursor loop then you have to add the

WHERE CURRENT OF cursor_name;


where clause to the updates.

It could very well be that you are running out of place in some temporary table space now, since you update the entire table twice for every row that the cursor returns.
Re: Need Help With Simple PL/SQL Script [message #328749 is a reply to message #328739] Sun, 22 June 2008 10:37 Go to previous messageGo to next message
Veronica316
Messages: 4
Registered: June 2008
Junior Member
ThomasG wrote on Sun, 22 June 2008 09:33
When you have to do it in a cursor loop then you have to add the

WHERE CURRENT OF cursor_name;


where clause to the updates.

It could very well be that you are running out of place in some temporary table space now, since you update the entire table twice for every row that the cursor returns.

Thank you for the help Thomas, indeed it was the WHERE CURRENT OF statement missing from my code that was preventing the procedure from executing. I inserted it into the code and it took seconds to execute successfully.

For anyone who is interested, I modified the code slightly and it is working nicely, as follows :

DECLARE 
CURSOR 	MyCursor IS 
SELECT   COUNTRY, COUNTY_STATE, TOWN_CITY
FROM      BSEC.COUNTERFEIT
		FOR UPDATE OF COUNTRY, COUNTY_STATE, TOWN_CITY;
MyRecord	MyCursor%ROWTYPE;

BEGIN
	OPEN MyCursor;
	LOOP
		FETCH MyCursor INTO MyRecord;
		EXIT WHEN MyCursor%NOTFOUND;

		IF trim(UPPER(MyRecord.TOWN_CITY)) LIKE '%CITY'
		THEN  UPDATE BSEC.COUNTERFEIT
			SET TOWN_CITY =  TRIM(REPLACE(UPPER(TOWN_CITY), 'CITY'))
			WHERE CURRENT OF MyCursor;

		ELSE IF trim(UPPER(MyRecord.TOWN_CITY)) LIKE '%TOWN'
		THEN  UPDATE BSEC.COUNTERFEIT
			SET TOWN_CITY =  TRIM(REPLACE(UPPER(TOWN_CITY), 'Town'))
			WHERE CURRENT OF MyCursor;
		END IF;
		END IF;
	END LOOP;
	CLOSE MyCursor;
END;

Re: Need Help With Simple PL/SQL Script [message #328759 is a reply to message #328749] Sun, 22 June 2008 12:11 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member

Thanks for the feedback, glad it worked. /forum/images/message_icons/icon14.gif


Previous Topic: Dynamically accessing columns of :NEW in trigger
Next Topic: doubts on update (merged)
Goto Forum:
  


Current Time: Mon Dec 05 12:48:36 CST 2016

Total time taken to generate the page: 0.09558 seconds