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

Marc Eggenberger wrote:

> 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.
>
> --
> mfg
> Marc Eggenberger

Just looking at it quickly your code looks fine. You could assign it all to a variable and execute it, called Native Dynamic SQL, but it would be far less efficient.

First thing I'd do is pull that SQL statement from the cursor and run it independently in SQL*Plus to see what it returns. Then manually plug the result into your update statement and see what happens.

There could be many causes it isn't working but I would expect most of them to give you an exception. The fact that you report no error message means the cursor likely is not fetching any records.

--
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)