Prev: Pivot table Query - Need help in building correct logic
Next: PL SQL Error
From: Dan Blum on 9 Dec 2009 13:02
The Magnet <art(a)unsu.com> wrote:
> On Dec 9, 10:50?am, joel garry <joel-ga...(a)home.com> wrote:
> > On Dec 9, 8:30?am, The Magnet <a...(a)unsu.com> wrote:
> >
> >
> >
> > > On Dec 9, 10:24?am, The Magnet <a...(a)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.
> >
> > Wellll...http://awads.net/wp/2007/05/31/did-you-know-that-about-plsql-variables/
> >
> > Sounds like a job for a Character Large OBject.
> >
> > http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_...
> >
> > And poke around in asktom. ?Looks like what you want to do (cursor
> > +clob) is limited to 11g:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4...
> >
> > Remember, a lot of these things are version dependent, so be real
> > specific about your environment. ?Those of us drowning in the stream
> > of consciousness may not remember even if you just told us.
> >
> > jg
> > --
> > @home.com is bogus.http://www.signonsandiego.com/news/2009/dec/08/how-fake-sites-trick-s...

> My total bad, we're running Oracle 10g R2. But I'll take a look at
> the docs. Thanks for the references.

In 10g the maximum VARCHAR2 length in PL/SQL is 32767, and you can use
a string of that length for dynamic SQL.

To use a CLOB for this in 10g you have to use TO_CHAR on it, which of course
doesn't help (in fact it hurts, as TO_CHAR is limited to 8000 characters
for some reason).

If you need dynamic SQL statements longer than 32767 characters you will need to
use DBMS_SQL. As the Database Application Developer's Guide - Fundamentals says
in Chapter 8 (Coding Dynamic SQL) under "Advantages of the DBMS_SQL Package":

The DBMS_SQL package supports SQL statements larger than 32 KB.
Native dynamic SQL does not.

--
_______________________________________________________________________
Dan Blum tool(a)panix.com
"I wouldn't have believed it myself if I hadn't just made it up."
From: Tim X on 10 Dec 2009 01:57
The Magnet <art(a)unsu.com> writes:

> On Dec 9, 10:24 am, The Magnet <a...(a)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).

Tim
--
tcross (at) rapttech dot com dot au
From: Mark D Powell on 10 Dec 2009 09:50
On Dec 9, 11:09 am, yf...(a)vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:
> The Magnet (a...(a)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.
>
> In PL/SQL max length of VARCHAR2 is much longer than in SQL.

In PL/SQL the maximum length for varchar2 is 32767 which is 7 bytes
longer than a PL/SQL long (32760). The PL/SQL data types are distinct
from database data types. Though usually very similar in nature there
are differences.

PL/SQL functions are also distinct from the SQL functions of the same
name.

HTH -- Mark D Powell --




From: Dan Blum on 10 Dec 2009 15:07
Tim X <timx(a)nospam.dev.null> wrote:
> The Magnet <art(a)unsu.com> writes:

> > On Dec 9, 10:24?am, The Magnet <a...(a)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(a)panix.com
"I wouldn't have believed it myself if I hadn't just made it up."
First  |  Prev  | 
Pages: 1 2
Prev: Pivot table Query - Need help in building correct logic
Next: PL SQL Error