See the code below. I'm running the code with user x in schema y. The exact same code works if I run it by user x in schema x, but not with user x in schema y. I get the error
ORA-06512: table or view does not exist. Why would this be? I'm setting the schema explicitly to y. When running JUST the create statement with user x in schema y, it runs fine. And when running JUST the create statement with user x in schema x, it also runs fine.
There are two synonyms in schema y, called aaaaa and bbbbb. See y.aaaaa and y.bbbbb in the create statement. Synonym y.aaaaa selects data from table z.aaaaa in schema z. Synonym y.bbbbb selects data from table z.bbbbb in schema z. user x currently has SELECT permissions on these two tables in schema z. I renamed the synonyms (y.aaaaa and y.bbbbb) in schema y so they're easier to find.
The XSQL is the culprit, but not sure what permissions we need. When
XSQL(sql_code) is commented out, it doesn't throw the error.
This doesn't work:
alter session set current_schema=y;
DECLARE sql_code VARCHAR2(4000) := 'create table basis as '|| '(select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis '|| ' inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num '|| ' join bbbbb basiscode '|| ' on basis.modifier_cd = basiscode.c_basecode '|| ' and basiscode.c_fullname like ''\BASIS\%'') '; BEGIN DROPSQL('DROP TABLE basis'); XSQL(sql_code); END;
alter session set current_schema=y; create table basis as (select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num join bbbbb basiscode on basis.modifier_cd = basiscode.c_basecode '|| and basiscode.c_fullname like '\BASIS\%');
For reference purposes:
create or replace PROCEDURE XSQL(sqlstring VARCHAR2) AS BEGIN EXECUTE IMMEDIATE sqlstring; dbms_output.put_line(sqlstring); END XSQL;