I have a merge statement (see below) that I have been running manually that I want to put into a procedure.
When I try to create the procedure I get the error: ORA:00942 - table or view does not exist. Again, this merge
statement runs fine when run manually. I'd appreciate any guidance that an
Here's the ddl for creating the procedure:
create or replace procedure rpa_admin.load_park_info
s := 'start';
MERGE INTO RPA_ADMIN.SB_T_PARK_INFO a
USING PA_DATA_DUMP_02032013.T_PARK_INFO b
ON (a.park_id = b.park_id)
a.PARK_NAME = b.PARK_NAME,
a.CONTACT_FIRST_NAME = b.CONTACT_FIRST_NAME,
a.CONTACT_MIDDLE_INI = b.CONTACT_MIDDLE_INI,
a.CONTACT_LAST_NAME = b.CONTACT_LAST_NAME,
a.CONTACT_SUFFIX = b.CONTACT_SUFFIX,
a.ADDR_1 = b.ADDR_1,
a.ADDR_2 = b.ADDR_2,
a.ADDR_3 = b.ADDR_3,
a.CITY = b.CITY,
a.STATE_CODE = b.STATE_CODE,
a.ZIP = b.ZIP,
a.ZIP_EXTENSION = b.ZIP_EXTENSION,
a.PHONE = b.PHONE,
a.FAX = b.FAX,
a.EMAIL = b.EMAIL,
a.PARK_ADNL_MESSAGE = b.PARK_ADNL_MESSAGE,
a.ACTIVE_FLAG = b.ACTIVE_FLAG,
a.PARK_DIRECTIONS = b.PARK_DIRECTIONS,
a.MODIFIED_BY = b.MODIFIED_BY,
a.MODIFIED_DATE = b.MODIFIED_DATE,
a.CREATED_DATE = b.CREATED_DATE,
a.CREATED_BY = b.CREATED_BY,
a.ENTITY_ID = b.ENTITY_ID,
a.REGION_ID = b.REGION_ID
WHEN NOT MATCHED
WHERE b.PARK_ID IS NOT NULL;
I bet it has nothing to do with MERGE. Stored procedure owner is granted privileges to source and/or target tables via role, right? Stored procedure with definer rights (which is the default) doesn't honor roles and therefore fails. You might need to either grant privileges directly or create stored procedure with invoker rights, depending on your situation.
I thought that it might be a privileges issue also. So I tried creating the procedure as 'sys' and got the same results.
And again, I can successfully run the merge statement manually as the procedure owner (rpa_admin).
And again, user rpa_admin is granted select on table PA_DATA_DUMP_02032013.T_PARK_INF via role. It doesn't matter who are you logged in as when creating stored procedure. What matters is who owns it. And procedure is owned by rpa_admin, since you are creating stored procedure rpa_admin.load_park_info.
Yes, user rpa_admin is granted select on PA_DATA_DUMP_02032013.T_PARK_INFO.
I understand that. Otherwise rpa_admin wouldn't be able to run MERGE successfuly as you mentioned. Question is is it granted select on PA_DATA_DUMP_02032013.T_PARK_INFO via role or directly. I bet - via role. That's why standalone MERGE succeeds but MERGE within stored procedure fails.
You might need to either grant privileges directly or create stored procedure with invoker rights, depending on your situation.
Actually I lied. Since SP will be owned by rpa_admin, rpa_admin must be granted select on PA_DATA_DUMP_02032013.T_PARK_INFO directly regardless if stored procedure is definer or invoker rights. It is one of those strange cases - user who has execute privilege on invoker rights stored procedure doesn't need privileges to be granted directly but user who owns stored procedure does, otherwise it will not compile.