Re: PL/SQL doesn't seem to commit

tarbster_at_yahoo.com wrote:
> Hi all,
>
> I'd really appreciate some help with a PL/SQL problem in 9i. This
> routine compiles fine, and seems to update the column as intended (the
> DBMS_OUTPUT command shows me a UID for each row), but after the commit
> I find that the column is blank (which is what it was before the
> routine ran).
>
> SET PAGESIZE 50
> SET SERVEROUTPUT ON SIZE 1000000
>
> DECLARE
> LV_GUID VARCHAR2(54);
> CURSOR HARNLOC1_CURSOR IS SELECT HARNLOC1_ID FROM HARNLOC1;
> HL1_ROW HARNLOC1_CURSOR%ROWTYPE;
>
> BEGIN
>
> OPEN HARNLOC1_CURSOR;
>
> LOOP
>
> FETCH HARNLOC1_CURSOR INTO HL1_ROW;
> EXIT WHEN HARNLOC1_CURSOR%NOTFOUND;
> SELECT SYS_GUID INTO LV_GUID FROM DUAL;
>
> HL1_ROW.HARNLOC1_ID := 'UID' || SUBSTR(LV_GUID,1,6) || '-' ||
> SUBSTR(LV_GUID,7,11)|| '-' || SUBSTR(LV_GUID,18) ||
> '00000000000000000';
> DBMS_OUTPUT.PUT_LINE(HL1_ROW.HARNLOC1_ID);
>
> END LOOP;
>
> CLOSE HARNLOC1_CURSOR;
>
> END;
> /
>
> COMMIT;
>
> What change do I need to make to the code to make the update permanent?
>
>
> TIA
> Tarby

Your anonymous block does nothing. There is no insert ... there is no update ... there is no delete. It does precisely nothing but does it with remarkable inefficiency. And ... just to continue with a bit of bluster seems to do nothing that requires a loop.

What is it you are trying to do? Insert records into some table? Update records in some table? Because what I see could be rewritten as something like:

UPDATE t1
SET somecol = (SELECT [whatever and your concatenation]

                FROM t2
                WHERE somecondition IS TRUE);

Hope this helps.

BTW: Always include your version number and if 9i or above there is almost never a reason to use a cursor loop. Look instead at array processing (Morgan's Library at www.psoug.org).

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)