SQL & PL/SQL

Hi All,

I have created the following types.

CREATE OR REPLACE TYPE ADDRESS_TYPE AS OBJECT (STREET_NO VARCHAR2(25),
ADDRESS_LINE VARCHAR2(80),
TOWN_CITY VARCHAR2(80),
REGION VARCHAR2(80),
COUNTRY VARCHAR2(80),
) NOT FINAL;

CREATE OR REPLACE TYPE SUPPLIER_TYPE AS OBJECT(SUPPLIER_ID NUMBER(12),
SUPPLIER_CODE NUMBER(12),
SUPPLIER_NAME VARCHAR2(1),
ADDRESS ADDRESS_TYPE);

CREATE OR REPLACE TYPE SUPPLIER_TYPE_TAB AS TABLE OF SUPPLIER_TYPE;


With EXECUTE permission

In supplier table there are 6000 records.

FUNCTION get_supplier RETURN supplier_type_tab IS
l_tab supplier_type_tab;
BEGIN

SELECT supplier_type(s.supplier_id,
s.supplier_code,
s.supplier_name,
address_type(a.street_no,
a.address_line,
a.town_city,
a.region,
a.country)) BULK COLLECT
INTO l_tab
FROM supplier s,
supplier_address a
WHERE a.SUPPLIER_ID (+) = s.SUPPLIER_ID;

RETURN l_tab;
END;
-----------------------

Using query

select get_supplier from dual;

following is the error i found.

ORA-22814: attribute or element value is larger than specified in type

On web following is the solution which is not clear to me

-- ORA-22814: attribute or element value is larger than specified in type
-- Cause: Value provided for the object type attribute or collection element exceeded the size specified in the type declaration.
--Action: Choose another value and retry the operation.


Please help me to resolve this issue..

Many thanks in advance

Regards,
Hasan