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

From: Dave in Roanoke <pmmgpgp_at_gmail.com>
Date: 3 Aug 2006 10:13:43 -0700
Message-ID: <1154625222.948123.12080@m79g2000cwm.googlegroups.com>

DA Morgan wrote:

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

Daniel
Here is basically what I have.
Line 76 is where I experience my problem.

Thanks for getting involved.
Dave

1   --------------------------- step 1 -------------------------
2   CREATE OR REPLACE TYPE PO_Comment_Detail AS OBJECT (
3   PO           VARCHAR2(14),
4   Head_or_Line VARCHAR2(14),

5 buyer_code varchar2(6),
6 POComment VARCHAR2(32767)
7 )

8   --------------------------- step 2 -------------------------

9
10
11 create or replace type PO_Comments as 12 TABLE OF PO_Comment_Detail

13  --------------------------- step 3 -------------------------

14
15 procedure GetTheData(ThePoComments in out MyRC) is 16 Comments2Return PO_Comments:= PO_Comments() ; 17

18      AComment2Write  VARCHAR2(32767);
19      AccumilateOn    varchar2(100);
20      CntLinesInPO    number(5):=0;
21      CntLineout      number(5):=0;
22      CntLinesIn      number(10):= -1;
23      SzPO              number(10);
24      SzSRC             number(10);
25      SzBuy             number(10);
26      SzComm            number(10);

27
28
29 cursor TheRawData is

30  ----------------------------------------------

31 =--sql omitted as i feel would add no value

32  ----------------------------------------------

33
34 begin
35

36       for x in TheRawData loop
37       if CntLinesInPO = 0 then
38          AccumilateOn:= (trim( x.po_number) || '-' ||trim( x.src));
39       end if;
40       CntLinesInPO:= CntLinesInPO + 1;
41       CntLinesIn:= CntLinesIn + 1;
42
43        if CntLineout= 0 then
44             Comments2Return.extend;
45             Comments2Return(Comments2Return.last):=
46                PO_Comment_detail (x.po_number, x.src,x.buyer_code,'
');
47             AccumilateOn:= (trim( x.po_number) || '-' ||trim(
x.src));
48             CntLineout:= CntLineout + 1;
49        end if;
50
51        IF AccumilateOn <>(trim( x.po_number) || '-' ||trim( x.src))
THEN
52           Comments2Return(Comments2Return.last ).POComment:=
substr(AComment2Write,1,4000);
53           AComment2Write:= '';
54           AccumilateOn:=(trim( x.po_number) || '-' ||trim( x.src));
55           CntLineout:= 0;
56        end if;

57
58
59
60 AComment2Write:= AComment2Write || x.comm; 61

62         SzPO:=  LENGTH(x.po_number);
63         SzSRC:= LENGTH(x.src);
64         SzBuy:= LENGTH(x.buyer_code);
65         SzComm:= LENGTH(AComment2Write);
66         dbms_output.put_line( 'For Record ' || AccumilateOn ||
67         ' PO is '   || SzPO ||
68         ' SRC is '  || SzSRC ||
69         ' Buyer_Code is ' || SzBuy ||
70         ' Comments is ' || SzComm
71         );

72
73 end loop;
74
75 =-- update the last entry
76 Comments2Return(Comments2Return.last ).POComment:= substr(AComment2Write,1,4000);
77
78

79      open ThePoComments for
80      select * from table (cast (Comments2Return as PO_Comments));

81
82 end GetTheData; Received on Thu Aug 03 2006 - 12:13:43 CDT