Re: 9.0.2 varchar2(32767) acting like varchar2(4000)

Dave in Roanoke wrote:
> I have oracle 9i rls 2.
>
> I am experiencing a size problem using varchar2.
> (4000 limit?) .
>
> I have ( as shown below) a type with field that
> I expect to be able to hold 32k of text.
>
>
> CREATE OR REPLACE TYPE PO_Comment_Detail AS OBJECT (
> PO VARCHAR2(14),
> Head_or_Line VARCHAR2(14),
> buyer_code varchar2(6),
> POComment VARCHAR2(32767)
> )
>
> I have a procedure that works flawlessly except for
> populating POComments. It accumilates a good deal of
> into a varchar2(32767) and trys to put it into POComment.
> To get data into POComments I have to reduce its size
> substr(AComment2Write,1,4000) works and
> substr(AComment2Write,1,4001) doesn't.
>
> Any thoughts on what is happening or more to the point
> how I can make it work ?
>
> Thanks
> Dave

Your PL/SQL contains an error. Of course since you didn't post it, or the actual error message, no further help is possible.

DECLARE
   TYPE demo IS TABLE OF VARCHAR2(32767);    x demo;
BEGIN
   x := demo(RPAD('X', 32766, 'Z'));
   dbms_output.put_line(x(1));
END;
/

Done correctly it will work.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org