This discussion is archived
1 Reply Latest reply: May 15, 2011 2:11 PM by aazayka RSS

ORA-04061 error with custom object type constructor

Luis Cabral Pro
Currently Being Moderated
Hi there,

I have having a strange problem with object types. Please consider the type below:
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 type was created in SCHEMA1, and I granted EXECUTE on it to SCHEMA2. I also created a private synonym for it in SCHEMA2.

This object type has a simple no-parameters constructor so that I am able to instantiate an object of that type simply doing this (in SCHEMA2):
DECLARE
  v_rec cfb_category_type_rec;
BEGIN
  v_rec := cfb_category_type_rec();
end;
This works perfectly well when called from a tool like SQL Plus.

The problem is that this code is to be used with Oracle Apex. When this simple code is run from Apex, I get this error:
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"
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.

The problem seems to be related to the custom constructor. If I change the code to use the default constructor, it works fine!

Any clues?

Application Express 4.0.2.00.06
Oracle EE 11.1.0.7.0

Thanks
Luis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points