ORA-21525 attribute number or (collection element at index) violated its constraints

[Jayarama Nettar]

Hi,

I am using a string aggregate function which makes use of a type named
"string_agg_type".

The problem is that when I run the script to create this type, it gives
the following error:

ORA-21525 attribute number or (collection element at index) violated
its constraints.

The type and its body are as follows:

CREATE OR REPLACE
TYPE string_agg_type AS OBJECT (
total VARCHAR2 (4000),
delim VARCHAR2 (100),
last_delim VARCHAR2 (100),
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
);
/

CREATE OR REPLACE
TYPE BODY string_agg_type
IS

STATIC FUNCTION odciaggregateinitialize (sctx IN OUT
string_agg_type)
RETURN NUMBER
IS
l_delim VARCHAR2 (100) DEFAULT ', ';
l_last_delim VARCHAR2 (100) DEFAULT ' & ';
BEGIN
sctx := string_agg_type (NULL, l_delim, l_last_delim);
RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT string_agg_type,
VALUE IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
SELF.total := SELF.total || SELF.delim || VALUE;
RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregateterminate (
SELF IN string_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
l_pos NUMBER;
BEGIN
returnvalue := LTRIM (SELF.total, SELF.delim);
l_pos:=INSTR(returnvalue,SELF.delim,-1);
IF ( l_pos >0 )
THEN
returnvalue:=SUBSTR (returnvalue,1,l_pos-1) ||SELF.last_delim ||
SUBSTR (returnvalue, l_pos+LENGTH(SELF.delim));
END IF;
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;
/

Can anybody tell me what is wrong here?

Thanks in advance.

Regards,
Jayarama Nettar.

[Jo]

Hi Nettar,
please find the description below

ORA-21525 attribute number or (collection element at index) string
violated its constraints

Cause: Attribute value or collection element value violated its
constraint.

Action: Change the value of the attribute or collection element such
that it meets its constraints. The constraints are specified as part of
the attribute or collection element's schema information

Hope this may help you

Regards,
Jomon

[Jayarama Nettar]

Thanks for the reply.

The attributes in the above function are VARCHAR2. I didnot get what
constraints do oracle impose on VARCHAR2. And how does it say attribute
value violated its constraint when we cannot impose constraints on
TYPES?

[fitzj...@cox.net]

> Jayarama Nettar.\

Works just fine for me:

SQL> CREATE OR REPLACE
2 TYPE string_agg_type AS OBJECT (
3 total VARCHAR2 (4000),
4 delim VARCHAR2 (100),
5 last_delim VARCHAR2 (100),
6 STATIC FUNCTION odciaggregateinitialize (sctx IN OUT
7 string_agg_type)
8 RETURN NUMBER,
9 MEMBER FUNCTION odciaggregateiterate (
10 SELF IN OUT string_agg_type,
11 VALUE IN VARCHAR2
12 )
13 RETURN NUMBER,
14 MEMBER FUNCTION odciaggregateterminate (
15 SELF IN string_agg_type,
16 returnvalue OUT VARCHAR2,
17 flags IN NUMBER
18 )
19 RETURN NUMBER,
20 MEMBER FUNCTION odciaggregatemerge (
21 SELF IN OUT string_agg_type,
22 ctx2 IN string_agg_type
23 )
24 RETURN NUMBER
25 );
26 /

Type created.

SQL>
SQL>
SQL> CREATE OR REPLACE
2 TYPE BODY string_agg_type
3 IS
4
5
6 STATIC FUNCTION odciaggregateinitialize (sctx IN OUT
7 string_agg_type)
8 RETURN NUMBER
9 IS
10 l_delim VARCHAR2 (100) DEFAULT ', ';
11 l_last_delim VARCHAR2 (100) DEFAULT ' & ';
12 BEGIN
13 sctx := string_agg_type (NULL, l_delim, l_last_delim);
14 RETURN odciconst.success;
15 END;
16
17
18 MEMBER FUNCTION odciaggregateiterate (
19 SELF IN OUT string_agg_type,
20 VALUE IN VARCHAR2
21 )
22 RETURN NUMBER
23 IS
24 BEGIN
25 SELF.total := SELF.total || SELF.delim || VALUE;
26 RETURN odciconst.success;
27 END;
28
29
30 MEMBER FUNCTION odciaggregateterminate (
31 SELF IN string_agg_type,
32 returnvalue OUT VARCHAR2,
33 flags IN NUMBER
34 )
35 RETURN NUMBER
36 IS
37 l_pos NUMBER;
38 BEGIN
39 returnvalue := LTRIM (SELF.total, SELF.delim);
40 l_pos:=INSTR(returnvalue,SELF.delim,-1);
41 IF ( l_pos >0 )
42 THEN
43 returnvalue:=SUBSTR (returnvalue,1,l_pos-1) ||SELF.last_delim ||
44 SUBSTR (returnvalue, l_pos+LENGTH(SELF.delim));
45 END IF;
46 RETURN odciconst.success;
47
48
49 END;
50
51
52 MEMBER FUNCTION odciaggregatemerge (
53 SELF IN OUT string_agg_type,
54 ctx2 IN string_agg_type
55 )
56 RETURN NUMBER
57 IS
58 BEGIN
59 SELF.total := SELF.total || ctx2.total;
60 RETURN odciconst.success;
61 END;
62 END;
63 /

Type body created.

SQL>

So which release of Oracle are you using? This executed correctly on
9.2.0.6. If you won't provide a release number there isn't much anyone
can do to help you.

David Fitzjarrell