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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
have you created the type body?
0
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xoxomosAuthor Commented:
Thank you!  Excellent.
0
sdstuberCommented:
glad I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.