This discussion is archived
10 Replies Latest reply: Sep 18, 2013 12:48 PM by Epic Fail RSS

Custom process to update a seq number

Haisa.M Newbie
Currently Being Moderated

Hello every body,

 

I have a custom update process for my page which works fine in SQL developer but when I move it to Apex, it does not work.

 

the code is:

 

declare

seq number;

begin

 

select MAX(REC_RET_SEQ_NUM)+1 Into seq from rta_rec_ret_cost_center;

UPDATE rta_rec_ret_cost_center

 

SET REC_RET_SEQ_NUM=seq

 

where

REC_RET_COST_CENTER=:P7_RET_COST_CENTER;

 

 

end;

 

Which has a condition on button create ( when button has pressed the process should run).

 

What I want to do is, when I select a ret_cos_center from the page and insert it to the table, then this process updates the cost center table ( which is another table) by the new sequence number. the process does not give me error but it does not do anything. I think the problem is I am not updating the same table in the page ( can be the problem!!). Please help me out on this. I appreciate your help.

  • 1. Re: Custom process to update a seq number
    Epic Fail Newbie
    Currently Being Moderated

    I'm having a hard time following what your wanting to do.  I only see one table in your example but you say "I think the problem is I am not updating the same table in the page". Some more details would help.

    Maybe:

    1) make sure there is a value in :P7_RET_COST_CENTER when this executes.

     

    2) look at using a sequence and trigger on your table.

  • 2. Re: Custom process to update a seq number
    Haisa.M Newbie
    Currently Being Moderated

    Sorry for the confusion. What I meant was my page is inserting and updating values of  record insert table and I have a custom update process which updates record cost center table when they insert to the record insert table. the process actually add a sequence number of the record cost center table. that is why i said maybe the problem is i am updating another table

  • 3. Re: Custom process to update a seq number
    Mike Kutz Expert
    Currently Being Moderated


    S T O P   N O W ! ! ! !

     

     

     

    Haisa.M wrote:

    declare

    seq number;

    begin

     

    select MAX(REC_RET_SEQ_NUM)+1 Into seq from rta_rec_ret_cost_center;

    UPDATE rta_rec_ret_cost_center

     

    SET REC_RET_SEQ_NUM=seq

     

    where

    REC_RET_COST_CENTER=:P7_RET_COST_CENTER;

     

     

    end;

     

     

    NEVER EVER CREATE SEQUENCE NUMBERS LIKE THIS !!!

     

    Use a SEQUENCE !!!

     

    Bad programmer!  Bad!

  • 4. Re: Custom process to update a seq number
    Epic Fail Newbie
    Currently Being Moderated

    Mike,

    I wasn't going to scold him like a child.  I just answered his question as I understand it. Tell me if this is a better solution.

     

    When I have to do something like this my technique is:

    table_a with a sequence and trigger that executes on insert when "sequance field" is null

    table_b with foreign key referencing table_a

     

    declare

      v_next_number number;

    begin

    if :REQUEST = 'CREATE' then

    select seq_name.nextval from dual;

     

    insert into table_a(sequence_field, data_needed)

    values( v_next_number, 'some data');

     

    insert into table_b(sequence_field, more_data_needed)

    values(v_next_number,'some more data');

    end if;

    end;


    Better?

  • 5. Re: Custom process to update a seq number
    Michael_Rife Newbie
    Currently Being Moderated

    For background, Oracle guarantees that sequences will be unique.  Doing a SELECT MAX()... may or may not result in unique values.  You could have to sessions connected to the database that issue the same SELECT statement at the same time getting the same results.  Now you will end up with duplicate values.

     

    Keep in mind that the sequence values will be unique, but not necessarily in order and may have gaps because of caching.

  • 6. Re: Custom process to update a seq number
    Haisa.M Newbie
    Currently Being Moderated

    I think I did not described it correctly here. That is not my sequence number. For each table I have built a sequence which does add the number when each row has been inserted and it is unique. This number is something else. Actually what I am doing is:

    I have a table which has cost center and the num 0 (table A) also I have another table which has cost center with title ( table B). Each table has its own unique sequence number.  when user selects a cost center from the table B and add title the  table B gets the last num of the selected cost center from table A ( which starts with 0) and add 1 to it  and inserts into table B like cost center- 1. when the user inserts this record I need the table A gets updated and for that specific cost center add the num 1 to table A. Now when the user selects the same cost center for the new title, the num will be 2 but if it is different cost center then it will be 1.

     

    Anyway, I created a trigger which works fine for the insert but I want to know when the user updates the cost center to another cost center in the table B then the used cost center should be decremented by 1 in table A. I can not get this right.

  • 7. Re: Custom process to update a seq number
    Epic Fail Newbie
    Currently Being Moderated

    Is this cost center number acting like a count of how many times the title has been used? If this is the case I think the entire column is not needed.  You can find that using using count() function either with group by or with an analytic fuction.  

  • 8. Re: Custom process to update a seq number
    Haisa.M Newbie
    Currently Being Moderated

    Yes it is like :

    table A

    Cost Center       Num

    test                    0

    test1                  0

    test2                  0

     

    and then when the table be inserts 'test' with some kind of title the num for cost center test will be 1. then if the user selects the 'test' again then the num will be 2. then if the user selects 'test1' the num for test1 will be 1. now I have the trigger for this and it works fine. now when the user for example inserts test in the table B and this table gets updated by 1 and then the user thinks he/she made a mistake and he/she needed to select test1 and updates the table b to test1, the number for test should go back to 0 but it stays 1.

  • 9. Re: Custom process to update a seq number
    Mike Kutz Expert
    Currently Being Moderated

    Haise,

    This is what I was afraid of..

    I understand what you are trying to do.  Proper implementation may be viewed as non-trivial for APEX developers but it is common practice for DB Developers.

     

    To help ensure that everybody is using the same terminology, please provide simple,shorten CREATE TABLE statements.

    This way, when we speak, we understand 'sequence for table' and 'sequence for title'

     

    If you implemented what I think you implemented, please stop right now.

    Ensuring that you do not get duplicates is a non-trivial task that becomes 1e100 more complicated when you try to implement it using a trigger.

     

    First thing you need to do is try to 'negotiate out' the 'sequence on TITLE based on cost center' number out of your software requirements.

    You need to try and see if 'simulating' the number is OK.

    eg

    select

      cost_center_id,

      title,

      row_number() over (partition by cost_center_id order by date_entered)

        as title_seq

    from TITLE;

     

    This is the Number One preferred method of "implementing" what you want.

     

    The main concern is if that 'cost center based sequence number in TITLE' is printed out somewhere... AND, that information will be used to reference back to data in the database.

    In that case, what you are trying to accomplish is correct but your implementation is horribly wrong.

     

    Let me know if you need to go this route.

    Again, create table statements will help us out so that all of us can 'speak' the same lingo.

     

    MK

  • 10. Re: Custom process to update a seq number
    Epic Fail Newbie
    Currently Being Moderated

    I would not count data like this.  It sounds like table_a is a look up table for table_b so I would do it with an analytical function.

     

    select  a.table_a_pk_num,

              a.title,

              count(*)

    from   table_a a,

             table_b b

    where a.table_a_pk_num = b.table_a_pk_num

    group by  a.table_a_pk_num,

                  a.title;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points