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.
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 Long value 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's an example of what I've done 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
The work in Guangzhou 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 number of inserts are required, but Oracle's CLOB are cumbersome to operate, and instead of using CLOB, I have used the long type in previous versions of Oracle [but the long type has some limitations, You can have only one long field in a table.
At first I insert the database directly using INSERT INTO table1 values (A,b[long]), but Oracle has some restrictions that one statement cannot exceed 4,000 characters and reports ORA-01704 errors.
Finding Oracle Documents finds a workaround, which 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);
