Dynamic prefix of schema name in PL/SQL objects
I have a requirement where schema name would be passed as a parameter to the procedure and I am supposed to write some DML statements on some of the fixed tables. The table names are common among any schemas, but then only the schema names change.
This weird requirment is because, here they do not maintain same names for schemas on DEV,QA,UAT and PROD databases, and I can not change that. My thoughts are that it can be acheived only using Dynamic SQL. I want to know whether any alternate ways exist since this is going to be the case on various packages and procedures. For example I have a package with around 80 procedures in it which have couple of DML statements in it. Now they want to pass the schema name to main procedure and want the 80 procedures to perform the DML on the schema name passed :)
So, should I change all the 80 procedures and make all the DML in it to Dynamic SQL (I am afraid I might end up doing that)? Please not that I can not change all the environments to same schema names (Not in my hands)
Please suggest.