Question on LONG datatype [message #625311] |
Sat, 04 October 2014 23:08 |
|
andrewscharles89
Messages: 25 Registered: July 2014
|
Junior Member |
|
|
Guidelines of LONG columns,from SQL Fundamentals I
1)A LONG column is not copied when a table is created using a subquery.
2)A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
3)Only one LONG column can be used per table.
4)No constraints can be defined on a LONG column.
Here,I agree with 1,2 and 3 points.
To prove 4) is wrong
Test case
create table t3
(
col1 long
);
insert into t3 values (1);
--1 row inserted--
alter table t3 modify col1 not null;
--table altered--
insert into t3 values (null);
--ORA-01442: column to be modified to NOT NULL is already NOT NULL--
Which means option 4) is wrong,I can define NOT NULL constraint on LONG column
In case if someone says,NOT NULL constraint cant be defined on a column where null exists
create table t4
(
col1 long
);
insert into t4 values (1);
--1 row inserted--
insert into t4 values (null);
--1 row inserted--
alter table t4 modify col1 not null enable novalidate;
--table altered--
Please clarify on option 4)
[Updated on: Sat, 04 October 2014 23:14] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Question on LONG datatype [message #625317 is a reply to message #625311] |
Sun, 05 October 2014 01:27 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
andrewscharles89 wrote on Sun, 05 October 2014 05:08Guidelines of LONG columns,from SQL Fundamentals I
1)A LONG column is not copied when a table is created using a subquery.
2)A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
3)Only one LONG column can be used per table.
4)No constraints can be defined on a LONG column.
<snip>
You have omitted the fifth bullet from the list on the page of the Oracle Uni courseware to which you refer:Quote: You might want to use a CLOB column rather than a LONG column.
|
|
|
|
|
Re: Question on LONG datatype [message #625321 is a reply to message #625318] |
Sun, 05 October 2014 03:39 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Andrew, your problem solving technique is better than most: you do test things. Do you trust the result of your test?
If you want to take this further, you could try defining a check or a unique constraint on a long column. You could try indexing it. Then you could think about whether any impossibilities are a limitation in technology and therefore could never be implemented, or whether they are merely impractical and therefore unimplemented by design.
|
|
|
Re: Question on LONG datatype [message #625323 is a reply to message #625315] |
Sun, 05 October 2014 06:42 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
andrewscharles89 wrote on Sun, 05 October 2014 02:09all the above points are also listed in the book Oracle Database 11g:SQL fundamentals I, Volume 2 page no 81 chapter 10
Well, then you should ask your instructor or Oracle University. Oracle documentation LONG Data Type clearly states:
•LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
And, btw, in earlier versions (e.g. 8i) it simply stated:
•LONG columns cannot appear in integrity constraints.
Maybe Oracle University never corrected SQL fundamentals since.
SY.
[Updated on: Sun, 05 October 2014 06:45] Report message to a moderator
|
|
|