Hi Experts,
Please let me know how to do the following DB calls in either OSB or BPEL 12c.
If EmpId exists:
Select DISTICNT NAME from dummy where EmployeeId = '$EmpId'
If EmpId and DOB exists:
Select DISTICNT NAME from dummy where EmployeeId = '$EmpId' and DateOfBirth = '$DOB'
If SearchDate exists:
Select DISTICNT NAME from dummy where EmployeeId = '$EmpId' and SearchDate = '$SearchDate'
Else:
Select DISTICNT NAME from dummy where LastName = '$LastName'
Basically the query is dynamic. The real PROD query is more complex and it is nested in some if else scenarios.
I am thinking of two approaches:
1. Pass args from BPEL / OSB process and let PL/SQL pkg to do the If-Else logic and return the result set back to the process. ~ Feel like, this is the best approach. But, DB is owned by some other team. They will not like this approach.
2. Use multiple DB Adapters for every if -else scenario and call partner link dynamically. ~ Not sure, this is the best one.
Please let me know if you there are any other best approaches.
Thanks