Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g44g2000cwa.googlegroups.com!not-for-mail
From: "Rauf Sarwar" <rs_arwar@hotmail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Newbie Cursor Question
Date: 6 Jun 2005 04:44:38 -0700
Organization: http://groups.google.com
Lines: 44
Message-ID: <1118058278.372169.232510@g44g2000cwa.googlegroups.com>
References: <42a41b40$1_1@glkas0286.greenlnk.net>
NNTP-Posting-Host: 194.131.247.213
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1118058283 4149 127.0.0.1 (6 Jun 2005 11:44:43 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 6 Jun 2005 11:44:43 +0000 (UTC)
In-Reply-To: <42a41b40$1_1@glkas0286.greenlnk.net>
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: g44g2000cwa.googlegroups.com; posting-host=194.131.247.213;
   posting-account=3xsT9QwAAAC3x7TJbwl3Hj0DXQs_bISq
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:119644



Keith wrote:
> What's wrong with this code? I want to be able to replace instances of the
> ampersand character with the string "AND" but this code seems to go into an
> end less loop and updates nothing.  The query in the cursor works on its own
> so there must be something I'm missing with the loop.
>
> Many thanks,
> Keith.
>
> declare
>
> cursor c1 is
>      select replace(ver_drg_title,chr(38),'AND')
>      from oms_version_tbl
>      where instr(ver_drg_title,'&',1,1)>0
>      for update of ver_drg_title;
>
> v_title oms_version_tbl.ver_drg_title%type;
>
> begin
>
> open c1;
>      loop
>           fetch c1 into v_title;
>           exit when c1%notfound;
>           update oms_version_tbl
>           set ver_drg_title = v_title
>           where current of c1;
>      end loop;
> close c1;
>
> end;

Why not just use,

update oms_version_tbl
   set ver_drg_title = replace(ver_drg_title, chr(38), 'AND')
   where instr(ver_drg_title, chr(38), 1, 1) > 0;

Regards
/Rauf