Field Length.

[EM1107]

Good day!

I have a problem and I would like to know the cause.
I am new to oracle and I was asked to create a field that insert data in it.

The field type I selected is LONG. When reading on the field type it was mentioned that field could take more than 5000 caracter.

When I try to insert information in my field it only allow insert for information that is less than 2000 caracter.

What am'I doing wrong?

Is there a reason for that. Is there a setting that I should be changing so the field be able to accept more than 2K caracter?

I have tryied to change that and use CLOB and it does the same.

 

[SantaMufasa]

EM,

Oracle is deprecating (de-supporting) LONG column types. You should use CLOB (Character Large OBjects) for columns that exceed 4000 characters. Varchar2 columns can accommodate up to that length:

select length(x) from em1;

 LENGTH(X)
----------
      4000

alter table em modify x varchar(4001);
                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

So, the above test shows that 4000 (not 4001) is the maximum length for a varchar2 column.

Now, for CLOB columns:

create table em2 (x clob);

Table created.

(I inserted a 64,000-character string.)

select length(x) from em2;

 LENGTH(X)
----------
     64000

Now, if/when you try, from SQL*Plus, to display the contents of a CLOB column, you must tell SQL*Plus how to display the results. For example, you must tell SQL*Plus to expand the length of LONG columns from its default display length of 80 columns to whatever you choose for the new length:

SQL> SQL> show long
long 80
SQL> set long 1000000
SQL> show long
long 1000000

Also, your SQL*Plus LINESIZE and other settings may need tweaking to get the appearance for which you are looking.

Let us know if you have additional questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[EM1107]

Thanks for the info.

Now I would like to know, what is the best way to insert data in a CLOB field using the regular query tool?

 

[jimirvine]

The same way as you would insert into a varchar2 field.
HTH

 

[SantaMufasa]

In addition to using a standard SQL INSERT statement:

INSERT into em2 values ('<enter value here>');

...you may find additional functionality in Oracle-supplied procedures for handling large-object manipulations. You can read up on them directly by issuing the following query:

select text from dba_source
where name = 'DBMS_LOB'
  and rownum <=  447;

Let us know if that query is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]