OraFAQ Forum: SQL & PL/SQL » Character String Buffer too small

Home » SQL & PL/SQL » SQL & PL/SQL » Character String Buffer too small (Oracle SQL Developer: ODMiner 3.0.04. Oracle 11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Character String Buffer too small [message #572620]

Fri, 14 December 2012 01:43

Saya22
Messages: 14
Registered: December 2012
Location: London

Junior Member

I'm trying to create a table with a select statement. I want to populate this new table with the aggregated value from a VIEW. Following is the code used for creating the VIEW,

  create or replace view FINAL_WEB_LOG
     as
     select SESSION_ID, 
            SESSION_DT, 
            C_IP, 
            CS_USER_AGENT,
            tab_to_string(CAST(COLLECT(web_link) AS t_varchar2_tab)) WEBLINKS
     from web_views_tab    
     group by C_IP, CS_USER_AGENT, SESSION_DT;

I want to create a table with WEBLINKS and SESSION_ID which is a sequence from another table.

CREATE TABLE FINAL_WEB
AS
SELECT weblinks 
FROM final_web_log
UNION
SELECT session_id
FROM WEB_VIEWS_TAB;

This now gives me the following error,

SQL Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

This has to do with the field, Weblinks, it does have longer values. What can I do now to get around this error??

Report message to a moderator

Re: Character String Buffer too small [message #572623 is a reply to message #572620]

Fri, 14 December 2012 01:46

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Check your function.

Regards
Michel

Report message to a moderator

Re: Character String Buffer too small [message #572705 is a reply to message #572620]

Sun, 16 December 2012 07:06

gopigt
Messages: 7
Registered: December 2012
Location: Bangalore

Junior Member

Hi,

Check the data type of the column "weblinks" from table final_web_log and data type of the column "session_id"
from table "WEB_VIEWS_TAB".

When you use UNION to get two differnt table column values, both the column data type must be the same else you will get this kind of error.

Thanks
Gopi GT

Report message to a moderator

Re: Character String Buffer too small [message #572712 is a reply to message #572705]

Sun, 16 December 2012 08:44

cookiemonster
Messages: 9148
Registered: September 2008
Location: Rainy Manchester

Senior Member

You will not get that error from a data type mismatch in a union.
That error only comes from PL/SQL.

Report message to a moderator

Re: Character String Buffer too small [message #572714 is a reply to message #572705]

Sun, 16 December 2012 08:51

Solomon Yakobson
Messages: 1398
Registered: January 2010

Senior Member

gopigt wrote on Sun, 16 December 2012 08:06

else you will get this kind of error.

No, you will get a different error:

ORA-01790: expression must have same datatype as corresponding expression

As Michel already noted issue is in tab_to_string function. Most likely (based on name) function concatenates collection elements and returns it as VARCHAR2. In such case function will fail with ORA-06502 if concatenation length exceeds 4000 bytes (maximum VARCHAR2 length in SQL).

SY.

Report message to a moderator

Re: Character String Buffer too small [message #572716 is a reply to message #572714]

Sun, 16 December 2012 09:00

Solomon Yakobson
Messages: 1398
Registered: January 2010

Senior Member

And below is an example:

SQL> select  f1('A','B')
  2    from  dual
  3  /

F1('A','B')
----------------------------------------------------------------------------
A,B

SQL> select  f1(lpad('A',2000,'A'),lpad('B',2000,'B'))
  2    from  dual
  3  /
select  f1(lpad('A',2000,'A'),lpad('B',2000,'B'))
        *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.F1", line 4


SQL>

Solution could be changing function return type to CLOB:

SQL> create or replace
  2  function f1(p1 varchar2,p2 varchar2) return clob
  3  is
  4  begin
  5      return to_clob(p1) || ',' || p2;
  6  end;
  7  /

Function created.

SQL> select  f1('A','B')
  2    from  dual
  3  /

F1('A','B')
--------------------------------------------------------------------------------
A,B

SQL> set long 10000
SQL> select  f1(lpad('A',2000,'A'),lpad('B',2000,'B'))
  2    from  dual
  3  /

F1(LPAD('A',2000,'A'),LPAD('B',2000,'B'))
--------------------------------------------------------------------------------
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

F1(LPAD('A',2000,'A'),LPAD('B',2000,'B'))
--------------------------------------------------------------------------------
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

F1(LPAD('A',2000,'A'),LPAD('B',2000,'B'))
--------------------------------------------------------------------------------
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
,BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

F1(LPAD('A',2000,'A'),LPAD('B',2000,'B'))
--------------------------------------------------------------------------------
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

F1(LPAD('A',2000,'A'),LPAD('B',2000,'B'))
--------------------------------------------------------------------------------
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
B


SQL>

SY.

Report message to a moderator

Previous Topic:	Error(4,9): PLS-00103: Encountered the "," symbol
Next Topic:	select queries

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 19:35:34 CDT 2013

Total time taken to generate the page: 1.33312 seconds