Re: Does this work or do I have to use dynamic sql?

Zman wrote:

> <snipped>
>
> Never done it that way. I think this will work but it is obviously untested.
> No cursor loop needed.
> In your example, perhaps it was not working because the commit was outside the loop.
>
> UPDATE ud set
> (name, vorname, bereich, stammnr, tel_int, kostenstl, unique_id) =
> (select SUBSTR(metaupdate_rec.sLastName,1,25) ,
> SUBSTR(metaupdate_rec.sFirstName,1,25) ,
> metaupdate_rec.sDepartment,
> metaupdate_rec.sPersonalNumber,
> SUBSTR(metaupdate_rec.sOfficePhoneNumber,1,15) ,
> SUBSTR(metaupdate_rec.sKST,1,10),
> metaupdate_rec.sobjUniqueKey
> from metaupdate_rec WHERE ud.Username = metaupdate_rec.sNetzwerklogin)

Good gosh man ... "not working because the commit is outside the loop"?

This is Oracle not SQL Server. The commit should NEVER be INSIDE the loop.

If you've been putting them inside a loop you have been slaughtering performance and scalability and opening yourself up for the dread ORA-01555. Stop it! Stop it now! ;-)

And it is definitely not the cause of the problem.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)