This discussion is archived
5 Replies Latest reply: Sep 29, 2013 3:24 AM by chiranjeevipola RSS

Need help in pl/sql stored procedure logic to replace set of records.

chiranjeevipola Newbie
Currently Being Moderated

Hello,

 

I have a requirement like this.

 

I have a table namely CUSTOMER_CHANNELS with columns customer_id (varchar2),channel (varchar2) and timestamp(Timestamp). This table stores the channels that a customer is subscribed to.

For example, if customer '1' watches channels 'history' and 'cnn' the table stores this mapping like 1-'history' and 1-'cnn'.

 

If the customer wants to modify his list, for example, wants to add a new channel 'fox' and remove 'cnn', the request comes to application with only channel list he is supposed to watch.

In this case, upstream order management system sends request with channels 'history' and 'fox'. i.e the oracle stored procedure needs to replace all existing channels in table with actual channels the customer is supposed to watch.

 

The procedure takes 2 arguments. 1> customer_id and 2> channel. After customer's request for 2 channels 'history' and 'fox' , this procedure has to be called 2 times each time for one channel.

 

create or replace

PROCEDURE MASTER_PROC

  IS

  V_CUSTOMER_ID VARCHAR2(10) := 1;

  V_channel VARCHAR2(10) := 'history' ;

  --v_sqlexecption EXCEPTION;

 

  BEGIN

        FOR I IN 1..3 LOOP

        DBMS_OUTPUT.PUT_line('sid '|| V_SID);

        MODIFY_CHANNELS(V_CUSTOMER_ID, V_CHANNEL);

        end loop;   

End MASTER_PROC;

 

called procedure.

 

create or replace

PROCEDURE MODIFY_CHANNELS

(

V_CUSTOMER_ID VARCHAR2,

v_channel VARCHAR2

)

  is

 

 

  V_CHANNEL_COUNT NUMBER;

  --v_sqlexecption EXCEPTION;

 

  Begin

       

       -- logic should come here to update timestamp of record if channel already exist, insert record if channel is not here and at the end remove the channels which are there in table for customer_id but not coming in request(customer wanted to unsubscribe).

           

End MODIFY_CHANNELS;

 

 

