Results 1 to 9 of 9

Thread: row count

  1. #1
    Join Date
    Mar 2005
    Posts
    8

    Unanswered: row count

    i need to determine the number of rows that have been updated. The following PL/SQL is inside a package

    UPDATE (SELECT bu.archive_flag
    FROM billing_update_items bu, txs_billings_dets tb
    WHERE bu.service_id = p_service
    and bu.class_id = p_class
    and bu.update_type = 0
    and bu.billing_det_id = tb.id
    and tb.txs_date >= p_start_date
    and tb.txs_date <= p_end_date
    and bu.archive_flag <> p_flag)
    SET archive_flag = p_flag;
    COMMIT;

    p_count := SQL%ROWCOUNT;

    when the sql is executed, SQL%ROWCOUNT always returns 0. I've run the same update query outside the package and the correct number of rows get updated (4 in my particular test case)

    Can anyone shed some light on this ?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    try this ...
    before the declare of the plsql

    set serveroutput on

    pl/sql code ....

    at bottom on pl/sql

    dbms_output.put_line(p_count);

    end;
    /

    HTH
    Gregg

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    It is only a matter of when to commit :
    Code:
    rbaraer@Ora10g> create table test(id number);
    
    Table created.
    
    rbaraer@Ora10g> insert into test(id) values (1);
    
    1 row created.
    
    rbaraer@Ora10g> commit;
    
    Commit complete.
    
    rbaraer@Ora10g> begin
    update test set id = 2;
    dbms_output.put_line('');
    dbms_output.put_line(SQL%ROWCOUNT||' row(s) updated');
    commit;
    end;
    /  2    3    4    5    6    7
    
    1 row(s) updated
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g> begin
    update test set id = 2;
    commit;
    dbms_output.put_line('');
    dbms_output.put_line(SQL%ROWCOUNT||' row(s) updated');
    end;
    /
      2    3    4    5    6    7
    0 row(s) updated
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    HTH & Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Good point RBARAER... I missed that one little word ... COMMIT

  5. #5
    Join Date
    Mar 2005
    Posts
    8

    rowcount

    if I do this

    UPDATE (SELECT bu.archive_flag
    FROM billing_update_items bu, txs_billings_dets tb
    WHERE bu.service_id = p_service
    and bu.class_id = p_class
    and bu.update_type = 0
    and bu.billing_det_id = tb.id
    and tb.txs_date >= p_start_date
    and tb.txs_date <= p_end_date
    and bu.archive_flag <> p_flag)
    SET archive_flag = p_flag;
    p_count := SQL%ROWCOUNT;
    COMMIT;

    I still get 0 in p_count

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Code:
    rbaraer@Ora10g> drop table test;
    
    Table dropped.
    
    rbaraer@Ora10g>  create table test(id number);
    
    Table created.
    
    rbaraer@Ora10g> insert into test(id) values (1);
    
    1 row created.
    
    rbaraer@Ora10g> commit;
    
    Commit complete.
    
    rbaraer@Ora10g> begin
    update test set id = 2;
    dbms_output.put_line('');
    dbms_output.put_line(SQL%ROWCOUNT||' row(s) updated');
    commit;
    end;
    /  2    3    4    5    6    7
    
    1 row(s) updated
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g> create or replace package testpackage as
    
    procedure testproc;
    
    end testpackage;
    /  2    3    4    5    6
    
    Package created.
    
    rbaraer@Ora10g> create or replace package body testpackage as
    
    procedure testproc is
    
    begin
    
    update test set id = 2;
    dbms_output.put_line('');
    dbms_output.put_line(SQL%ROWCOUNT||' row(s) updated');
    commit;
    
    end testproc;
    
    end testpackage;
    /  2    3    4    5    6    7    8    9   10   11   12   13   14   15
    
    Package body created.
    
    rbaraer@Ora10g> execute testpackage.testproc;
    
    1 row(s) updated
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    Being in a procedure within a package or not does not matter for SQL%ROWCOUNT, as shown in the example above. When you say that SQL%ROWCOUNT returns 0, are you sure that some rows should be updated ?

    Could you show the two different behaviours you are speaking about when placing SQL%ROWCOUNT inside a procedure or not ?

    Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Mar 2005
    Posts
    8

    rowcount

    executing this in a SQL window rteurns 1 result

    SELECT *
    FROM billing_update_items bu, dummy_billings_dets db
    WHERE bu.service_id = 142
    AND bu.class_id = 174
    AND bu.update_type = 1
    AND bu.billing_det_id = db.id
    AND db.txs_date >= to_date('21 MAR 2004')
    AND db.txs_date <= to_date('21 MAR 2004')
    AND bu.archive_flag <> 'Y'

    doing this inside a package updates 1 row

    UPDATE (SELECT bu.archive_flag
    FROM billing_update_items bu, dummy_billings_dets db
    WHERE bu.service_id = p_service
    AND bu.class_id = p_class
    AND bu.update_type = 1
    AND bu.billing_det_id = db.id
    AND db.txs_date >= p_start_date
    AND db.txs_date <= p_end_date
    AND bu.archive_flag <> p_flag)
    SET archive_flag = p_flag;

    changing the parameters and running it as a straightforward select (just changed the date to 01 JUN 2003 which returned 1 row, so 1 row should be updated which it is because running the same set of parameters a second time reurns no results) and then tracing through the code in the package still results in the SQL%ROWCOUNT being 0

  8. #8
    Join Date
    Mar 2005
    Posts
    8

    Rowcount

    here, inside the package, rowcount is 0

    UPDATE (SELECT bu.archive_flag
    FROM billing_update_items bu, dummy_billings_dets db
    WHERE bu.service_id = p_service
    AND bu.class_id = p_class
    AND bu.update_type = 1
    AND bu.billing_det_id = db.id
    AND db.txs_date >= p_start_date
    AND db.txs_date <= p_end_date
    AND bu.archive_flag <> p_flag)
    SET archive_flag = p_flag;
    p_count := SQL%ROWCOUNT;
    COMMIT;

    **The paramaters are exactly the same as the values below, I stopped before the commit so I could test in a normal SQL window

    running this in a normal SQL window reports 1 row updated

    UPDATE (SELECT bu.archive_flag
    FROM billing_update_items bu, dummy_billings_dets db
    WHERE bu.service_id = 142
    AND bu.class_id = 174
    AND bu.update_type = 1
    AND bu.billing_det_id = db.id
    AND db.txs_date >= TO_DATE('01 JUN 2003')
    AND db.txs_date <= TO_DATE('01 JUN 2003')
    AND bu.archive_flag <> 'Y')
    SET archive_flag = 'Y'

    I really cant see whats going on here, the rows are definately being updated, but SQL%ROWCOUNT reports 0

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    That's strange. Could you please post the whole code of your procedure, and execution samples through sqlplus as I did in my previous posts ? That should be of some help.

    Thanks & Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •