Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: 9.0.2 varchar2(32767) acting like varchar2(4000)

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US