The additional module will be used by no more than 20 people on an intranet.
"People" is a fuzzy term when you're talking about legal applications. Supporting 20 lawyers is a lot different than supporting 1 lawyer, 4 paralegals, and 15 staff.
For legal cases new business rules are more like "everything is an
exception"
And that's one reason why. Within case management, certain kinds of things are simply deterministic. Timing of responses, deadlines for filing, things like that. But the rules that govern were written by lawyers for lawyers. Companies that build case management software have lawyers on staff to read the rules for procedure (which change over time) and to verify that their software complies with the rules.
Some of them have a lot of lawyers.
Other kinds of things are governed by whimsy. For example, some case management packages let users "template" their tasks. Entering something like "Deposition" on the calendar might also calendar a series of earlier tasks that have to be done to prepare for the deposition. Under the pressure of multiple, incompatible deadlines, parts of a task chain get dropped or given short shrift.
Now, to (finally) get to your question . . .
Decision tables in the database are a viable solution. In fact, decision tables constitute a database whether they're stored in Oracle or in application code. There are obvious trade offs to storing decision tables in application code.
But decision tables in the database are probably not a viable solution for you. The problem you have, if I understand you correctly, is that you think you might need to simplify the architecture to decision tables in order to eliminate (or at least to greatly reduce) the use of Microsoft Workflow. With just two of you and a minimal budget, success will be difficult at best.
I can suggest two things.
- Can you economically produce a prototype of a replacement system
built on decision tables?
- Can you use better hardware on the existing system?
One way of return select data from PL/SQL is using Oracle table functions. Below is an example where the line SELECT * FROM table_a where test_a = par1; should be replaced by your select statement + change the table name.
Create table Table_a
(test_a varchar2(1))
/
insert into table_a
values('a')
/
create or replace package test_package as
TYPE col_table_1 is table of TABLE_A%ROWTYPE;
function test_plsql_table(par1 varchar2) return col_table_1
pipelined;
end;
/
create or replace package body test_package as
function test_plsql_table(par1 varchar2) return col_table_1
PIPELINED as
cursor temp_cur is
SELECT * FROM table_a where test_a = par1;
begin
for cur_rec in temp_cur loop
pipe row(cur_rec);
end loop;
end;
end;
/
SELECT * from TABLE( test_package.test_plsql_table('a'))
/
Best Answer
If the duplicates are being introduced by the inserts from Table A, then it seems a MERGE would be in order. A
MERGEcanINSERTdata when the record does not exist in Table B andUPDATEdata if the record already exists thus preventing the introduction of duplicates.FrustratedWithFormsDesign is correct that you probably should have a constraint preventing the introduction of duplicates. He is also correct in pointing out that this code would probably benefit from Bulk processing.
Phil is correct that if you only need to INSERT data, then a subquery pruning rows from the insert would be preferable.