Do I need to create a field type LONG?

x · Jun 27 '08, 06:19 PM

Re: Do I need to create a field type LONG?

"Galina" <galkas@mail.ru wrote in message
news:ecdc865.04 04290204.79ae76 df@posting.goog le.com...

Hello
I am going to copy a data from MS Access table into Oracle table. One
of fields is memo type and data in this field range from 1 character
to 551 long string. Do I need to create a field type LONG in the
Oracle table to accommodate this data? If not, what field type would
suit best?
Any help will be greatly appreciated.
Thank you.
Galina

varchar2 should be enough, iz can store up to 4000 characters.

**Hans Forbrich** · Jun 27 '08, 06:19 PM

Re: Do I need to create a field type LONG?

Galina wrote:

Hello
I am going to copy a data from MS Access table into Oracle table. One
of fields is memo type and data in this field range from 1 character
to 551 long string. Do I need to create a field type LONG in the
Oracle table to accommodate this data? If not, what field type would
suit best?
Any help will be greatly appreciated.
Thank you.
Galina

You want type VARCHAR2.

Since Oracle 8.0 there are limited, if any, circumstances that justify LONG.
If the limit of 4000 bytes in VARCHAR2 is not enough, switch to the LOB
family (eg: CLOB) not the LONG family.

Check out Oracle's online documentation at http://docs.oracle.com for more.

/Hans

**Galina** · Jun 27 '08, 06:20 PM

Re: Do I need to create a field type LONG?

Thank you both for answering. I have already re-created the table with
the field type Varchar2 length 1000.
Simply for curiosity, when I copy a string shorter then 1000 into this
field, will Oracle use memory necessary to store the string, or to
store 1000 characters long string? Sorry for silly question, I
understand that I could have found it somewhere...
Galina
galkas@mail.ru (Galina) wrote in message news:<ecdc865.0 404290204.79ae7 6df@posting.goo gle.com>...

Hello
I am going to copy a data from MS Access table into Oracle table. One
of fields is memo type and data in this field range from 1 character
to 551 long string. Do I need to create a field type LONG in the
Oracle table to accommodate this data? If not, what field type would
suit best?
Any help will be greatly appreciated.
Thank you.
Galina

**Jim Kennedy** · Jun 27 '08, 06:20 PM

Re: Do I need to create a field type LONG?

"Galina" <galkas@mail.ru wrote in message
news:ecdc865.04 05040457.25b3d4 8a@posting.goog le.com...

Thank you both for answering. I have already re-created the table with
the field type Varchar2 length 1000.
Simply for curiosity, when I copy a string shorter then 1000 into this
field, will Oracle use memory necessary to store the string, or to
store 1000 characters long string? Sorry for silly question, I
understand that I could have found it somewhere...
Galina
galkas@mail.ru (Galina) wrote in message

news:<ecdc865.0 404290204.79ae7 6df@posting.goo gle.com>...

Hello
I am going to copy a data from MS Access table into Oracle table. One
of fields is memo type and data in this field range from 1 character
to 551 long string. Do I need to create a field type LONG in the
Oracle table to accommodate this data? If not, what field type would
suit best?
Any help will be greatly appreciated.
Thank you.
Galina

It stores the string as is. It does not pad the string. So if the string
is of length 2 then it takes up 2 (plus one for the column delimiter)
Jim

**Galina** · Jun 27 '08, 06:20 PM

Re: Do I need to create a field type LONG?

Hi Jim
Thank you for your answer. Of course, it should be this way. Any
respectable database stores strings allocating disk space as much as
necessary and not more. If Access does so, how can it be different
with Oracle, which is much more superiour!
Yesterday I copied my table from Access to Oracle with that field,
which can be any length, set as Varchar2 1000. I was very baffled to
find that the tablespace lost after copying about 70 MBt of space.
Previously, when I set up the field type long and wizard didn't allow
me to enter initial allocations, the tablespace after copying was
about 8 MBt less.
I even doubted for a short while, that your answer was right. But then
I decided that Oracle allocated all this space to my table, because
every record might have have 1000 character long string, but didn't
use it and new records will be added into the same allocated space.
Which means, that for a while the tablespace will not grow, despite
records being added. We shall see. The performance, if the field is
set as Varchar2, is noticeably better then when it was long.
Galina

"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message news:<nmNlc.209 01$IG1.865525@a ttbi_s04>...

"Galina" <galkas@mail.ru wrote in message
news:ecdc865.04 05040457.25b3d4 8a@posting.goog le.com...

Thank you both for answering. I have already re-created the table with
the field type Varchar2 length 1000.
Simply for curiosity, when I copy a string shorter then 1000 into this
field, will Oracle use memory necessary to store the string, or to
store 1000 characters long string? Sorry for silly question, I
understand that I could have found it somewhere...
Galina
galkas@mail.ru (Galina) wrote in message

news:<ecdc865.0 404290204.79ae7 6df@posting.goo gle.com>...

Hello
I am going to copy a data from MS Access table into Oracle table. One
of fields is memo type and data in this field range from 1 character
to 551 long string. Do I need to create a field type LONG in the
Oracle table to accommodate this data? If not, what field type would
suit best?
Any help will be greatly appreciated.
Thank you.
Galina

It stores the string as is. It does not pad the string. So if the string
is of length 2 then it takes up 2 (plus one for the column delimiter)
Jim

**Sharkie** · Jun 27 '08, 06:20 PM

Re: Do I need to create a field type LONG?

galkas@mail.ru (Galina) wrote in message news:<ecdc865.0 405052359.1dd8e 62b@posting.goo gle.com>...

Hi Jim
Thank you for your answer. Of course, it should be this way. Any
respectable database stores strings allocating disk space as much as
necessary and not more.

That's why it's called varchar ==variable character length.
char data type on the other hand will pad your string up to specified
length, see example:

SQLcreate table temp (varname varchar2(20), fixname char(20));
Table created.

SQLinsert into temp values ('short string', 'short string');
1 row created.

SQLinsert into temp values ('longer string', 'longer string');
1 row created.

SQLselect '|'||varname||' |' "varname" from temp;

varname
----------------------
|short string|
|longer string|

SQLselect '|'||fixname||' |' "fixname" from temp;

fixname
----------------------
|short string |
|longer string |

**Galina** · Jun 27 '08, 06:21 PM

Re: Do I need to create a field type LONG?

Thank you., especially for taking your time to explain the difference
with the example. Not that I see a need for fixed length string at the
moment, but I am sure in some situations it would be needed.
Galina
sharkdba@yahoo. com (Sharkie) wrote in message news:<423b5ab1. 0405071248.10c7 36a1@posting.go ogle.com>...

galkas@mail.ru (Galina) wrote in message news:<ecdc865.0 405052359.1dd8e 62b@posting.goo gle.com>...

Hi Jim
Thank you for your answer. Of course, it should be this way. Any
respectable database stores strings allocating disk space as much as
necessary and not more.

>
That's why it's called varchar ==variable character length.
char data type on the other hand will pad your string up to specified
length, see example:
>
SQLcreate table temp (varname varchar2(20), fixname char(20));
Table created.
>
SQLinsert into temp values ('short string', 'short string');
1 row created.
>
SQLinsert into temp values ('longer string', 'longer string');
1 row created.
>
SQLselect '|'||varname||' |' "varname" from temp;
>
varname
----------------------
|short string|
|longer string|
>
SQLselect '|'||fixname||' |' "fixname" from temp;
>
fixname
----------------------
|short string |
|longer string |

Do I need to create a field type LONG?

Do I need to create a field type LONG?

Comment

Comment

Comment

Comment

Comment

Comment

Comment