5 Replies Latest reply: Sep 29, 2013 5:24 AM by chiranjeevipola RSS

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

    chiranjeevipola

      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
          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

            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

              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

                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

                  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