Thanks in advance.

  • 1. Re: Need help in pl/sql stored procedure logic to replace set of records.
    rp0428 Guru
    Currently Being Moderated
    I have a requirement like this.

     

    I have a table namely CUSTOMER_CHANNELS with columns customer_id (varchar2),channel (varchar2) and timestamp(Timestamp). This table stores the channels that a customer is subscribed to.

    For example, if customer '1' watches channels 'history' and 'cnn' the table stores this mapping like 1-'history' and 1-'cnn'.

     

    In this case, upstream order management system sends request with channels 'history' and 'fox'. i.e the oracle stored procedure needs to replace all existing channels in table with actual channels the customer is supposed to watch.

     

    The procedure takes 2 arguments. 1> customer_id and 2> channel. After customer's request for 2 channels 'history' and 'fox' , this procedure has to be called 2 times each time for one channel.

     

     

    That code implementation does NOT implement the requirements you provided. You said you need to:

    1. delete all exising channels

    2. insert new list containing the new channels

     

    If the customer wants to modify his list, for example, wants to add a new channel 'fox' and remove 'cnn', the request comes to application with only channel list he is supposed to watch.

    You don't show any such request containing multiple channels. Your code just adds ONE channel at a time but it doesn't account for the fact that channels may need to be deleted.

     

    You need code that contains ALL channels that the user is allowed to have.

  • 2. Re: Need help in pl/sql stored procedure logic to replace set of records.
    Etbin Guru
    Currently Being Moderated

    create table customer_channels

    (customer_id varchar2(15),

    channel varchar2(15),

    time_stamp timestamp

    )

     

    insert into customer_channels

    select '1' customer_id,'history' channel,systimestamp time_stamp from dual union all

    select '1','cnn',systimestamp from dual

     

    select customer_id,channel,time_stamp from customer_channels

     

    CUSTOMER_IDCHANNELTIME_STAMP
    1history28-SEP-13 07.37.22.505124 PM
    1cnn28-SEP-13 07.37.22.505124 PM

     

    submitting bind variables

     

    :customer_id := '1'

    :channels := 'history~fox'

     

    delete from customer_channels

    where (customer_id,channel) not in (select customer_id,regexp_substr(channels,'[^~]+',1,level) channel

                                           from (select :customer_id customer_id,:channels channels

                                                   from dual

                                                )

                                         connect by level <= length(regexp_replace(channels,'[^~]*')) + 1

                                        )

     

    select customer_id,channel,time_stamp from customer_channels


     

    CUSTOMER_IDCHANNELTIME_STAMP
    1history28-SEP-13 07.37.22.505124 PM


    merge into customer_channels cc

    using (select customer_id,regexp_substr(channels,'[^~]+',1,level) channel,systimestamp time_stamp

             from (select :customer_id customer_id,:channels channels

                     from dual

                  )

           connect by level <= length(regexp_replace(channels,'[^~]*')) + 1

          ) new

       on (cc.customer_id = new.customer_id

      and  cc.channel = new.channel

          )

    when matched

    then update

             set cc.time_stamp = new.time_stamp

    when not matched

    then insert values (new.customer_id,new.channel,new.time_stamp)

     

    select customer_id,channel,time_stamp from customer_channels


    CUSTOMER_IDCHANNELTIME_STAMP
    1fox28-SEP-13 10.24.39.314872 PM
    1history28-SEP-13 10.24.39.314872 PM


    delete and merge might be incorporated into a single procedure taking customer_id and the new list of channels as parameters.


    Regards


    Etbin

  • 3. Re: Need help in pl/sql stored procedure logic to replace set of records.
    chiranjeevipola Newbie
    Currently Being Moderated

    rp0428,

     

    Thanks for your response. you got it right. That is where I am struggling to write a logic. When the new channels are coming to procedure one by one, I can take care of inserting channel if it is new and update timestamp if channel is already there.

     

    The issue is with deleting those channels which are there in table but not coming from request.

  • 4. Re: Need help in pl/sql stored procedure logic to replace set of records.
    chiranjeevipola Newbie
    Currently Being Moderated

    Etbin,


    Thanks a lot for your time.


    I couldn't get  below one thing from your solution. 


    submitting bind variables

     

    :customer_id := '1'

    :channels := 'history~fox'


    The stored procedure I am going to write will take 2 arguments for each time it is called.

    1. customer_id and another channel ( one channel at a time).

    For ex:

    first call: 1, history

    second call: 1, fox.

     

    then, how can we bind variable channels as 'history~fox'. I first get 'history' and then 'channel' to stored procedure.

     

    Thank you very much.



  • 5. Re: Need help in pl/sql stored procedure logic to replace set of records.
    Etbin Guru
    Currently Being Moderated

    I proposed a SQL solution much easier to write and much faster too (no loops used)

    If the user channels are not that many you could use a string of new channels to be submitted to the procedure which would get things settled.

    procedure new_channels(p_customer_id,p_channel_list)

    calling it as: new_channels('1','channel1~channel2~channel3~channel4~channel5')

    I used ~ as separator having troubles in APEX while using comma

    as I mentioned in my previous post (I tried to stick to your request) the procedure might look something like


    create or replace procedure new_channels(p_customer_id varchar2,p_channel_list varchar2) is

    begin

    delete from customer_channels

    where customer_id = p_customer_id

       and (customer_id,channel) not in (select customer_id,regexp_substr(channels,'[^~]+',1,level) channel

                                           from (select p_customer_id customer_id,p_channel_list channels

                                                   from dual

                                                )

                                         connect by level <= length(regexp_replace(p_channel_list,'[^~]*')) + 1

                                        );

    insert into customer_channels

    select customer_id,channel,systimestamp

        from (select customer_id,regexp_substr(channels,'[^~]+',1,level) channel

                from (select p_customer_id customer_id,p_channel_list channels

                        from dual

                     )

              connect by level <= length(regexp_replace(p_channel_list,'[^~]*')) + 1

             )

        where (customer_id,channel) not in (select customer_id,channel

                                              from customer_channels

                                           );

    end;‚Äč


    This way you could preserve the time_stamp of channels with subscription confirmed.

     

    Regards

     

    Etbin


    alter table "CUSTOMER_CHANNELS" modify ("CHANNEL" VARCHAR2(30))


    select * from customer_channels


     

    CUSTOMER_IDCHANNELTIME_STAMP
    1fox28-SEP-13 10.24.39.314872 PM
    1history28-SEP-13 10.24.39.314872 PM


    begin

    new_channels('2','history~discovery~cnn~fox');

    end;

     

    select * from customer_channels


     

    CUSTOMER_IDCHANNELTIME_STAMP
    2cnn29-SEP-13 12.04.04.953305 PM
    2discovery29-SEP-13 12.04.04.953305 PM
    2fox29-SEP-13 12.04.04.953305 PM
    2history29-SEP-13 12.04.04.953305 PM
    1fox28-SEP-13 10.24.39.314872 PM
    1history28-SEP-13 10.24.39.314872 PM

     

    begin

    new_channels('2','history~discovery~BBC news~NBC news~fox');

    new_channels('1','history~Euro Sport~fox');

    new_channels('3','National Geographic');

    end;

     

    select * from customer_channels order by 1,3


    CUSTOMER_IDCHANNELTIME_STAMP
    1history28-SEP-13 10.24.39.314872 PM
    1fox28-SEP-13 10.24.39.314872 PM
    1Euro Sport29-SEP-13 12.20.53.670266 PM
    2history29-SEP-13 12.04.04.953305 PM
    2fox29-SEP-13 12.04.04.953305 PM
    2discovery29-SEP-13 12.04.04.953305 PM
    2BBC news29-SEP-13 12.20.53.669930 PM
    2NBC news29-SEP-13 12.20.53.669930 PM
    3National Geographic29-SEP-13 12.20.53.670433 PM

     

     



     

    Message was edited by: Etbin Did some tests

Legend

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