Re: PROC Problem : Select for update

r0cky_at_insightbb.com (Wario) wrote in message news:<9204a53e.0408250655.21ce545a_at_posting.google.com>...
> rbenzarti_at_hotmail.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7fa25ffe.0408200852.41c1de37_at_posting.google.com>...
> > Hello,
> > I am working with Oracle 9Ri2 version on Linux AS 2.1 for IA64.
> >
> > I have a problem when i try to lanch the following program :
> >
> >
> > EXEC SQL PREPARE MyStmt FROM
> > SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR
> > UPDATE;
> >
> > EXEC SQL DECLARE MyCursor CURSOR FOR MyStmt;
> >
> > EXEC SQL OPEN MyCursor;
> >
> > EXEC SQL DESCRIBE MyStmt into q_APP;
> >
> >
> > EXEC SQL FETCH MyCursor into :Var1;
> >
> > EXEC SQL CLOSE MyCursor;
> >
> > When I don't use the PREPARE Statement i don't have problem. But when
> > i add the PREPARE statement program crached when it try to Open Cursor
> > ( EXEC SQL OPEN MyCursor)
> >
> > Thank you for your help.
>
> Try using PL/SQL
>
> declare
> cursor work_cur is
> SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR UPDATE;
> begin
> for x in work_cur loop
> update tab1 set champ1 = 'test'
> where current of work_cur;
>
> end loop;
>
> commit;
> end;
> /

Why use the declare?

begin

     for x in (
       SELECT CHAMP1 
       FROM TAB1 
       WHERE CHAMP2="4" 
         AND CHAMP3 = "C" FOR UPDATE
      )
     loop
         update tab1 set champ1 = 'test'
         where current of work_cur;
        
     end loop;
     
     commit;