| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: URGENT - HELP ORA-06502:
Try DBMS_LOB.WRITEAPPEND
Sorry I forgot this are not funcions allowed, in my example the string was
too short.
SQL and PL/SQL VARCHAR2 Functions/Operators Now Allowed for CLOBs
The following SQL VARCHAR2 functions and operators are now allowed for
CLOBs, as indicated in Table 7-6:
n INSTR related operators/functions
n INSTR() and variants (See Table 7-7)
n LIKE
n REPLACE()
n CONCAT and ||
n LENGTH() and variants (See Table 7-7)
n SUBSTR() and variants (See Table 7-7)
n TRIM(), LTRIM() and RTRIM()
n LOWER(), UPPER(), NLS_LOWER(), NLS_UPPER()
n LPAD() and RPAD()
Try DBMS_LOB.WRITEAPPEND this
1 declare
2 myStory CLOB;
3 revisedStory CLOB;
4 myGist VARCHAR2(100);
5 revisedGist VARCHAR2(100);
6 begin
7 -- select a CLOB column into a CLOB variable
8 SELECT Story INTO myStory FROM Multimedia_tab WHERE clip_id=10;
9 -- perform VARCHAR2 operations on a CLOB variable
10 revisedStory := UPPER(SUBSTR(myStory, 100, 1));
11 -- revisedStory is a temporary LOB
12 -- Concat a VARCHAR2 at the end of a CLOB
13 revisedStory := revisedStory || myGist;
14 -- The following statement will raise an error since myStory is
15 -- longer than 100 bytes
16 myGist := myStory;
17 end;
Please note that in line 10 of "PL/SQL Example 4: CLOB Variables in PL/SQL", a
temporary CLOB is implicitly created and is pointed to by the revisedStory
CLOB locator. In the current interface the line can be expanded as:
buffer VARCHAR2(32000)
DBMS_LOB.CREATETEMPORARY(revisedStory);
buffer := UPPER(DBMS_LOB.SUBSTR(myStory,100,1));
DBMS_LOB.WRITE(revisedStory,length(buffer),1, buffer);
In line 13, myGist is appended to the end of the temporary LOB, which has the
same effect of:
DBMS_LOB.WRITEAPPEND(revisedStory, myGist, length(myGist));
In some occasions, implicitly created temporary LOBs in PL/SQL statements can
change the representation of LOB locators previously defined. Consider the next
example.
> This has the same result - 'PLS-00306: wrong number or types of arguments
in
> call to 'concat'
> ----- Original Message -----
> From: "Juan Cachito Reyes Pacheco" <jreyes_at_dazasoftware.com>
> To: <oracle-l_at_freelists.org>
> Sent: Friday, February 27, 2004 1:20 PM
> Subject: Re: URGENT - HELP ORA-06502:
>
>
> > Did you tried concat
> > declare
> >
> > a clob := 'a';
> >
> > b clob := 'sb';
> >
> > begin
> >
> > dbms_output.put_line( concat(a,b) );
> >
> > end;
> >
> > ----- Original Message -----
> > From: "Igor Neyman" <ineyman_at_perceptron.com>
> > To: <oracle-l_at_freelists.org>
> > Sent: Friday, February 27, 2004 9:16 AM
> > Subject: RE: URGENT - HELP ORA-06502:
> >
> >
> > > "||" operator does not work with CLOB.
> > >
> > > Igor Neyman, OCP DBA
> > > ineyman_at_perceptron.com
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: oracle-l-bounce_at_freelists.org
> > > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Oracle
> > > Sent: Friday, February 27, 2004 7:04 AM
> > > To: oracle-l_at_freelists.org
> > > 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_at_perceptron.com>
> > > To: <oracle-l_at_freelists.org>
> > > 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_at_perceptron.com
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: oracle-l-bounce_at_freelists.org
> > > > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
> > > > Sent: Thursday, February 26, 2004 12:45 PM
> > > > To: 'oracle-l_at_freelists.org'
> > > > 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_at_freelists.org
> > > > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Igor Neyman
> > > > Sent: Thursday, February 26, 2004 12:39 PM
> > > > To: oracle-l_at_freelists.org
> > > > 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_at_perceptron.com
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: oracle-l-bounce_at_freelists.org
> > > > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Oracle
> > > > Sent: Thursday, February 26, 2004 12:13 PM
> > > > To: oracle-l_at_freelists.org
> > > > 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_at_freelists.org
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > > FAQ is at http://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_at_freelists.org
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > > FAQ is at http://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_at_freelists.org
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > > FAQ is at http://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_at_freelists.org
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > > FAQ is at http://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_at_freelists.org
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > FAQ is at http://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_at_freelists.org
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > FAQ is at http://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_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Feb 27 2004 - 10:33:29 CST
 |
 |