Home » SQL & PL/SQL » SQL & PL/SQL » Character String Buffer too small (Oracle SQL Developer: ODMiner 3.0.04. Oracle 11g)
Character String Buffer too small [message #572620] Fri, 14 December 2012 01:43 Go to next message
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??
Re: Character String Buffer too small [message #572623 is a reply to message #572620] Fri, 14 December 2012 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check your function.

Regards
Michel
Re: Character String Buffer too small [message #572705 is a reply to message #572620] Sun, 16 December 2012 07:06 Go to previous messageGo to next message
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
Re: Character String Buffer too small [message #572712 is a reply to message #572705] Sun, 16 December 2012 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 10981
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.
Re: Character String Buffer too small [message #572714 is a reply to message #572705] Sun, 16 December 2012 08:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
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.
Re: Character String Buffer too small [message #572716 is a reply to message #572714] Sun, 16 December 2012 09:00 Go to previous message
Solomon Yakobson
Messages: 2036
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.
Previous Topic: Error(4,9): PLS-00103: Encountered the "," symbol
Next Topic: select queries
Goto Forum:
  


Current Time: Tue Sep 30 13:43:49 CDT 2014

Total time taken to generate the page: 0.05517 seconds