1 Reply Latest reply: May 15, 2011 4:11 PM by aazayka RSS

    ORA-04061 error with custom object type constructor

    Luis Cabral
      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