Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #289978] Thu, 27 December 2007 00:10 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

Hope i will get a solution.

I created a table with CLOB column.
I want to display 5000 characters through substr.

CREATE TABLE TEST_EXP (id NUMBER,msg CLOB)


DECLARE
    MSG CLOB;
BEGIN
    MSG:= LPAD('V',25000,'A');
    INSERT INTO TEST_EXP VALUES(1,msg);
END;

When i execute the query 
select dbms_lob.substr(msg,3000,1) from test_exp;

DBMS_LOB.SUBSTR(MSG,3000,1)
--------------------------------------------------------------------------------
12345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890 123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901 23456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
901234567890123456789012345678901234567890123456789012 3456789012345678901234567
89012345678901234567890123456789012345678901234567890123456789012345678901234567
8901234567890123456789012345678901234567890123 456789012345678901234567890123456
78901234567890123456789012345678901234567890123456789012345678901234567890123456
78901234567890123456789012345678901234 56789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890123456789012345

DBMS_LOB.SUBSTR(MSG,3000,1)
--------------------------------------------------------------------------------
678901234567890123456789012345 6789012345678901234567890123456789012345678901234
56789012345678901234567890123456789012345678901234567890123456789012345678901234
5678901234567890123456 789012345678901234567890123456789012345678901234567890123
45678901234567890123456789012345678901234567890123456789012345678901234567890123
45678901234567 89012345678901234567890123456789012345678901234567890123456789012
34567890123456789012345678901234567890123456789012345678901234567890123456789012
345678 9012345678901234567890123456789012345678901234567890123456789012345678901
234567890123456789012345678901234567890123456789012345678901234567890123456789 0
12345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890 123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789

DBMS_LOB.SUBSTR(MSG,3000,1)
--------------------------------------------------------------------------------
01234567890123456789012345678901234567890123456789012345678901 23456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
901234567890123456789012345678901234567890123456789012 3456789012345678901234567
89012345678901234567890123456789012345678901234567890123456789012345678901234567
8901234567890123456789012345678901234567890123 456789012345678901234567890123456
78901234567890123456789012345678901234567890123456789012345678901234567890123456
78901234567890123456789012345678901234 56789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890123456789012345
678901234567890123456789012345 6789012345678901234567890123456789012345678901234
56789012345678901234567890123456789012345678901234567890123456789012345678901234
5678901234567890123456 789012345678901234567890123456789012345678901234567890123

DBMS_LOB.SUBSTR(MSG,3000,1)
--------------------------------------------------------------------------------
45678901234567890123456789012345678901234567890123456789012345678901234567890123
45678901234567 89012345678901234567890123456789012345678901234567890123456789012
34567890123456789012345678901234567890123456789012345678901234567890123456789012
345678 9012345678901234567890123456789012345678901234567890123456789012345678901
2345678901234567890123456789012345678901

It shows me result but when i execute following query it gives me as error.

SQL> select dbms_lob.substr(msg,5000,1) from test_exp;
select dbms_lob.substr(msg,5000,1) from test_exp
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


Please tell me how can i display 5000 characters.
Thanks in advance.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #289981 is a reply to message #289978] Thu, 27 December 2007 00:29 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
For some reason DBMS_LOB.SUBSTR can only return up to 4000 characters.

Strangely enough will following query display/return those 5000 characters

SELECT SUBSTR( msg , 1 , 5000 )
FROM test_exp



HTH
Marc

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #289989 is a reply to message #289981] Thu, 27 December 2007 00:53 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks for reply.

I used the same thing before but it gives me only 255 characters display.

SQL> SELECT SUBSTR( msg , 1 , 5000 ) FROM test_exp;

SUBSTR(MSG,1,5000)
--------------------------------------------------------------------------------
12345678901234567890123456789012345678901234567890123456789012345678901234567890

SQL>


Please let me know how can i display it.

Thanks in advance.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #289990 is a reply to message #289989] Thu, 27 December 2007 00:58 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
In SQL*plus:

SQL> set long <value>


Maximum <value> would be 999999999

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #289991 is a reply to message #289981] Thu, 27 December 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

Strangely enough will following query display/return those 5000 characters

This is because dbms_lob.substr returns a varchar2 which is limited to 4000 bytes whereas substr is an overloaded function that returns either a varchar2 either a clob.

Regards
Michel

[Updated on: Thu, 27 December 2007 00:58]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #289993 is a reply to message #289991] Thu, 27 December 2007 01:02 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Thu, 27 December 2007 07:58

Quote:

Strangely enough will following query display/return those 5000 characters

This is because dbms_lob.substr returns a varchar2 which is limited to 4000 bytes whereas substr is an overloaded function that returns either a varchar2 either a clob.

Regards
Michel



Something I know, but tend to forget apparently Embarassed

Thanks for reminding

Marc

Report message to a moderator

Previous Topic: ORA-02429 / ORA-25188 dance (removing primary key and index)
Next Topic: embiding into an object
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Feb 11 17:01:16 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.