Greetings,

Is there any known case of oracle allowing only one column of LONG
data type in a table? The orcale 8 and 9 does not allow us to create
more than one LONG data type in a particular table.

Why is it so ? Is there any limitation to it ?

Please advice.

Thanks

Uthuras

Uthuras wrote:

> Greetings,
>
> Is there any known case of oracle allowing only one column of LONG
> data type in a table? The orcale 8 and 9 does not allow us to create
> more than one LONG data type in a particular table.
>
> Why is it so ? Is there any limitation to it ?
>
> Please advice.
>
> Thanks
>
> Uthuras

Oracle has clearly stated that LONG is not to be used. It is there
for backward compatibility. So rather than getting stressed over
something you shouldn't be doing ... move to CLOBs.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Uthuras wrote:
> Greetings,
>
> Is there any known case of oracle allowing only one column of LONG
> data type in a table? The orcale 8 and 9 does not allow us to create
> more than one LONG data type in a particular table.
>
> Why is it so ? Is there any limitation to it ?
>
> Please advice.
>
> Thanks
>
> Uthuras

It's been that way for practically ever. It is one of the limitations of
the LONG and LONG RAW data types that you can only have one column of
either in a table... and, because the data is stored in-line with the
rest of the row data, if you've got any sense you make it the last
column of the table definition.

This is precisely one reason why Oracle very, very strongly recommends
that you do NOT use LONG or LONG RAW data types any more. Since version
8.0, there have been CLOB and BLOB data types to replace them (and
NCLOBs if you are using national character data).

With CLOBs, you can have as many of the things in a table as you desire,
they store double the amount of data that a LONG can manage (even more
in 10g), and the data over 4000 bytes is stored out of line, so the
column order doesn't matter. Even under 4000 bytes you can choose to
store out of line if you wish, though it's not the default.

There are all sorts of other restrictions with LONG that largely
disappear with CLOBs, too (such as partitioning issues). There are also
performance issues (CLOBs can be indexed, LONGs can't be; CLOBs can
therefore have randomised access, LONGs are read serially etc etc etc).

In short, you shouldn't be developing new applications with LONGs in
them any more.

Regards
HJR

On 4 May 2004 21:13:09 -0700, uthuras@hotmail.com (Uthuras) wrote:

>Greetings,
>
>Is there any known case of oracle allowing only one column of LONG
>data type in a table? The orcale 8 and 9 does not allow us to create
>more than one LONG data type in a particular table.
>
>Why is it so ? Is there any limitation to it ?
>
>Please advice.
>
>Thanks
>
>Uthuras

This is a known and more importantly *documented* restriction (which
has existed since Oracle 6) and this is why LONGs are *deprecated*.
Please
- use CLOBs instead
- consult the documentation prior to asking doc questions


--
Sybrand Bakker, Senior Oracle DBA

Thanks for all the input guys!

uthuras


Sybrand Bakker <gooiditweg@sybrandb.verwijderdit.demon.nl> wrote in message news:<v9tg90d2c0usjruu03hs6h8500ee6r1q8o@4ax.com>. ..
> On 4 May 2004 21:13:09 -0700, uthuras@hotmail.com (Uthuras) wrote:
>
> >Greetings,
> >
> >Is there any known case of oracle allowing only one column of LONG
> >data type in a table? The orcale 8 and 9 does not allow us to create
> >more than one LONG data type in a particular table.
> >
> >Why is it so ? Is there any limitation to it ?
> >
> >Please advice.
> >
> >Thanks
> >
> >Uthuras
>
> This is a known and more importantly *documented* restriction (which
> has existed since Oracle 6) and this is why LONGs are *deprecated*.
> Please
> - use CLOBs instead
> - consult the documentation prior to asking doc questions

BTW, how about Oracles´ own tables containing LONGs ?
In 8i (8.1.7.3 - yaya, old version, but still in service widely), I find a
lot of them:

SQL> select table_name from dba_tab_columns where data_type = 'LONG';

