Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: dbms_application_info package

    Hai

    I want to point in time information about the execution using dbam_application_info package.
    One article was published by EXPERT ADVISE IN september/october 2002 ORACLE WORLD update all employee salaries .I want point in time information about all employee salaries
    whose deptno =20.I unable to get how many records processed how to do that .
    Thanks in advance
    mohan



    declare
    cursor c1 is select deptno,sal from emp;
    v_deptno emp.deptno%type;
    v_sal emp.sal%type;
    count_num pls_integer;
    start_time pls_integer;
    begin
    start_time:=dbms_utility.get_time;
    for c2_rec in c1 loop
    count_num:=count_num+1;

    v_sal:=c2_rec.sal;
    update emp set sal=v_sal where deptno=c2_rec.deptno;
    if mod(count_num,14)=0 then
    dbms_application_info.set_module('Records processed: ' ||count_num, 'Elapsed: ' ||(dbms_utility.get_time- start_time)/100 || 'sec ');
    end if;
    end loop;
    commit;
    dbms_application_info.set_module('Records processed : ' || count_num, 'Elapsed : ' ||(dbms_utility.get_time - start_time)/100 ||'sec');
    end;



    SQL> SELECT SID,ACTION,MODULE FROM V$SESSION WHERE USERNAME='SYSTEM';


    SID ACTION
    ---------- -----------------------------
    MODULE
    ----------------------------------------
    12 Elapsed : .09sec
    Records processed :

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: dbms_application_info package

    You need to initialise count_num:

    count_num pls_integer := 0;

    Otherwise it will remain forever NULL, since count_num:=count_num+1; is same as count_num := NULL+1;

Posting Permissions

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