1Z0-051 QUESTION 21 LONG数据类型应该注意的地方

QUESTION 21
Examine the description of the EMP_DETAILS table given below:
name NULL TYPE
EMP_ID NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2 (40)
EMP_IMAGE LONG
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table?
(Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause.
B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
C. You cannot add a new column to the table with LONG as the data type.

D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.

答案：BC（但是觉得答案是错的，应该是BCD，可以参看解析）

解析：

有关LONG的官方文档为：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF00201

应该注意的如下：

You can reference LONG columns in SQL statements in these places:

• SELECT lists

• SET clauses of UPDATE statements

• VALUES clauses of INSERT statements

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.

• You cannot create an object type with a LONG attribute.

• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

• LONG columns cannot be indexed.

• LONG data cannot be specified in regular expressions.

• A stored function cannot return a LONG value.

• You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.

• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.

• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

• The UNIQUE operator of a SELECT statement

• The column list of a CREATE CLUSTER statement

• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

• SQL built-in functions, expressions, or conditions

• SELECT lists of queries containing GROUP BY clauses

• SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators

• SELECT lists of CREATE TABLE ... AS SELECT statements

• ALTER TABLE ... MOVE statements

• SELECT lists in subqueries in INSERT statements

Triggers can use the LONG data type in the following manner:

• A SQL statement within a trigger can insert data into a LONG column.

• If data from a LONG column can be converted to a constrained data type (such as CHAR and VARCHAR2), then a LONG column can be referenced in a SQL statement within a trigger.

• Variables in triggers cannot be declared using the LONG data type.

• :NEW and :OLD cannot be used with LONG columns.

You can use Oracle Call Interface functions to retrieve a portion of a LONG value from the database.

SQL> create table emp_details (
  2  emp_id number not null,
  3  emp_name varchar2(40) not null,
  4  emp_image LONG);

Table created.
--A选项错，LONG类型的列不能出现在GROUP BY语句中，B选项对
SQL> select emp_image from emp_details group by emp_image;
select emp_image from emp_details group by emp_image
                                           *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

--C选项对，一个表中不能出现两个LONG类型的字段
SQL> alter table emp_details add (emp_file long);
alter table emp_details add (emp_file long)
                             *
ERROR at line 1:
ORA-01754: a table may contain only one column of type LONG

--D选项也应该对，能够修改为not null约束
SQL> alter table emp_details modify emp_image not null;

Table altered.

SQL> desc emp_details;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------

 EMP_ID                                    NOT NULL NUMBER
 EMP_NAME                                  NOT NULL VARCHAR2(40)
 EMP_IMAGE                                 NOT NULL LONG