Re: VARCHAR2 Length

The Magnet <art@xxxxxxxx> wrote:
On Dec 9, 10:50?am, joel garry <joel-ga...@xxxxxxxx> wrote:
On Dec 9, 8:30?am, The Magnet <a...@xxxxxxxx> wrote:



On Dec 9, 10:24?am, The Magnet <a...@xxxxxxxx> 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@xxxxxxxxx
"I wouldn't have believed it myself if I hadn't just made it up."
.

Relevant Pages

  • Re: VARCHAR2 Length
    ... need to declare a variable longer than 4000, ... need to declare a variable and continue to concatenate strings onto it ... a dynamic query and open up a cursor: ...
    (comp.databases.oracle.misc)
  • Re: VARCHAR2 Length
    ... need to declare a variable longer than 4000, ... need to declare a variable and continue to concatenate strings onto it ... v_select:= 'blah blah blah....' ...
    (comp.databases.oracle.misc)
  • RE: spl and dynamic sql and cursors
    ... Can someone point me to howto use dynamic sql, ... Guide to sql syntax ... the problem is that i can not DECLARE cursor_1 FOR stmt_1; ... IBM Informix Dynamic Server Version 11.70.UC4IE Software Serial Number ...
    (comp.databases.informix)
  • Re: Turning rows into columns.
    ... You might try something like Dynamic SQL (I just found out how powerful this ... DECLARE @i INT ... WHILE @i <= COUNTFROM dtblRowColumns ... > stored procedure, it might find 5 rows of data. ...
    (microsoft.public.sqlserver.programming)
  • Cursor based loop not working properly
    ... I am having trouble with getting a variable in a loop to update. ... Then I am doing an exec on a dynamic sql statement for each of these tables. ... why my @recordCount is not getting populated. ... DECLARE @numTablesWithProtseg VARCHAR ...
    (microsoft.public.sqlserver.programming)