Re: VARCHAR2 Length
From: Dan Blum <tool_at_panix.com>
Date: Wed, 9 Dec 2009 18:02:58 +0000 (UTC)
Message-ID: <hfoooi$o4f$1_at_reader1.panix.com>
The Magnet <art_at_unsu.com> wrote:
> On Dec 9, 10:50?am, joel garry <joel-ga..._at_home.com> wrote:
> > On Dec 9, 8:30?am, The Magnet <a..._at_unsu.com> wrote:
> >
> >
> >
> > > 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.
> >
> > 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
> > --
> > _at_home.com is bogus.http://www.signonsandiego.com/news/2009/dec/08/how-fake-sites-trick-s...
Date: Wed, 9 Dec 2009 18:02:58 +0000 (UTC)
Message-ID: <hfoooi$o4f$1_at_reader1.panix.com>
The Magnet <art_at_unsu.com> wrote:
> On Dec 9, 10:50?am, joel garry <joel-ga..._at_home.com> wrote:
> > On Dec 9, 8:30?am, The Magnet <a..._at_unsu.com> wrote:
> >
> >
> >
> > > 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.
> >
> > 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
> > --
> > _at_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_at_panix.com "I wouldn't have believed it myself if I hadn't just made it up."Received on Wed Dec 09 2009 - 12:02:58 CST