TABLE_NAME
------------------------------
ALL_ARGUMENTS
ALL_CLUSTER_HASH_EXPRESSIONS
ALL_CONSTRAINTS
ALL_IND_EXPRESSIONS
ALL_IND_PARTITIONS
ALL_MVIEWS
ALL_MVIEW_AGGREGATES
ALL_MVIEW_ANALYSIS
ALL_REGISTERED_SNAPSHOTS
ALL_SNAPSHOTS
ALL_SUMMARIES
ALL_SUMMARY_AGGREGATES
ALL_TAB_COLUMNS
ALL_TAB_PARTITIONS
ALL_TRIGGERS
ALL_VIEWS
ARGUMENT$
CDEF$
COL
COL$
DBA_CLUSTER_HASH_EXPRESSIONS
DBA_CONSTRAINTS
DBA_IND_EXPRESSIONS
DBA_IND_PARTITIONS
DBA_MVIEWS
DBA_MVIEW_AGGREGATES
DBA_MVIEW_ANALYSIS
DBA_OUTLINES
DBA_REGISTERED_SNAPSHOTS
DBA_SNAPSHOTS
DBA_SUMMARIES
DBA_SUMMARY_AGGREGATES
DBA_TAB_COLUMNS
DBA_TAB_PARTITIONS
DBA_TRIGGERS
DBA_VIEWS
DIM$
EXU7CLU
EXU7CLUC
EXU7CLUI
EXU7CLUU
EXU7CON
EXU7CONU
EXU7SNAP
EXU7SNAPC
EXU7SNAPI
EXU7SNAPU
EXU7TGR
EXU7TGRC
EXU7TGRI
EXU7TGRIC
EXU7TGRU
EXU7VEW
EXU7VEWC
EXU7VEWI
EXU7VEWU
EXU816TGR

TABLE_NAME
------------------------------
EXU816TGRC
EXU816TGRI
EXU816TGRIC
EXU816TGRU
EXU81IXCP
EXU81IXCPU
EXU81SNAP
EXU81SNAPC
EXU81SNAPI
EXU81SNAPU
EXU81SPOK
EXU81SPOKI
EXU81SPOKIU
EXU81SPOKU
EXU81TBCP
EXU81TBCPU
EXU81TGR
EXU81TGRC
EXU81TGRI
EXU81TGRIC
EXU81TGRU
EXU8CLU
EXU8CLUC
EXU8CLUI
EXU8CLUU
EXU8COE
EXU8COEU
EXU8COL
EXU8COLU
EXU8CON
EXU8CONU
EXU8COO
EXU8COOU
EXU8CSN
EXU8CSNU
EXU8DIM
EXU8DIMC
EXU8DIMI
EXU8ICO
EXU8ICOU
EXU8IXP
EXU8IXPU
EXU8POK
EXU8POKI
EXU8POKIU
EXU8POKU
EXU8SNAP
EXU8SNAPC
EXU8SNAPI
EXU8SNAPU
EXU8TBP
EXU8TBPU
EXU8TGR
EXU8TGRC
EXU8TGRI
EXU8TGRIC
EXU8TGRU

TABLE_NAME
------------------------------
EXU8VEW
EXU8VEWC
EXU8VEWI
EXU8VEWU
EXU8VINF
EXU8VINFC
EXU8VINFI
EXU8VINFU
EXU8VNC
EXU8VNCC
EXU8VNCI
EXU8VNCU
HS$_EXTERNAL_OBJECTS
HS_EXTERNAL_OBJECTS
IDL_CHAR$
INDCOMPART$
INDPART$
ORA_KGLR7_IDL_CHAR
PLAN_TABLE
REG_SNAP$
REPCAT$_CDEF
SNAP$
SNAP_REFOP$
SQLAB_DBA_CONSTRAINTS
SUM$
SUMAGG$
SUMKEY$
TABCOMPART$
TABPART$
TRIGGER$
TRIGGERJAVAC$
TRIGGERJAVAM$
TRIGGERJAVAS$
TYPED_VIEW$
USER_ARGUMENTS
USER_CLUSTER_HASH_EXPRESSIONS
USER_CONSTRAINTS
USER_IND_EXPRESSIONS
USER_IND_PARTITIONS
USER_MVIEWS
USER_MVIEW_AGGREGATES
USER_MVIEW_ANALYSIS
USER_OUTLINES
USER_REGISTERED_SNAPSHOTS
USER_SNAPSHOTS
USER_SUMMARIES
USER_SUMMARY_AGGREGATES
USER_TAB_COLUMNS
USER_TAB_PARTITIONS
USER_TRIGGERS
USER_VIEWS
VIEW$
PRODUCT_PRIVS
SMP_LONG_TEXT
SMP_LONG_TEXT_
SQLPLUS_PRODUCT_PROFILE
AUGAUF (OK, some are here from an older user schema, but most are DDs)

