Re: URGENT - HELP ORA-06502:
- From: "Oracle" <Oracle_list@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Mar 2004 17:12:01 -0000
I created this function:
FUNCTION Fmemo_text (p_mtsn IN NUMBER)
return CLOB is
l_str CLOB;
BEGIN
Dbms_Lob.createtemporary(l_str,TRUE,Dbms_Lob.Call);
FOR x IN
(SELECT REPLACE(REPLACE(MEMO_TEXT,chr(13),'Z'),CHR(10),'') AS MEMO_TEXT
FROM NC_MEMO_TEXT
WHERE MTSN = p_mtsn
ORDER BY TEXT_ORDER)
LOOP
Dbms_Lob.WriteAppend(l_str,Length(x.memo_text),x.memo_text);
END LOOP;
RETURN l_str;
END;
However when i tried this insert into table concat_memo_text (mtsn NUMBER,
memo_text CLOB) i get an error.
INSERT INTO CONCAT_MEMO_TEXT (mtsn,memo_text)
(SELECT mtsn,fmemo_text( mtsn ) MEMO_TEXT
FROM nc_memo_text
GROUP BY mtsn);
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 722
Any ideas why??
----- Original Message -----
From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, February 27, 2004 1:16 PM
Subject: RE: URGENT - HELP ORA-06502:
> "||" operator does not work with CLOB.
>
> Igor Neyman, OCP DBA
> ineyman@xxxxxxxxxxxxxx
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Oracle
> Sent: Friday, February 27, 2004 7:04 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: URGENT - HELP ORA-06502:
>
> The query returned a record which has length of 9612. This could vary
> depending on when the query is run .
>
> So i changed the concat_memo_text.memo_text to CLOB.
> And changed the function to use CLOB also.
>
> SQL> CREATE OR REPLACE FUNCTION Fmemo_text (p_mtsn IN NUMBER)
> 2 return CLOB is
> 3 l_str CLOB;
> 4 BEGIN
> 5 FOR x IN
> 6 (SELECT REPLACE(REPLACE(MEMO_TEXT,chr(13),'Z'),CHR(10),'') AS
> MEMO_TEXT
> 7 FROM NC_MEMO_TEXT
> 8 WHERE MTSN = p_mtsn
> 9 ORDER BY TEXT_ORDER)
> 10 LOOP
> 11 l_str := l_str||''||x.memo_text;
> 12 END LOOP;
> 13 RETURN l_str;
> 14 END;
> 15 /
>
> However, i get error:
>
> 11/18 PLS-00306: wrong number or types of arguments in call to '||'
>
> Have i missed something??
>
>
> ----- Original Message -----
> From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Thursday, February 26, 2004 6:19 PM
> Subject: RE: URGENT - HELP ORA-06502:
>
>
> > You could be right, but:
> >
> > Select MTSN, sum(length(MEMO_TEXT))
> > From NC_MEMO_TEXT
> > Group by MTSN;
> >
> > Should tell, if "l_str" is big enough to hold the result of
> > concatenation.
> >
> > Igor Neyman, OCP DBA
> > ineyman@xxxxxxxxxxxxxx
> >
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
> > Sent: Thursday, February 26, 2004 12:45 PM
> > To: 'oracle-l@xxxxxxxxxxxxx'
> > Subject: RE: URGENT - HELP ORA-06502:
> >
> > Actually could it be that while pl/sql supports a varchar2 being up to
> > 32K
> > in size that the calling environment only supports a varchar2 being as
> > large
> > as the database supports: 4,000 bytes?
> >
> > How large is the string returned by Fmemo_text? Can you test with a
> > value
> > that will return under 2K and see if the error still occurs or if the
> > function works?
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Igor Neyman
> > Sent: Thursday, February 26, 2004 12:39 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: RE: URGENT - HELP ORA-06502:
> >
> >
> > What error says, is that l_str variable in FUNCTION Fmemo_text is too
> > small to hold the result of concatenation.
> >
> > Igor Neyman, OCP DBA
> > ineyman@xxxxxxxxxxxxxx
> >
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Oracle
> > Sent: Thursday, February 26, 2004 12:13 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: URGENT - HELP ORA-06502:
> >
> > Hi,
> >
> > I have the following 2 Tables:
> >
> > CONCAT_MEMO_TEXT -
> > MTSN NUMBER(10)
> > MEMO_TEXT VARCHAR2(4000)
> >
> > NC_MEMO_TEXT -
> > MTSN NUMBER(10)
> > TEXT_ORDER NUMBER(10)
> > MEMO_TEXT VARCHAR2(255)
> > R_STATUS VARCHAR2(1)
> >
> > This function:
> >
> > CREATE OR REPLACE FUNCTION Fmemo_text (p_mtsn IN NUMBER)
> > return VARCHAR2 is
> > l_str VARCHAR2(10000);
> > BEGIN
> > FOR x IN
> > (SELECT REPLACE(REPLACE(MEMO_TEXT,chr(13),'Z'),CHR(10),'') AS
> > MEMO_TEXT
> > FROM NC_MEMO_TEXT
> > WHERE MTSN = p_mtsn
> > ORDER BY TEXT_ORDER)
> > LOOP
> > l_str := l_str||''||x.memo_text;
> > END LOOP;
> > RETURN l_str;
> > END;
> > /
> >
> > I am trying this insert statement:
> >
> > INSERT INTO CONCAT_MEMO_TEXT (mtsn,memo_text)
> > (SELECT mtsn,fmemo_text( mtsn ) MEMO_TEXT
> > FROM nc_memo_text
> > GROUP BY mtsn);
> >
> > However i get this error:
> >
> > ERROR at line 2:
> > ORA-06502: PL/SQL: numeric or value error: character string buffer too
> > small
> > ORA-06512: at line 1
> >
> > I have tried various settings for l_str in the finction however i keep
> > getting this error.
> >
> > I have also tried changing the datatype of CONCAT_MEMO_TEXT .MEMO_TEXT
> > to
> > Long, clob etc but no luck.
> >
> > Anyone got any ideas.
> >
> > Thanks
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
