Of course it should be possible to retrieve the DATE value from the Oracle table with one generic mapping. The only problem I see is that mapping variables are bound to their mapping and not to a session, so if more than one of those 100 workflows may be running at the same point in time, you're indeed in trouble, no question.
In this case a slightly different approach might be better:
You retrieve the DATE value from the Oracle table and write it to a parameter file. The name of this parameter file is specific per workflow (meaning the workflow name is part of the file name):
You can retrieve the workflow name as one standard parameter ($PMWorkflowName) to set the value of a workflow variable.
Now you hand over this workflow variable to the session which retrieves the DATE from Oracle and uses this mapping parameter(!) to create the correct parameter for the actual working session.
This means that you have to create one new session per workflow, but as mentioned above once you have created this session for one workflow you should be able to simply copy it into each affected workflow.
Second part of your question: a reusable session won't work because in reusable sessions you cannot use pre- or post-session variable assignments. Sad but true. So the sessions will have to be created "normally", meaning one session per main workflow.
But it might work to make life easier for yourself by creating such a session in one workflow and then copy it to all other workflows one by one. I haven't tried it myself, but I think it should work.
Creating additional session for every workflow is what we are trying to avoid. Is there any other way to resolve this problem
That depends on the logic of execution for those 100 workflows: do they always refer to the same DATE value?
What I mean is this: if those 100 workflows run one after the other AND they all use the same start date, then of course you don't need more than one extra session, namely for the very first workflow (to prepare the parameter file for all 100 workflows).
If, however, e.g. 10 out of the 100 workflows may need one DATE value, the next 20 workflows need a different DATE value, the following 30 workflows need yet another DATE value, and the final 40 workflows need a fourth different DATE value, then you're out of luck.
At least I don't see (at this moment) how that can be avoided.
As you said, I created only one mapping but a new session in all workflows. It is working fine now.
Thanks a lot ..!!