Hi folks,
We have around 50 schemas having the same database tables etc..
We need to generate a report on each of the schemas. (Ex: get total number of users).
We can not grant access from one schema to the other.
In order to resolve the problem:
- I created a package with a stored procedure in each of the schemas.
- I granted package access from all these schemas to one of the schema (let's call it mainSchema).
- In this mainschema, I created a synonym :
create or replace synonym mainSchema.EXT_I_REPORTS for mainSchema.I_REPORTS;
- In a new stored procedure getUsertotalAllSchemas, I invoke a stored procedure (getUserTotal) in the package for each of the schemas dynamically :
replaceSynonym := 'create or replace synonym mainSchema.EXT_I_REPORTS for "'|| v_schema||'".I_REPORTS ';
DBMS_OUTPUT.PUT_LINE(replaceSynonym);
EXECUTE IMMEDIATE replaceSynonym;
EXT_I_REPORTS.getUserTotal();
- Compilation works fine-
- While execution, it gives this error:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "mainSchema.EXT_I_REPORTS"
ORA-06508: PL/SQL: could not find program unit being called: "mainSchema.EXT_I_REPORTS"
ORA-06512: at "mainSchema.getUsertotalAllSchemas", line 38
ORA-06512: at line 1
04068. 00000 - "existing state of packages%s%s%s has been discarded"
*Cause: One of errors 4060 - 4067 when attempt to execute a stored
procedure.
*Action: Try again after proper re-initialization of any application's
state.
There is some problem with the create/replace synonym
I am looking for a different approach or resolution to the issue.
Any help is highly appreciated.