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

Marc Eggenberger <nw1_at_devnull.ch> wrote in message news:<MPG.199230b0ae3ec384989690_at_news.cis.dfn.de>...
> Hi there.
>
> Should this work:
>
> DECLARE
> CURSOR metadir_cur IS
> SELECT sobjUniqueKey, sPersonalNumber, sLastName,
> sFirstName, sNetzwerkLogin, sDepartment, sKST, sOfficePhoneNumber
> FROM metadir_import.tblPersonalData;
>
> BEGIN
> FOR metaupdate_rec IN metadir_cur
> LOOP
> UPDATE ud SET NAME = SUBSTR(metaupdate_rec.sLastName,1,25)
> , Vorname = SUBSTR(metaupdate_rec.sFirstName,1,25) , Bereich =
> metaupdate_rec.sDepartment, Stammnr = metaupdate_rec.sPersonalNumber,
> Tel_Int = SUBSTR(metaupdate_rec.sOfficePhoneNumber,1,15) , Kostenstl =
> SUBSTR(metaupdate_rec.sKST,1,10), Unique_ID =
> metaupdate_rec.sobjUniqueKey
> WHERE Username = metaupdate_rec.sNetzwerklogin;
> END LOOP;
> COMMIT;
> END;
>
>
> It runs without errors but it seems that it doesn't actually update my
> table ud.
>
> It runs on Oracle 8.1.7.0.0.
>
> Havent done much coding in Oracle yet.
> In T-SQL I would put the Update statement in a varchar first and then
> exec it. Do I have to something similar in Oracle too?
>
> Thanks for any hints.

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)