TYPE does not exist

I've defined a function:

(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;

and object type

create or replace type string_agg_type as object
  2  (
  3     total varchar2(4000),
  4  
  5     static function

create or replace type string_agg_type as object
    (
       total varchar2(4000),
   
       static function
            ODCIAggregateInitialize(sctx IN OUT string_agg_type )
            return number,
     
       member function
           ODCIAggregateIterate(self IN OUT string_agg_type ,
                                value IN varchar2 )
           return number,
     
      member function
           ODCIAggregateTerminate(self IN string_agg_type,
                                  returnValue OUT  varchar2,
                                  flags IN number)
           return number,
     
      member function
           ODCIAggregateMerge(self IN OUT string_agg_type,
                              ctx2 IN string_agg_type)
           return number
   );
   /

Then when i try calling the function, i get ORA-04067 error.
What is the problem and how do i fix?

SELECT  A.course_name, A.course_id, stragg( C.firstname||' '|| C.lastname) as Na
me
FROM course_users B
    JOIN users C ON (B.users_pk1 = C.pk1)
    JOIN course_main A ON (B.crsmain_pk1 = A.pk1)
WHERE  B.row_status = '0' AND A.course_id like '2102%' and B.role = 'P'
group by A.course_id,A.course_name;
~


*
ERROR at line 1:
ORA-04067: not executed, type body "BB_BB60.STRING_AGG_TYPE" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"BB_BB60.STRING_AGG_TYPE"
ORA-06512: at line 1
xoxomosAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
types are like packages,  you need the declaration and the body.  

I assume you're trying to build a stragg type function

you'll need something like this
CREATE OR REPLACE TYPE BODY STRING_AGG_TYPE
IS
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        sctx := string_agg_type(NULL);
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregateiterate(
        SELF    IN OUT   string_agg_type,
        VALUE   IN       VARCHAR2
    )
        RETURN NUMBER
    IS
    BEGIN
        SELF.total := SELF.total || ',' || VALUE;
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregateterminate(
        SELF          IN       string_agg_type,
        returnvalue   OUT      VARCHAR2,
        flags         IN       NUMBER
    )
        RETURN NUMBER
    IS
    BEGIN
        returnvalue := LTRIM(SELF.total, ',');
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregatemerge(
        SELF   IN OUT   string_agg_type,
        ctx2   IN       string_agg_type
    )
        RETURN NUMBER
    IS
    BEGIN
        SELF.total := SELF.total || ctx2.total;
        RETURN odciconst.success;
    END;
END; 
/

Open in new window

0
 
sdstuberCommented:
have you created the type body?
0
 
xoxomosAuthor Commented:
Thank you!  Excellent.
0
 
sdstuberCommented:
glad I could help
0
All Courses

From novice to tech pro — start learning today.