The Magnet (art (AT) unsu (DOT) 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.

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.

On Dec 9, 10:24*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
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.

On Dec 9, 8:30*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
Wellll... http://awads.net/wp/2007/05/31/did-y...sql-variables/

Sounds like a job for a Character Large OBject.

http://download.oracle.com/docs/cd/B....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/...20500346758810

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/2...o-hit-the-top/

On Dec 9, 10:50*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:
My total bad, we're running Oracle 10g R2. But I'll take a look at
the docs. Thanks for the references.

The Magnet <art (AT) unsu (DOT) com> wrote:
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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

The Magnet <art (AT) unsu (DOT) com> writes:

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

On Dec 9, 11:09*am, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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 --

Tim X <timx (AT) nospam (DOT) dev.null> wrote:
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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."