TABLE_NAME
------------------------------
AUGINF
AUGKAR
AUGKUW
AUGPOS
AUGPOT
AUGTAK
AUGTER
HILFEN
PLAN_TABLE
TERMIN
PLAN_TABLE
PLAN_TABLE
SMP_LONG_TEXT
SMP_LONG_TEXT_
PLAN_TABLE
PLAN_TABLE
PLAN_TABLE
TOAD_PLAN_TABLE
EMBARCADERO_EXPLAIN_PLAN
PLAN_TABLE
OL$
PLAN_TABLE

193 Zeilen ausgewählt.

That is quiet a lot, isn´t it ?

SQL>
SQL> desc all_arguments
Name Null? Typ
------------------------------- -------- ----
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OVERLOAD VARCHAR2(40)
ARGUMENT_NAME VARCHAR2(30)
POSITION NOT NULL NUMBER
SEQUENCE NOT NULL NUMBER
DATA_LEVEL NOT NULL NUMBER
DATA_TYPE VARCHAR2(30)
DEFAULT_VALUE LONG ******
in the middle of the definition, not at the end as suggested by
another poster.
DEFAULT_LENGTH NUMBER
IN_OUT VARCHAR2(9)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
RADIX NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
TYPE_OWNER VARCHAR2(30)
TYPE_NAME VARCHAR2(30)
TYPE_SUBNAME VARCHAR2(30)
TYPE_LINK VARCHAR2(128)
PLS_TYPE VARCHAR2(30)

SQL>

Has it all changed or gone with 9i / 10g ?

Just curious,
Jan

Jan Gelbrich wrote:
> BTW, how about Oracles´ own tables containing LONGs ?
> In 8i (8.1.7.3 - yaya, old version, but still in service widely), I find a
> lot of them:
>
> SQL> select table_name from dba_tab_columns where data_type = 'LONG';

[Almighty Snip]

> 193 Zeilen ausgewählt.

Anything wrong with doing a count(table_name) and saving us all some
bandwidth?

> That is quiet a lot, isn´t it ?

Yes, it is quite a lot.

[snip]

> in the middle of the definition, not at the end as suggested by
> another poster.

You are allowed to actually remember the names of people who take the
trouble to reply to you, you know! In any case, I didn't "suggest" it,
but said that because of the in-line storage of LONGs it was sensible to
shove them to the end of the definition -which is merely reporting a
suggestion made by Oracle themselves (though clearly not one they
*adopt* themselves).

> Has it all changed or gone with 9i / 10g ?

No, it's still there in 9i and 10g. Indeed, there have been some
enhancements to LONG functionality in 10g (it's a newly-supported data
type in streams, for example).

But so what? Oracle breaks its own advice, and we're supposed to do
likewise?? Oracle does all sorts of 'strange' things that you'd never do
yourself (take a look at the defaults on SYSTEM tablespace, for example;
or ask why there is still a good old-fashioned SYSTEM rollback segment
when we're all supposed to be using undo tablespaces and automatic
undo). What Oracle does with its data dictionary is up to it, basically,
though I have no doubt that there is, or will be, a project underway to
gradually convert the data dictionary to CLOB technology. However, given
the size of the data dictionary, its crucial importance to a
properly-functioning database and its complexity, that's a massive
undertaking, and not one they would implement lightly.

