Run stored procedures in other schemas from stored procedure


I have a current process: a large procedure in one schema.
There is a plan to have A, B1, B2, B3 schemas (B-schemas are identical, could be B-n schemas) in Oracle 12c database.
Schemas B1, B2, B3 have synonyms to A.
Schema A stores all the connections information to other schemas.

Is it possible to run a stored procedure in A and call the stored procedure in other B-schemas (dynamically determine schemas to run and do it in parallel).

There is also .Net part and it is better to put this logic there and make calls to the B-schemas stored procedure in different threads?

Best Answer

You can call procedures in schema B from procedures in schema A. Depending on the version of Oracle, you may have to grant execute privileges on each procedure explicitly from B to A, using the "with grant" option, rather than through a role. Consult documentation for your specific version to verify what permissions are required.

In general I would also recommend putting orchestration logic into the back-end (database) rather than client-side code. Keep your back-end and front-end business logic separate.