Re: hi all

"Hasta" <hasta_l3_at_hotmail.com> a écrit dans le message de news: MPG.21a2b0185aba2cf99896a4_at_news.dommel.be... In article <47387fa4$0$15439$426a74cc_at_news.free.fr>, "Michel Cadot" <micadot{at}altern{dot}org> says...
>
> "mamta81" <roy.mamta_at_gmail.com> a écrit dans le message de news: 1194856541.430671.148720_at_i38g2000prf.googlegroups.com...
> | Hi , I have a file cfoil1.sql when i try to run tht in my sqldeveloper
> | or toad i get the following error message
> |
> | declare
> | CURSOR l1 is select bank_code,x,name,cat,idcode
> | from salchq order by cat,bank_code,idcode;
> | chq_no number(6):= 190423;
> | bnk_code char(2);
> | netsal number;
> | bnkname varchar2 (120);
> | cat char(1);
> | idcode char(6);
> | begin
> | OPEN l1;
> | LOOP
> | FETCH l1 into bnk_code, netsal,bnkname,cat,idcode;
> | EXIT when l1%notfound;
> | chq_no := chq_no + 1;
> | if bnk_code != 'AZ' then
> | insert into C_FOIL (CHQ_NO,BNKCODE,BNKNAME,NETSAL,CAT,IDCODE)
> | values (chq_no,bnk_code,bnkname,netsal,cat,idcode);
> | commit;
> | END IF;
> | END LOOP;
> | close l1;
> | end;
> | insert into sal_chq_printed select mon_start, chq_no, bnkcode,
> | bnkname, netsal, idcode from c_foil, s_globals
> |
> | /
> |
>
> Good example of bad pratice!
>
> The whole PL/SQL block is no more than:
>
> insert into C_FOIL (CHQ_NO,BNKCODE,BNKNAME,NETSAL,CAT,IDCODE)
> select chq_no+rownum-1, bank_code,x,name,cat,idcode
> from salchq
> where bnk_code != 'AZ'
> order by cat,bank_code,idcode;
>
> Regards
> Michel Cadot
>

Roy,

Michel's point is a very good and very important one.

However, as written his sql statement is not exactly equivalent to your loop with regard to chk_no, as I can see. Please consider it as a quick, near perfect illustration of the point, only.

Yes, a MERGE with an empty or dummy update part, will be better regarding the chk_no value.

Regards
Michel Cadot Received on Mon Nov 12 2007 - 13:11:22 CST