Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003

    Unanswered: Production Job Philosophy

    We're migrating our systems from a WANG system to Oracle, which gives us the added tool of set-level processing.

    On the WANG, updates were based upon programs which used row-level processing, and each record which was successfully updated would show up on report A, those that were unsuccessfully updated and/or errors would show up on report B, and a summary report based on the job would show up on report C. The addition of a record's information to a report would occur while that record was still held in the cursor.

    I know that we can continue to code this way in Oracle, but what is the approach other's take for production jobs which can perform this in one statement, without a cursor? Do you run 3 SQL statements, one for each report? Do you avoid extensive reporting like this?

    An example situation might be:

    update employees
    set salary = salary + (salary * .1)
    where salary < 4000
    Report A
    name old_salary new_salary
    Report B
    name >4000_unchanged_salary
    Report C
    # updated
    # not updated
    Just looking for advice in new territory

  2. #2
    Join Date
    Sep 2004
    London, UK
    Are you talking about auditing or results of batch jobs? For a batch process I would include a process ID in relevant records.
    The addition of a record's information to a report would occur while that record was still held in the cursor.
    I'm afraid I don't understand that part at all. How is a record "held" in a cursor?

  3. #3
    Join Date
    Dec 2003
    Sorry for the misunderstanding. The pseudocode would look like

    declare cursor as select * from employees
    for rec in cursor
       if rec.salary < 4000 then
            emp_name := rec.emp_name
            emp_sal := rec.emp_sal
            emp_sal_new := rec.emp_sal + (rec.emp_sal*.10)
            update employees set salary = salary + (salary*.10) where current of cursor;
            >send emp_name, emp_sal, emp_sal_new to a row in rptA table
            cnt_success := cnt_success + 1;
            emp_name := rec.emp_name
            emp_sal := rec.emp_sal
            >send emp_name, emp_sal, emp_sal_new to a row in rptB table
            cnt_not_updated := cnt_not_updated + 1;
       end if
            >send counts to rptC table
    end loop

    I don't see how you'd do the same with set-level processing, perhaps
    run report B
    select emp_name, salary
    from employees
    where salary > 4000;
    update data
    update employees
    set salary = salary + (salary * .1)
    where salary < 4000
    run report A
    select emp_name, salary, salary - (salary/10)
    from employees
    where (salary - (salary/10)) < 4000;
    report C
    the data for report B is hard to capture now, because you don't know whose salary was > 4000 and not changed, or is now >4000 and was changed, unless you introduce update flags or something in the table.
    I don't know if this is a poor example, but pretty much set-level processing here makes it harder to generate the same reports. So, I am wondering what other people are doing. Are all of these reports considered overkill? Do you use Oracle's flashback query mechanism to get a before shot of the table?

    I appreciate the help,

  4. #4
    Join Date
    Jun 2003
    West Palm Beach, FL


    Yes all those reports are considered overklill and depletion of our forest resources.
    The better approach is to have either an update flag or 'last_modified' timestamp.
    Also you could design a 'salary history' table where you record all salary changes and do your reporting based on that table (EMP->OUTER JOIN->SAL_HIST).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2003
    We do have a last_mod_id and a last_mod_datetime field. But using these in a query might not always yield accurate results, especially when the table can be updated from multiple sources around the same time.

    The benefit of the cursor is that the record you are processing is captured at the time it is modified. However, I come from a background of set-level processing, so I prefer to write my code that way, but I don't see:

    1) how to adapt set-level processing to perform the same function as a cursor
    2) what rationale I can use with our Analysts accustomed to their WANG output to realize that many of these reports are overkill.

    Has anyone had to convince someone of the second point during a conversion like this?


  6. #6
    Join Date
    Nov 2002
    Desk, slightly south of keyboard
    Hi Chuck,

    Qnd 'update log' - this should give you the idea. You can modify this to be more generic if necessary.

    create table emp 
       id   number(3),
       name varchar2(20),
       sal  number(8,2)
    insert into emp values (1,'Tom',1000);
    insert into emp values (2,'Dick',2000);
    insert into emp values (3,'Harry',3000);
    insert into emp values (4,'Joe',4000);
    create global temporary table emp_log 
       id number(3)
       ) on commit delete rows;
    CREATE OR REPLACE function emp_loginsert( inID ) return number IS
       insert into emp_log (id) values (inId);
       return 0;
    END emp_loginsert;
    update emp set sal=(sal*1.1)+Emp_LogInsert(id) where sal > 2000;
    select * from emp;    
    ID  NAME    SAL
    ==  ====    ===   
    1   Tom     1000
    2   Dick    2000
    3   Harry   3300
    4   Joe     4400
    select * from emp_log;
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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