Re: PL*SQL Long Datatype Restrictions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Sep 1999 13:27:07 GMT
Message-ID: <37d07a18.62265683@newshost.us.oracle.com>

A copy of this was sent to hornmule_at_my-deja.com (if that email address didn't require changing) On Thu, 02 Sep 1999 12:36:38 GMT, you wrote:

>Long Datatypes -- Selecting and inserting into
>new table -- PL/SQL
>
>I am currently having the following issue...
>
>I have a table that contains a long datatype and
>a few other fields. I now want to concatenate
>the long datatype and two other fields into one
>long string and insert it into new table as one
>field. When I do this in PL/SQL, Oracle will
>insert all fields that are less than 2,000
>characters
>(Oracle V7 limit for strings) but will fail on
>all others. I am getting error messages(see
>below)
>although I am defining the variables as longs (I
>also tried varchar2(32767). If anyone has a
>workaround for this it would be appreciated.
>

need to see an example. plsql can easily do strings upto 32k in/out of longs -- not 2000 bytes at all.

Here is an example in a 7.3 database that does what you describe:

tkyte_at_ORACLE73.WORLD> create table t ( a varchar2(25), b varchar(25), c long );

Table created.

tkyte_at_ORACLE73.WORLD>
tkyte_at_ORACLE73.WORLD> declare
  2 l_tmp long;
  3 begin

  4          l_tmp := rpad( '*', 30000, '*' );
  5          insert into  t values ( 'Hello', 'World', l_tmp );
  6          commit;

  7 end;
  8 /

PL/SQL procedure successfully completed.

tkyte_at_ORACLE73.WORLD>
tkyte_at_ORACLE73.WORLD> begin

  2          for x in ( select * from t ) loop
  3                  dbms_output.put_line( 'Long length = ' || length( x.c ) );
  4          end loop;

  5 end;
  6 /
Long length = 30000

PL/SQL procedure successfully completed.

tkyte_at_ORACLE73.WORLD>
tkyte_at_ORACLE73.WORLD> begin

  2          for x in ( select * from t ) loop
  3                  x.c := x.a || ' ' || x.b || ' ' || x.c;
  4                  insert into t values ( null, null, x.c );
  5          end loop;
  6          commit;

  7 end;
  8 /

PL/SQL procedure successfully completed.

tkyte_at_ORACLE73.WORLD>
tkyte_at_ORACLE73.WORLD> begin

  2          for x in ( select * from t ) loop
  3                  dbms_output.put_line( 'Long length = ' || length( x.c ) );
  4          end loop;

  5 end;
  6 /
Long length = 30000
Long length = 30012

PL/SQL procedure successfully completed.

tkyte_at_ORACLE73.WORLD>

>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 02 1999 - 08:27:07 CDT