Summary of long types in Oracle __oracle

Summary of long types in Oracle

1, Long data type is stored in variable-length strings, the maximum length limit is 2GB.
2, for more than a certain length of text, the basic only long type to store, the data dictionary many objects are defined in a long to store.
3, long type is mainly used to do not need to do a string search of the lengthy string of data, if you want to do character search will use the VARCHAR2 type.
4. Many tools, including Sql*plus, are difficult to deal with long data types.
5, LONG data type use, to be limited by the size of the disk.
SQL statement capable of manipulating LONG:
1. SELECT statement
2, the SET statement in the UPDATE statement
3. Values statements in INSERT statements
Limit:
1, a table can contain only a LONG type of column.
2, cannot index long type column.
3. A table containing a long column cannot be clustered.
4, you cannot insert the value of a long column in Sql*plus into another table, such as INSERT INTO ... select.
5, you cannot create a long type column in Sql*plus by querying other tables, such as CREATE table as SELECT.
6. You cannot add constraints to a long column (NULL, NOT NULL, except for default), such as: a keyword column (PRIMARY key) cannot be a long data type.
7. A long type column cannot be used in the following clause of a select: where, group BY, order by, and a SELECT statement with distinct.
8. A long type column cannot be used for a distributed query.
9, the variable of the PL/SQL procedure block cannot be defined as a long type.
10, the Long type column cannot be changed by the SQL function, such as: substr, InStr.
Because a value of type long cannot be inserted with the INSERT INTO ... Select method, we need to insert the other fields first, and then insert the long field, which can be implemented through the procedure. Here is an example of what I did experimentally.
sql>; CREATE TABLE Testlong (ID number,name varchar2 (), history long);
Table has been created.
sql>; CREATE TABLE testlong1 (ID number,name varchar2 (), history long);
Table has been created.
sql>; INSERT into Testlong values (1, ' dwh ', ' work in Foshan ');
1 lines have been created.
sql>; INSERT into Testlong values (2, ' SFG ', ' work in Guangzhou ');
1 lines have been created.
sql>; SELECT * from Testlong;
ID NAME
———- ————————
HISTORY
——————————————————————————–
1 DWH
Work in Foshan
2 SFG
Work in Guangzhou
sql>; INSERT INTO TESTLONG1 select * from Testlong;
INSERT INTO TESTLONG1 select * from Testlong
*
ERROR is on line 1th:
ORA-00997: illegal use of LONG data type
sql>; Declare
2 CURSOR Bcur
3 is SELECT id,history from Testlong;
4 Brec Bcur%rowtype;
5 BEGIN
6 INSERT INTO Testlong1 (id,name) Select Id,name from testlong;– other types
Insert First
7 OPEN Bcur;
8 LOOP
9 FETCH bcur into Brec;
Ten EXIT when Bcur%notfound;
One update testlong1 set history=brec.history where id=brec.id;
End LOOP;
Close bcur;
End;
15/
The PL/SQL process has completed successfully.
sql>; SELECT * from Testlong1;
ID NAME
———- ————————
HISTORY
————————————————————————–
1 DWH
Work in Foshan
2 SFG
Work in Guangzhou
A long column cannot appear in some parts of the SQL statement:
1, in the Where, GROUP by, order BY, CONNECT by clause, and in the SELECT statement with distinct operations.
2, SQL functions (such as substr or instr)
3, expression or condition.
4, a Select table that contains a query with a GROUP BY clause.
5, in the subquery selection table or by the collection Operation combination query selection table.
6. Select Table of CREATE table as SELECT statement.
Instructions for use:
1, Long data type is stored in variable-length strings, the maximum length limit is 2GB.
2, for more than a certain length of text, the basic only long type to store, the data dictionary many objects are defined in a long to store.
3, long type is mainly used to do not need to do a string search of the lengthy string of data, if you want to do character search will use the VARCHAR2 type.
4. Many tools, including Sql*plus, are difficult to deal with long data types.
5, LONG data type use, to be limited by the size of the disk.
SQL statement capable of manipulating LONG:
1. SELECT statement
2, the SET statement in the UPDATE statement
3. Values statements in INSERT statements
Limit:
1, a table can contain only a LONG type of column.
2, cannot index long type column.
3. A table containing a long column cannot be clustered.
4, you cannot insert the value of a long column in Sql*plus into another table, such as INSERT INTO ... select.
5, you cannot create a long type column in Sql*plus by querying other tables, such as CREATE table as SELECT.
6. You cannot add constraints to a long column (NULL, NOT NULL, except for default), such as: a keyword column (PRIMARY key) cannot be a long data type.
7. A long type column cannot be used in the following clause of a select: where, group BY, order by, and a SELECT statement with distinct. 8. A long type column cannot be used for a distributed query.
9, the variable of the PL/SQL procedure block cannot be defined as a long type.
10, the Long type column cannot be changed by the SQL function, such as: substr, InStr.
Insert large text with the Oracle long field
When writing an application, a large amount of inserting large text is required, but Oracle's CLOB is cumbersome to operate, so I have not chosen to use CLOB, but instead have used the long type in previous versions of Oracle [but there are some limitations in the long type that can have only one long field in a table].
At first I inserted the database directly using INSERT INTO table1 values (A,b[long]), but Oracle had some restrictions that one statement could not exceed 4,000 characters and reported ORA-01704 errors.
The solution to finding Oracle documents is to insert a string into the database using the Setcharacterstream () method for fields long.
Code:
sql= INSERT into msg_info values (?,?,?,? [ Long field], ' C ', sysdate,sysdate+ "+msgterm+",?) ";
PSTAT1 = conn.preparestatement (sql);
Pstat1.setlong (1, msg_id);
Pstat1.setint (2, msg_gp_id);
Pstat1.setstring (3, Msg_title);
Pstat1.setcharacterstream (4,new StringReader (msg_info.tostring ()), msg_info.length ());
Conn.commit ();
Pstat1.setlong (5, this.upid);
Article Source: http://www.diybl.com/course/7_databases/oracle/oraclejs/2008827/138116.html