Thanks for the question, Rahul.
Asked: April 17, 2014 - 8:32 pm UTC
Last updated: April 23, 2014 - 10:25 pm UTC
Version: 10.1.7
Viewed 10K+ times! This question is
You Asked
This is my following code for stragg
--
CREATE OR REPLACE type APPS.ccl_string_agg_type as object
(
data vcArray,
static function
ODCIAggregateInitialize(sctx IN OUT ccl_string_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT ccl_string_agg_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN ccl_string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT ccl_string_agg_type,
ctx2 IN ccl_string_agg_type)
return number
);
/
------
/* Formatted on 4/16/2014 3:14:12 PM (QP5 v5.252.13127.32847) */
CREATE OR REPLACE TYPE BODY APPS.ccl_STRING_AGG_TYPE
IS
STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT ccl_string_agg_type)
RETURN NUMBER
IS
BEGIN
sctx := ccl_string_agg_type (vcArray ());
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT ccl_string_agg_type,
VALUE IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
data.EXTEND;
data (data.COUNT) := VALUE;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate (self IN ccl_string_agg_type,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
IS
l_data VARCHAR2 (4000);
BEGIN
FOR x IN ( SELECT COLUMN_VALUE
FROM TABLE (data)
ORDER BY 1)
LOOP
l_data := l_data || ',' || x.COLUMN_VALUE;
END LOOP;
returnValue := LTRIM (l_data, ',');
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ccl_string_agg_type,
ctx2 IN ccl_string_agg_type)
RETURN NUMBER
IS
BEGIN -- not really tested ;)
FOR i IN 1 .. ctx2.data.COUNT
LOOP
data.EXTEND;
data (data.COUNT) := ctx2.data (i);
END LOOP;
RETURN ODCIConst.Success;
END;
END;
/
--------
CREATE OR REPLACE FUNCTION APPS.ccl_stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING ccl_string_agg_type;
/
--- i dont know what is the issue in one database this was working fine
but when i try to compile it in another database i am getting the above error
and Tom said...
you do not have your vcArray type compiled in the one database.
my examples are:
create or replace type vcarray is table of varchar2(400);
create or replace type stragg as object
(
l_total vcarray,
static function odciaggregateinitialize(ctx in out stragg) return number,
you have to define VCARRAY
Is this answer out of date? If it is, please let us know via a Comment