Re: VARCHAR2 Length
Date: Thu, 10 Dec 2009 20:07:12 +0000 (UTC)
Message-ID: <hfrkdg$ck8$1_at_reader1.panix.com>
Tim X <timx_at_nospam.dev.null> wrote:
> The Magnet <art_at_unsu.com> writes:
> > On Dec 9, 10:24?am, The Magnet <a..._at_unsu.com> wrote:
> >> So, the max length of VARCHAR2 is 4000 bytes. ?So, in PL/SQL, if you
> >> need to declare a variable longer than 4000, what can you use? ?We
> >> need to declare a variable and continue to concatenate strings onto it
> >> and it becomes longer than 4000 bytes, so, we're SOL?
> >>
> >> Still looking for the answer on other sites.
> >
> > Of course I should have said more: What we need to do is put together
> > a dynamic query and open up a cursor:
> >
> > v_select := 'blah blah blah....'
> >
> > open p_data for v_select;
> >
> > v_select is longer than 4000 bytes.
> limit of varchar2 is 4k for database columns
> limit of varchar2 for pl/sql is 32k
> Note that I also think dynamic sql is limited to 32k unless you use the
> dbms_sql package (but verify this against the Oracle version your
> using as I could be wrong).
That is what I thought (because it was stated in the documentation I quoted earlier in the thread), but in 10g this in fact not the case. Dynamic SQL can be longer than 32K if you assemble it at execution time. E.g., this works:
declare
foo varchar2(32767) := 'select 0 from dual ';
foo2 varchar2(32767) := 'select 0 from dual ';
x number_array;
begin
for i in 1 .. 999 loop
foo := foo||' union all select '||i||' from dual';
foo2 := foo2||' union all select '||i||' from dual';
end loop;
dbms_output.put_line(length(foo));
dbms_output.put_line(length(foo2));
execute immediate (foo||' union all '||foo2) bulk collect into x;
dbms_output.put_line(x.count);
end;
-- _______________________________________________________________________ Dan Blum tool_at_panix.com "I wouldn't have believed it myself if I hadn't just made it up."Received on Thu Dec 10 2009 - 14:07:12 CST