This type was created in SCHEMA1, and I granted EXECUTE on it to SCHEMA2. I also created a private synonym for it in SCHEMA2.
CREATE OR REPLACE TYPE CFB_CATEGORY_TYPE_REC AS OBJECT ( category_type_id NUMBER(10), create_by VARCHAR2(30 CHAR), create_datetime DATE, last_upd_by VARCHAR2(30 CHAR), last_upd_datetime DATE, category_type_name VARCHAR2(50 CHAR), valid_from DATE, valid_to DATE, order_by NUMBER(2), category_title VARCHAR2(30), sub_category_title VARCHAR2(30), CONSTRUCTOR FUNCTION CFB_CATEGORY_TYPE_REC RETURN SELF AS RESULT ) CREATE OR REPLACE TYPE BODY CFB_CATEGORY_TYPE_REC AS CONSTRUCTOR FUNCTION CFB_CATEGORY_TYPE_REC RETURN SELF AS RESULT AS BEGIN RETURN; END; END;
This works perfectly well when called from a tool like SQL Plus.
DECLARE v_rec cfb_category_type_rec; BEGIN v_rec := cfb_category_type_rec(); end;
To those not familiar with Apex, it connects to the database using the schema APEX_PUBLIC_USER and, using some type of database privileges, it runs the code as SCHEMA2.
ORA-04061: existing state of has been invalidated ORA-04061: existing state of stored procedure "SCHEMA1.CFB_CATEGORY_TYPE_REC" has been invalidated ORA-04065: not executed, altered or dropped stored procedure "SCHEMA1.CFB_CATEGORY_TYPE_REC" ORA-06508: PL/SQL: could not find program unit being called: "SCHEMA1.CFB_CATEGORY_TYPE_REC"