Number of updated rows is different than it should be when using DECLARE statement

[urosdigital]

Hello,

I have the following statement which returns wrong number of updated rows when using python (somethimes it returns 0 updated rows, sometimes a random number) but in fact it acts OK and i checked in db all rows are updated as expected:

update_query = '''
DECLARE
CURSOR C1 IS select d.id, S.Phone_Number, tmp.imei
from TMP_IMPORT_IMEI_PYTHON tmp, 
service s, 
device d
where tmp.msisdn=S.Phone_Number
and s.id=D.Assigned_To_Service_Id
and d.description=tmp.model;
BEGIN
FOR I IN C1
LOOP
UPDATE device
SET    device_identifier = I.imei
WHERE  id=I.id;
END LOOP;
END;
'''

If i use this function to perform update, cursor.rowcount returns wrong number of updated rows:

@timer 
def update_data_in_device_table():
    cursor.execute(preprod_imei_config.update_query)
    logger.info('Update query executed')
    print('Update query executed')
    updated_rows = cursor.rowcount
    logger.info(f'Updated {updated_rows} rows')
    print(f'Updated {updated_rows} rows')

    return

Is there a way to get exact number of updated rows when for this case?

BR,
Uros

[anthony-tuininga]

I'm not sure what you think the "right" number of rows ought to be, but the definition according to the documentation is that the value should be 1 for all PL/SQL blocks executed by a call to cursor.execute() -- so likely not what you would like to see. If you want the number of rows updated in your table you will need to use an OUT variable to your PL/SQL block.

[cjbj]

We can improve that doc :)

[urosdigital]

Hello @anthony-tuininga , @cjbj ,

Sorry for my late response. Somehow i omitted the fact Anthony pointed out.
Solved with the help of docs and sql statement modification.

update_query = '''
DECLARE
rows_updated NUMBER := 0; --added
CURSOR C1 IS select d.id, S.Phone_Number, tmp.imei
from TMP_IMPORT_IMEI_PYTHON tmp,
service s,
device d
where tmp.msisdn=S.Phone_Number
and s.id=D.Assigned_To_Service_Id
and d.description=tmp.model;
BEGIN
FOR I IN C1
LOOP
UPDATE device
SET    device_identifier = I.imei
WHERE  id=I.id;
rows_updated := rows_updated + 1; -- added
END LOOP;
dbms_output.put_line('Number of rows updated: ' || rows_updated); --added
END;
'''

and added this part (from the docs) to my code and got what i needed.

chunk_size = 100
    lines_var = cursor.arrayvar(str, chunk_size)
    num_lines_var = cursor.var(int)
    num_lines_var.setvalue(0, chunk_size)
    while True:
        cursor.callproc("dbms_output.get_lines", (lines_var, num_lines_var))
        num_lines = num_lines_var.getvalue()
        lines = lines_var.getvalue()[:num_lines]
        for line in lines:
            print(line or "")
            logger.info(line or "")
        if num_lines < chunk_size:
            break

Thank you both! You poinetd me into right direction.

BR,
Uros

[anthony-tuininga]

Glad you found a solution. There is a simpler one, though! You can do something like this:

update_query = '''
DECLARE
rows_updated NUMBER := 0; --added
CURSOR C1 IS select d.id, S.Phone_Number, tmp.imei
from TMP_IMPORT_IMEI_PYTHON tmp,
service s,
device d
where tmp.msisdn=S.Phone_Number
and s.id=D.Assigned_To_Service_Id
and d.description=tmp.model;
BEGIN
FOR I IN C1
LOOP
UPDATE device
SET    device_identifier = I.imei
WHERE  id=I.id;
rows_updated := rows_updated + 1; -- added
END LOOP;
:rows_updated := rows_updated;
END;
'''
rows_updated_var = cursor.var(int)
cursor.execute(update_query, [rows_updated_var])
print("rows updated:", rows_updated_var.getvalue())

[urosdigital]

Thumbs up! :) Thanks Anthony