SQL queries the nclob field of oracle, sql1_lenclob

SQL queries the nclob field of oracle, sql1_lenclob

Use the CONTAINS keyword to query the NCLOB Field

 

SELECT
FORMATTED_MESSAGE
FROM
FOC. TBL_LOG
WHERE
CONTAINS (FORMATTED_MESSAGE, p_FORMATTED_MESSAGE)> 0;

Oracle clob field insertion

In oracle, four lobs types are available: blob, clob, bfile, and nclob.
The following is a brief introduction to the lob data type.
Blob: Binary lob, Which is binary data. It can be up to 4 GB and stored in the database.
Clob: character lob, character data, up to 4 GB, stored in the database.

Bfile: binary file; read-only binary data stored outside the database. The maximum length is limited by the operating system.
Nclob: supports a clob column of the byte character set (nultibyte characterset.
Oracle Database developers often encounter lob data retrieval and operation. I will introduce some methods and skills for lob data processing in oracle, and hope to help readers in future development.

Oracle can use multiple methods to retrieve or operate lob data. The common solution is to use the dbms_lob package.
Other methods include application programminginterfaces and oracle call interface.
1. In the oracle development environment, we can use the dbms_lob package for processing! Dbms_lob package is powerful and simple to use. It can be used to read internal lob objects or process bfile objects. However, there is a difference between the two. Read and Write operations can be performed when processing internal lob objects (blob, clob), but read operations can only be performed when processing external lob object bfile, write operations can be processed using pl/SQL. In addition, you can use SQL to process lob, but note that SQL can only process the entire lob and cannot operate on lob data slices.

The dbms_lob package has built-in functions such as read (), append, write (), erase (), copy (), getlength (), and substr, you can easily operate lob objects. We will not discuss it in depth here. Readers can refer to relevant books.

For pl/SQL, the following describes how to use dynamic pl/SQL statements to process clob objects and pass them as table names!
Example 1.
Dynamic PL/SQL. For CLOB field operations, you can pass the table name table_name, the unique table Identifier Field name field_id, And the clob field name field_name Record Number v_id to start processing the character location v_pos, passed string variable v_clob

Update the PL/SQL PROCESS OF CLOB: updateclob
Create or replace procedure updateclob (
Table_name in varchar2,
Field_id in varchar2,
Field_name in varchar2, v_id in number,
V_pos in number,
V_clob in varchar2)
Is
Lobloc clob;
C_clob varchar2 (32767 );
Amt binary_integer;
Pos binary_integer;
Query_str varchar2 (1000 );
Begin
Pos: = v_pos * 32766 + 1;
Amt: = length (v_clob );
C_clob: = v_clob;
Query_str: = 'select' | field_name | 'from' | table_name |'
Where '... the remaining full text>

I need to query the value of the CLOB field, but there are too many values. I only need to display the first seven words, and then use "" to replace it with SQL statement.

Select substr (field, 0, 7) | '...' from tablename.