Oracle, in other words, are stuck with LONGs because the DD has been
around for ages. A bit like MS being saddled with DOS substrates in
their consumer operating systems for so long when they really wanted
everyone to move to NT-type technology (and finally got them to do so
with XP). If you are developing a new application/database, you are not
constrained by such a history, and have the choice of adopting a
technology which Oracle has publicly deprecated and which comes with
major size and functionality limitations, or a technology which doesn't
come with those problems. I know which one I'd be choosing!

Regards
HJR

Howard J. Rogers wrote:
> Jan Gelbrich wrote:
>
>> BTW, how about Oracles´ own tables containing LONGs ?
>> In 8i (8.1.7.3 - yaya, old version, but still in service widely), I
>> find a
>> lot of them:
>>
>> SQL> select table_name from dba_tab_columns where data_type = 'LONG';
>
>
> [Almighty Snip]
>
>> 193 Zeilen ausgewählt.
>
>
> Anything wrong with doing a count(table_name) and saving us all some
> bandwidth?

In addition, not all reported columns actually are in a real table, but
are some Data Dictionary Views.

The following sql ( not to be used in production systems) shows this clearly
(or I made a silly mistake and will gladly receive any corrections):

select dtc.table_name,
column_name,
column_id,
max_id,
decode (column_id, max_id, 'LAST', 'MIDDLE') position
from
(select table_name from dba_tables where owner = 'SYS') dt,
(select table_name, column_name, column_id from dba_tab_columns where data_type = 'LONG') dtc,
(select table_name, max(column_id) max_id from dba_tab_columns group by table_name) dtm
where dt.table_name = dtc.table_name
and dtc.table_name = dtm.table_name

returns on my 10g test environment 34 lines, 31 reporting the long column somewhere in the middle.

Given the number of objects in the DD, I'd hardly take this as an advice or hint that longs are
to be used in any current application. In fact, a small variation of the query give 30 tables
with CLOBs, so the transition to CLOBs instead them already started.

I'm in no position to comment on the usage of longs within the Data Dictionary, but personally I
assume that they are still there for a reason, however the nature of that reason we can only
speculate.

Cheers,

Holger

"Jan Gelbrich" <j_gelbrich@westfalen-blatt.de> wrote in message news:<c7cnoo$28otu$1@ID-93924.news.uni-berlin.de>...
> BTW, how about Oracles´ own tables containing LONGs ?
> In 8i (8.1.7.3 - yaya, old version, but still in service widely), I find a
> lot of them:
>
> SQL> select table_name from dba_tab_columns where data_type = 'LONG';
> 193 Zeilen ausgewählt.
>
> That is quiet a lot, isn´t it ?
> Has it all changed or gone with 9i / 10g ?

Nope, Oracle 9.2.0.5 - 216 and Oracle 10G - 210. However, it seems to
me like LONG are no longer heading for disposal in Oracle databases.
Maybe they will have a second chance.

--
Dusan Bolek

> > in the middle of the definition, not at the end as suggested by
> > another poster.
>
> You are allowed to actually remember the names of people who take the
> trouble to reply to you, you know! In any case, I didn't "suggest" it,
> but said that because of the in-line storage of LONGs it was sensible to
> shove them to the end of the definition -which is merely reporting a
> suggestion made by Oracle themselves (though clearly not one they
> *adopt* themselves).
>

You guys realize, of course, that Oracle always will store long
columns at the physical end of the row, no matter where you place it
in the definition.

SVRMGR> CREATE TABLE example1(c1 number, l1 long);
Statement processed.
SVRMGR> CREATE TABLE example2(l1 long, c1 number);
Statement processed.
SVRMGR>
SVRMGR> select o.name || '.' || c.name name
2> , col# logical
3> , segcol# physical
4> from obj$ o, col$ c
5> where o.name like 'EXAMPLE%'
6> and o.obj# = c.obj#
7> order by 1,2,3
8> ;
NAME LOGICAL PHYSICAL
--------------- -------- --------
EXAMPLE1.C1 1 1
EXAMPLE1.L1 2 2
EXAMPLE2.C1 2 1
EXAMPLE2.L1 1 2
4 rows selected.

If you are interested in more information about the physical layout of
data in tables and indices, please come and look at our graphical
block browser for Oracle at www.tlingua.com.
Thanks,
SCott.