Select for update where current of problem [message #407267] |
Tue, 09 June 2009 06:52  |
darrenedmonds
Messages: 2 Registered: June 2009
|
Junior Member |
|
|
I am hoping someone can help me with this problem. I am trying to use select for update and where current of funtionality but its not actually updating or deleting. Please see the code below:
DECLARE
CURSOR fpi_cursor IS
SELECT b.sku, a.skn, a.orderqty, a.packmethod
FROM firstpiece.fpi_to_wms_staging a, WH1.SKUXPACK b
WHERE a.skn = substr(b.sku,1,6) AND a.orderqty = b.orderqty
FOR UPDATE;
fpi_val fpi_cursor%ROWTYPE;
BEGIN
OPEN fpi_cursor;
LOOP
FETCH fpi_cursor INTO fpi_val;
EXIT WHEN fpi_cursor%NOTFOUND;
UPDATE wh1.skuxpack
SET packmethod = fpi_val.packmethod,
editdate = SYSDATE,
editwho = 'FPI'
WHERE CURRENT OF fpi_cursor;
dbms_output.put_line(fpi_val.sku ||' '|| fpi_val.skn||' update');
DELETE FROM firstpiece.fpi_to_wms_staging WHERE CURRENT OF fpi_cursor;
dbms_output.put_line(fpi_val.sku ||' '|| fpi_val.skn||' delete');
END LOOP;
CLOSE fpi_cursor;
END;
/
COMMIT;
I know the cursor is populated with data by the use of the dbms_output however neither the update or the delete from statement does anything. I have tried them seperately as well and still no records are updated/deleted. Any ideas?
|
|
|
|
|
Re: Select for update where current of problem [message #407281 is a reply to message #407272] |
Tue, 09 June 2009 07:32   |
darrenedmonds
Messages: 2 Registered: June 2009
|
Junior Member |
|
|
Michel,
I did perform a commit immediately after the closing the cursor and no records were updated/deleted.
I will read through the Forum guide shortly and my oracle version is 11.1.0.7.
Here is the formatted code:
DECLARE
CURSOR fpi_cursor IS
SELECT b.sku,
a.skn,
a.orderqty,
a.packmethod
FROM firstpiece.fpi_to_wms_staging a,
wh1.skuxpack b
WHERE a.skn = Substr(b.sku,1,6)
AND a.orderqty = b.orderqty
FOR UPDATE; -- Lock both tables due to delete.
ROWCOUNT VARCHAR(10);
fpi_val fpi_cursor%ROWTYPE;
BEGIN
OPEN fpi_cursor;
LOOP
FETCH fpi_cursor INTO fpi_val;
EXIT WHEN fpi_cursor%NOTFOUND;
UPDATE wh1.skuxpack b
SET b.packmethod = fpi_val.packmethod,
b.editdate = SYSDATE,
b.editwho = 'FPI'
WHERE CURRENT OF fpi_cursor;
ROWCOUNT := To_char(SQL%ROWCOUNT);
dbms_output.Put_line(fpi_val.sku
||' '
||fpi_val.skn
||' update '
||ROWCOUNT);
DELETE FROM firstpiece.fpi_to_wms_staging a
WHERE CURRENT OF fpi_cursor;
ROWCOUNT := To_char(SQL%ROWCOUNT);
dbms_output.Put_line(fpi_val.sku
||' '
||fpi_val.skn
||' delete '
||ROWCOUNT);
END LOOP;
CLOSE fpi_cursor;
END;
Jrowbottom,
I amended the code to capture rowcount and output it with each of the dbvms_output statements (e.g dbms_output.put_line(fpi_val.sku ||' '|| fpi_val.skn||' update '||rowcount)) but it was 0 for each entry.
I am running this as sysdba so there should be no issues with permissions.
Here is a selection of the output from running this in sqlplus:
584900 584900 update 0
584900 584900 delete 0
584900 584900 update 0
584900 584900 delete 0
.....
Thanks,
Darren.
[Edit MC: add code tags]
[Updated on: Tue, 09 June 2009 07:38] by Moderator Report message to a moderator
|
|
|
|