Prev: Pivot table Query - Need help in building correct logic
Next: PL SQL Error
From: The Magnet on 9 Dec 2009 11:24

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.
From: The Magnet on 9 Dec 2009 11:30
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.
From: joel garry on 9 Dec 2009 11:50
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_plsql_semantics.htm#sthref837

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:492620500346758810

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-search-engines-to-hit-the-top/
From: The Magnet on 9 Dec 2009 12:02
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.
From: Malcolm Dew-Jones on 9 Dec 2009 11:09
The Magnet (art(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.
 |  Next  |  Last
Pages: 1 2
Prev: Pivot table Query - Need help in building correct logic
Next: PL SQL Error