Search code examples
oracleplsqlplsqldeveloper

Make inserts in the final table by time and by quantities in PL/SQL


Hello
I'm developing the logic to an application in PL/SQL and I ran into a problem that I can't solve. I have a stage that will be fed frequently through another application. I need to insert this stage data into a final table, however, there are some rules:

  • Rule 1 ->I need to send all records (up to a limit of 200 thousand) that have this stage within a time window, that is, if between 2PM and 3PM this stage has 150 thousand records, then at 3:01PM an insert of the data will be done in the final table in Oracle;
  • Rule 2 -> If within this time window the stage adds 200 thousand records, even if the time window has not yet ended, we will also insert in the final table of these 200 thousand records;
  • Rule 3 -> It may happen that the stage receives 300 thousand records at once, for example. In this case, rule 2 applies. 200 thousand records will be inserted in the final table and the other 100 thousand records will remain for the next time window, from 3PM to 4PM, in this example.

    How can I do this logic in PL/SQL?
    Thanks
Solution

  • Well, I know this is gonna be argued. A lot. But first of all, that is not a logic suitable to be implemented in PL/SQL. PL/SQL should be left fordatabase specific actions, not to implement the logic of the application. There is few cases in which I would recomend any kind of business logic to be implemented in PL/SQL. Distributing business logic across different layers is always trouble.

    If you decide to go for the original language in which the application is (which I guess is not PL/SQL) create another question. If you go for PL/SQL that's the only way I can think of:

    1. Create an internediate table in which keeping the records. This way the intermediate table will act as a cache, thread safe and transactional, in which easily manage the 3 business rulea that you posted.

    2. Forneach request tjat you receive, query the intermadate table, donthe counting taking into acount table and request, amd act accordingly.

    May be someone with better Oracle knowledge can think of something better. This is all I can think of, that's how I would do it. If someone gives a better PL/SQL solution I will delete this answer.