Hello Experts,
I am trying to compile a pl/sql package spec and body by login through user "system". Requirement is to create this package at user/schema "oha_sys".
Getting below error at compile time. Spec is getting compiled successfully but body compilation is throwing error.
I was getting same error when compiling by login to oha_sys before I granted permission to user "oha_sys" on all tables at another user "oha_dmt".
Package body is having tables from oha_dmt schema.
I have granted access to system user as well on all tables at oha_dmt but still getting error.
Kindly help.
Spec:
create or replace PACKAGE OHA_SYS.INS_STAT_DIM AS
PROCEDURE "AGE_GROUP_D_INS"("P_UNK_ID" IN NUMBER DEFAULT -1, "P_UNK_ALL_CODE" IN NVARCHAR2 DEFAULT 'ALL', "P_ALL_DESCR" IN NVARCHAR2 DEFAULT 'All', "P_UNK_CODE" IN NVARCHAR2 DEFAULT '-1', "P_UNK_DESCR" IN NVARCHAR2 DEFAULT 'Unknown');
END OHA_SYS.INS_STAT_DIM;
Body:
create or replace PACKAGE BODY OHA_SYS.INS_STAT_DIM AS
PROCEDURE "AGE_GROUP_D_INS"("P_UNK_ID" IN NUMBER DEFAULT -1, "P_UNK_ALL_CODE" IN NVARCHAR2 DEFAULT 'ALL', "P_ALL_DESCR" IN NVARCHAR2 DEFAULT 'All', "P_UNK_CODE" IN NVARCHAR2 DEFAULT '-1', "P_UNK_DESCR" IN NVARCHAR2 DEFAULT 'Unknown') IS
-- single row upsert of unknown dimension id
BEGIN
MERGE INTO OHA_DMT.age_group_d n
USING (SELECT p_unk_id row_wid
, p_unk_all_code agrp_all_code
, p_unk_code agrp_age
, p_unk_descr agrp_descr
, p_unk_descr agrp_descr_5
, p_unk_descr agrp_descr_10
, p_unk_descr agrp_descr_15
FROM dual) o
ON (o.row_wid = n.row_wid)
WHEN MATCHED THEN
UPDATE
SET n.agrp_all_code = o.agrp_all_code
, n.agrp_age = o.agrp_age
, n.agrp_descr = o.agrp_descr
, n.agrp_descr_5 = o.agrp_descr_5
, n.agrp_descr_10 = o.agrp_descr_10
, n.agrp_descr_15 = o.agrp_descr_15
WHEN NOT MATCHED THEN
INSERT
( n.row_wid
, n.agrp_all_code
, n.agrp_age
, n.agrp_descr
, n.agrp_descr_5
, n.agrp_descr_10
, n.agrp_descr_15
)
VALUES
( o.row_wid
, o.agrp_all_code
, o.agrp_age
, o.agrp_descr
, o.agrp_descr_5
, o.agrp_descr_10
, o.agrp_descr_15
)
;
commit;
END;
-- End of AGE_GROUP_D_INS;
END INS_STAT_DIM;
Basically I am not sure what permission should be granted to system user so that it can create package in oha_sys user (and this package is referring tables in another user oha_dmt) wihout compilation errors.
Thanks,