Home » SQL & PL/SQL » SQL & PL/SQL » Select for update where current of problem (11.1.0.7 Database)
Select for update where current of problem [message #407267] Tue, 09 June 2009 06:52 Go to next message
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 #407271 is a reply to message #407267] Tue, 09 June 2009 07:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
check the value of SQL%ROWCOUNT after the update and delete.
That will tell you the definitive answer of how many rows were affected by each piece of DML
Re: Select for update where current of problem [message #407272 is a reply to message #407267] Tue, 09 June 2009 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Stupid question but "did you commit?".

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Select for update where current of problem [message #407281 is a reply to message #407272] Tue, 09 June 2009 07:32 Go to previous messageGo to next message
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

Re: Select for update where current of problem [message #407282 is a reply to message #407281] Tue, 09 June 2009 07:37 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your code is not formatted, you must read the guide and use code tags.

Try to reproduce it with a simple test case that you will post and we can check and reproduce at our places.

Regards
Michel
Previous Topic: Creating Hash Partition
Next Topic: sql for incremental load
Goto Forum:
  


Current Time: Thu Dec 08 08:38:03 CST 2016

Total time taken to generate the page: 0.11871 seconds