473,324 Members | 2,246 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Do I need to create a field type LONG?

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
Jul 19 '05 #1
7 6752
x
"Galina" <ga****@mail.ru> wrote in message
news:ec*************************@posting.google.co m...
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.
Jul 19 '05 #2
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
Jul 19 '05 #3
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
ga****@mail.ru (Galina) wrote in message news:<ec*************************@posting.google.c om>...
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

Jul 19 '05 #4

"Galina" <ga****@mail.ru> wrote in message
news:ec*************************@posting.google.co m...
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
ga****@mail.ru (Galina) wrote in message

news:<ec*************************@posting.google.c om>...
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
Jul 19 '05 #5
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" <ke****************************@attbi.net> wrote in message news:<nmNlc.20901$IG1.865525@attbi_s04>...
"Galina" <ga****@mail.ru> wrote in message
news:ec*************************@posting.google.co m...
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
ga****@mail.ru (Galina) wrote in message

news:<ec*************************@posting.google.c om>...
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

Jul 19 '05 #6
ga****@mail.ru (Galina) wrote in message news:<ec*************************@posting.google.c om>...
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:

SQL> create table temp (varname varchar2(20), fixname char(20));
Table created.

SQL> insert into temp values ('short string', 'short string');
1 row created.

SQL> insert into temp values ('longer string', 'longer string');
1 row created.

SQL> select '|'||varname||'|' "varname" from temp;

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

SQL> select '|'||fixname||'|' "fixname" from temp;

fixname
----------------------
|short string |
|longer string |
Jul 19 '05 #7
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
sh******@yahoo.com (Sharkie) wrote in message news:<42**************************@posting.google. com>...
ga****@mail.ru (Galina) wrote in message news:<ec*************************@posting.google.c om>...
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:

SQL> create table temp (varname varchar2(20), fixname char(20));
Table created.

SQL> insert into temp values ('short string', 'short string');
1 row created.

SQL> insert into temp values ('longer string', 'longer string');
1 row created.

SQL> select '|'||varname||'|' "varname" from temp;

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

SQL> select '|'||fixname||'|' "fixname" from temp;

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

Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mike Button | last post by:
Hello all, I am really really desperate on what I should do, and I am asking for help from anyone in this newsgroup, here's the situation: I am creating a form that is being run on a server...
9
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then...
3
by: CSDunn | last post by:
Hello, I have 14 fields on a report that hold integer values. The field names use the following naming convention: T1Number, T2Number ....T14Number. I need to get a 'sub total' of all fields as...
7
by: Jack Addington | last post by:
I've got a fairly simple application implementation that over time is going to get a lot bigger. I'm really trying to implement it in a way that will facilitate the growth. I am first writing a...
2
by: Greg Strong | last post by:
Hello All, Is it possible to change table field lookup properties in code? I've been able to change other field properties in code, however so far no luck with field lookup properties. What...
9
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you can't reopen database after exiting as a result...
7
by: Rnykster | last post by:
I know a little about Access and have made several single table databases. Been struggling for about a month to do a multiple table database with no success. Help! There are two tables. First...
7
by: Galina | last post by:
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...
9
by: pereges | last post by:
Hello I need some ideas for designing a recursive function for my ray tracing program. The idea behind ray tracing is to follow the electromagnetic rays from the source, as they hit the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.