Application of Oracle long Fields

1. The LONG data type stores variable-length strings with a maximum length limit of 2 GB. 2. For text that exceeds a certain length, the LONG type can only be used for storage.

1. The LONG data type stores variable-length strings with a maximum length limit of 2 GB. 2. For text that exceeds a certain length, the LONG type can only be used for storage.

1. The LONG data type stores variable-length strings with a maximum length limit of 2 GB.

2. For text that exceeds a certain length, the LONG type can only be used for storage. Many objects in the data dictionary are defined as LONG.

3. The LONG type is mainly used for LONG string data that does not need to be searched for strings. To search for characters, the varchar2 type is required.

4. Many tools, including SQL * Plus, are difficult to process LONG data types.

5. Use of the LONG data type depends on the disk size.

SQL statements that can operate on LONG:

1. Select statement

2. SET statement in Update statement

3. VALUES statement in the Insert statement

Restrictions:

1. A table can only contain one LONG column.

2. LONG Columns cannot be indexed.

3. tables containing LONG Columns cannot be clustered.

4. Values of LONG Columns cannot be inserted into another table in SQL * Plus, such as insert... Select.

5. You cannot create LONG columns by querying other tables in SQL * Plus, for example, create table as select.

6. Constraints (except NULL, not null, and DEFAULT) cannot be added to LONG columns. For example, a KEY column cannot be of the LONG data type.

7. LONG Columns cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct.

8. LONG Columns cannot be used for distributed query.

9. PL/SQL process block variables cannot be defined as LONG.

10. LONG Columns cannot be changed by SQL functions, such as substr and instr.

Because the values of the long type cannot use insert... Select method insert, so we need to take two steps, First insert other fields, and then insert the long type field, which can be achieved through the process. the following is an example of my experiment implementation.

SQL>; create table testlong (id number, name varchar2 (12), history long );

The table has been created.

SQL>; create table testlong1 (id number, name varchar2 (12), history long );

The table has been created.

SQL>; insert into testlong values (1, 'tslxg', 'work in tangshan ');

One row has been created.
SQL>; insert into testlong values (2, 'makee', 'work in beiing ');

One row has been created.

SQL>; select * from testlong;

ID NAME
------------
HISTORY
---------------------------

1 tslxg
Work in tangshan

2 imakee
Work in beijing

SQL>; insert into testlong1 select * from testlong;
Insert into testlong1 select * from testlong
*
ERROR is located in row 1st:
ORA-00997: Invalid 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;
10 exit when bcur % NOTFOUND;
11 update testlong1 set history = brec. history where id = brec. id;
12 end loop;
13 CLOSE bcur;
14 END;
15/

The PL/SQL process is successfully completed.

SQL>; select * from testlong1;

ID NAME
------------
HISTORY
-------------------------

1 tslxg
Work in tangshan

2 imakee
Work in beijing

LONG Columns cannot appear in some parts of SQL statements:
1. In the WHERE, group by, order by, connect by clauses, and SELECT statements with DISTINCT operations.
2. SQL functions (such as SUBSTR or INSTR)
3. expressions or conditions.
4. Select a table that contains a group by clause.
5. Select a table in the subquery or in the Combined Query of set operations.
6. SELECT a TABLE for the create table as select statement.

Instructions for use:

1. The LONG data type stores variable-length strings with a maximum length limit of 2 GB.
2. For text that exceeds a certain length, the LONG type can only be used for storage. Many objects in the data dictionary are defined as LONG.
3. The LONG type is mainly used for LONG string data that does not need to be searched for strings. To search for characters, the varchar2 type is required.
4. Many tools, including SQL * Plus, are difficult to process LONG data types.
5. Use of the LONG data type depends on the disk size.

SQL statements that can operate on LONG:

1. Select statement
2. SET statement in Update statement
3. VALUES statement in the Insert statement

Restrictions:

1. A table can only contain one LONG column.
2. LONG Columns cannot be indexed.
3. tables containing LONG Columns cannot be clustered.
4. Values of LONG Columns cannot be inserted into another table in SQL * Plus, such as insert... Select.
5. You cannot create LONG columns by querying other tables in SQL * Plus, for example, create table as select.
6. Constraints (except NULL, not null, and DEFAULT) cannot be added to LONG columns. For example, a KEY column cannot be of the LONG data type.
7. LONG Columns cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct. 8. LONG Columns cannot be used for distributed query.
9. PL/SQL process block variables cannot be defined as LONG.
10. LONG Columns cannot be changed by SQL functions, such as substr and instr.

Use the Oracle long field to insert large text

When writing applications, a large amount of large text is required to be inserted, But oracle's clob operations are complicated. Therefore, I did not choose to use clob, the long type in earlier versions of oracle is used [but there are some restrictions on the long type. A table can only have one long field].

At the beginning, I used insert into table1 values (a, B [long]) to insert data into the database. However, oracle imposes a limit on a statement that cannot exceed 4000 characters, and reported an error in the ORA-01704.

The solution is found in the oracle document, that is, the String is inserted into the database using the setCharacterStream () method for the long field.

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 );