General discussion


Create functions to join and split strings in SQL

By MaryWeilage Editor ·
This week's Oracle e-newsletter describes how to create functions to join and split strings in SQL. Will you use the tips in this e-newsletter in your development work? If not, what topics would you like Scott Stephens to cover in future editions of this e-newsletter?

If you aren't subscribed to the free Oracle e-newsletter, click the following link to automatically sign up:

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Clarification Requested

by avia In reply to Create functions to join ...

According to the "Oracle 9i SQL Reference" the maximum size for the VARCHAR2 data-type is 4,000 -- and not 32,767 as defined in the posted procedure.

However, I can define such a variable in PL/SQL:

v1 varchar2(32767);

But not as a table column:

SQL> create table t (c varchar2(32767));
create table t (c varchar2(32767))
ERROR at line 1:
ORA-00**0: specified length too long for its datatype

I am using Oracle on SUN [sparc] Solaris 9.


Collapse -

4000 is only for storage in a database table...

by swstephe In reply to Clarification Requested

You can only store 4000 characters in a database VARCHAR2 column, however, PL/SQL allows the 32767 limit, and SQL allows you to pass around that amount and much more. Try this statement on for size:

select lpad('x',40000) from dual;

Since it is a PL/SQL procedure, I let you use the largest string possible to avoid the limits. If you were to write this procedure in Java or C, you could probably go beyond this limit too.

Related Discussions

Related Forums