Max Length for VARCHAR2 display is 4000?

[Stew Stryker]

I'm hoping this is just a problem in my setup, but I can't find it.

I'm trying to convert a CLOB to VARCHAR2 in a SQL window query. But if the note has more than 4000 characters, I get the error "character string buffer is too small". Yet if I don't convert the CLOB, it works fine. I thought the limit for VARCHAR2 was 32k? The record I'm trying to get has a note length of 4632.

SELECT note_id, dbms_lob.substr(note_text, 4200, 1) note_text, dbms_lob.getlength(note_text) len
  FROM notes
 WHERE note_id = 25787;

Thanks for straightening me out! :-/

 

[CTzen]

Stew,

limit for VARCHAR2 in PL/SQL is 32K. But in SQL it's 4000.

Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
Connected as slava

SQL>
create or replace function vc2_len(p_length in number)
return varchar2
is
    l_vc varchar2(32000);
begin
    l_vc := rpad('x', p_length, 'x');
    return l_vc;
end;
/

Function created

Executed in 0.141 seconds

col cmd for a100
-- 4000 --
select vc2_len(4000) cmd from dual;

CMD
----------------------------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
...
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Executed in 0.218 seconds

-- 4001 --
select vc2_len(4001) cmd from dual;

select vc2_len(4001) cmd from dual

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SLAVA.VC2_LEN", line 7

-- 5000 --
declare
    l_var varchar2(5000);
begin
    l_var := vc2_len(5000);
end;
/

PL/SQL procedure successfully completed

Executed in 0.063 seconds

 

[Stew Stryker]

DOH!

I knew that but totally forgot about it applying in this instance! I'm so embarassed! Thanks for the clue (which I clearly lacked!).

Unfortunately the reporting tool I'm trying to get this data out via (PowerBuilder) gives me the following error when I try to access the CLOB directly:

"This column's data type (clob_column) requires the use of an embedded SQL statement."

So I'd been trying to convert the CLOB to VARCHAR2, and hit this limit. I thought about trying to convert this to a LONG, but couldn't manage that.

 

[CTzen]

will something similar to this help?

select id,
       dbms_lob.substr(note, 4000, 1) part1,
       decode(
          sign(4000 - dbms_lob.getlength(note)),
          -1, dbms_lob.substr(note, 4000, 4001),
          null
       ) part2
  from notes
;

 

[Stew Stryker]

Slava,

Thanks for the thought, but we're trying to get it out as a single column.