0

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic variables, etc.) and are later JOINed to produce customer level table with all extracted metrics.

As examples, toy datasets for each table can be created with the following code:

CREATE TABLE customerData 
(msisdn number(10),
  yrmonth varchar(20),
  crdt number(10));

insert into customerData (msisdn, yrmonth, crdt)
values (05512315, '201401', 200);

insert into customerData (msisdn, yrmonth, crdt)
values (05516347, '201402', 100);

insert into customerData (msisdn, yrmonth, crdt)
values (05567892, '201401', 200);

insert into customerData (msisdn, yrmonth, crdt)
values (05571236, '201401', 300);

insert into customerData (msisdn, yrmonth, crdt)
values (05551234, '201401', 200);


CREATE TABLE redemptionData
(txnID number(4),
 txnDate Date,
 msisdn number(10),
 partner varchar2(30),
 redempAmount number(10));

insert into redemptionData (txnID, txnDate, msisdn, partner, redempAmount)
values (1212, To_Date('11-nov-2014'), 05512315, 'Google', 250);

insert into redemptionData (txnID, txnDate, msisdn, partner, redempAmount)
values (1141, To_Date('12-nov-2014'), 05516347, 'Apple', 200);

insert into redemptionData (txnID, txnDate, msisdn, partner, redempAmount)
values (1542, To_Date('13-nov-2014'), 05567892, 'Microsoft', 150);


CREATE TABLE customerRevenue
(msisdn number(10),
Revenue number(10)
);

insert into customerRevenue (msisdn, Revenue)
values (05567892, 1200);

insert into customerRevenue (msisdn, Revenue)
values (05571236, 200);

What is expected from this automation task

All tables being produced previously be produced automatically for each month.

For example, if we produced a table redemptionData_Jan2015, it is expected that a new table will be automatically produced for Feb 2015 too, by querying and applying filter conditions on the redemptionData base table.


In view of this requirement of creating monthly tables for each metric and then JOINING them, I figured that the only way to accomplish this task is to use Dynamic SQL.

My question is: Would you agree that NDS is the way to go here? If not, what other options would you suggest that I consider?

Any comments + suggestions are welcome. I am trying to broaden my understanding of PL/SQL interactively, after spending some time in lone reading a couple of relevant books. If it helps, I am using Toad for automation.

  • What is "NDS"? One general comment: It is a very bad design to create a different table for each month. Such scenario you should typically solve with partitionized tables. In case you cannot change this poor design you have to use Dynamic SQL. – Wernfried Domscheit Feb 22 '15 at 19:25
  • @Wernfried, thanks for the response. NDS is Native Dynamic SQL - I will clarify this in the OP too. May I ask you to elaborate why you think the design is bad (creating a different table for each month)? – info_seekeR Feb 22 '15 at 21:02
  • Each table has the same structure, so it should be all in one table as well. Otherwise you will have to do a lot of dynamic stuff where it wouldn't be required actually. – Wernfried Domscheit Feb 22 '15 at 21:25
  • @Wernfried I did some basic reading (as of now) about partitioned tables, and I think you are certainly correct. However, the requirement I have been presented with is for tables to be created monthly, unfortunately... I suppose in this scenario, I can't do much except use Dynamic SQL, right? – info_seekeR Feb 24 '15 at 9:08
  • I think I may need to rephrase the title and/or the question for clarity... – info_seekeR Feb 24 '15 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.