Forum Stats

  • 3,770,233 Users
  • 2,253,082 Discussions
  • 7,875,370 Comments

Discussions

save multiple column values through procedure that accepts only old and new values as parameters

Mr.Peabody
Mr.Peabody Member Posts: 11 Red Ribbon
edited Apr 19, 2021 9:55AM in SQL & PL/SQL

Currently, I am having a procedure which takes old_value and new_value as parameter and saves them to database if the column value is changed.

Now I have a scenario where I need to save multiple column values at the same time but procedure don't allow me to do so. So, I am checking for every column if they are changed and calling the procedure that number of times for different columns.

CASE: one or more than one columns can be changed at a time.

Dummy Procedure:

call_proc(p_id, p_old_value, p_new_value, p_date);

Pseudocode:

IF value_1 is changed THEN
   call_proc();
END IF;
IF value_2 is changed THEN
   call_proc();
END IF;

But, I want to achieve something so that I don't need to call procedure separately everytime.

Any Suggestions???

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy

    It's not clear what the procedure does. Nor is it clear in which context it is called.

    When you speak about new value and old value, that makes one think of some trigger before/after update on some table or tables. So, if you know which tables you are willing to track, write triggers for those tables and forget about that procedure.

    Or, if you are willing to use one procedure, then just how many parameters would it get.

    Maybe change it so as to receive as in parameter a pl/sql table.

    create or replace type typ_colval as object (

     column_name varchar2(30)

     ,old_value varchar2(4000)

     ,new_value varchar2(4000)

    )

    ;

    create or replace type tab_colval as table of typ_colval

    ;

    create or replace procedure log_x (p_colvals tab_colval) is

    begin

    if p_colvals.count<=0 then

    return; --- call with no elements in table

    end if;

    for i in p_colvals.first..p_colvals.last loop

    --- do whatever has to be done with those column values

    .....

    end loop;

    end;

  • Mr.Peabody
    Mr.Peabody Member Posts: 11 Red Ribbon
    edited Apr 19, 2021 10:29AM