Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use CLOB big data field types in Oracle. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. Varchar2 types in Oracle

The character data we store in the Oracle database is usually stored in VARCHAR2. VARCHAR2 is divided into variable types in PL/SQL Data Types and field types in Oracle Database. The length of * varies in different scenarios.

In Oracle Database, the VARCHAR2 field type, the * value is 4000 * * the VARCHAR2 variable type in PL _ SQL, and the * byte length is 32767.

When VARCHAR2 can't hold the information we need to store, the Oracle's big data type LOB (Large Object) comes out.

II. LOB types in Oarcle

In Oracle, fields of type LOB (Large Object) are now used more and more. Because this type of field has a large capacity (up to 4GB data), and there can be multiple fields of this type in a table, it is very flexible and suitable for business areas with a very large amount of data (such as images, archives, etc.).

There are two types of LOB: BLOB and CLOB: BLOB is binary large object (Binary Large Object), which is suitable for storing non-text byte stream data (such as programs, images, audiovisual, etc.).

On the other hand, CLOB, that is, character-based large objects (Character Large Object), is related to the character set and is suitable for storing text-based data (such as historical archives, tome, etc.).

Third, use CLOB type fields in DB

(1), create a table (using sql or directly in the PL/SQL client), field type CLOB

-- Create table create table TEMP (name VARCHAR2, age NUMBER, temp_clob CLOB) tablespace INSIGHTDATA3_TS pctfree 10 initrans 1 maxtrans 255storage (initial 160K next 1m minextents 1 maxextents unlimited)

(2) add, delete, modify and check

Let's first take a look at the normal way to manipulate the CLOB type:

SELECT t.name, t.temp_clob FROM temp t;-General query INSERT INTO temp t VALUES ('Grand.Jon', 22,' joining the blog park * days')

Since the query is not of type varchar2, the field content of type CLOB cannot be seen in an ordinary query. The result is as follows.

The ordinary insert operation will also convert the string to varchar2 type by default because of the implicit conversion of Oracle. Once the string content exceeds the * limit of varchar2, an ora-01704 (string too long) error will be reported.

Correct operation

-- using PL/ SQL syntax to bind variables instead of stitching SQL directly

DECLARE V_LANG CLOB: = 'massive string to be inserted'; V_UPDATE CLOB: = 'updated massive string'; BEGIN INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG);-- add UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1;-- modify SELECT t.NAME, dbms_lob.substr (t.temp_clob) FROM TEMP t -- query converts CLOB to character type DELETE temp t WHERE rownum = 1;-- Delete COMMIT; END; / by column

For the operation of CLOB, we basically use substr, append, write and other methods in dbms_lob in the stored procedure.

Summary of dbms_lob method

Dbms_lob.createtemporary;-- create a temporary clob to store the stitched sql dbms_lob.write;-- write operation dbms_lob.append;-- splice clob dbms_lob.substr (v_SQL) -- intercept clob, and read all dbms_lob.freetemporary (v_SQL) without passing parameters;-- release clob

The query results are as follows:

Fourth, use CLOB type instances in stored procedures

Requirements: take the developed stored procedure as an example, you need to cycle through the time range stitching sql and reverse the time and date by column (pivot). If the time is too long (more than 1 year), the sql statement (varchar2) will report an error out of the range. At this time, you need to use CLOB to store the stitched sql.

PROCEDURE P_AND_CPT_RATIOOTH_APP_BAK2_N (V_APPIDS IN VARCHAR2, V_TYPE IN VARCHAR2, V_CHANNEL IN VARCHAR2, V_TABLE IN VARCHAR2, V_START IN VARCHAR2, V_END IN VARCHAR2, RESULT OUT mycursor) IS V_SQL CLOB; V_SQLWHERE VARCHAR2 (32767) default''; V_SQLWHERE_CHANNEL VARCHAR2 (32767) default'' V_SQL_DATES CLOB; V_Sdate DATE; V_Edate DATE; V_TABLE_DATE VARCHAR2 (50); V_TABLE_TYPE VARCHAR2 (50); V_START_DATE VARCHAR2 (50); V_END_DATE VARCHAR2 (50); V_DAY VARCHAR2 (50); BEGIN select column_name into V_TABLE_DATE from user_tab_columns where table_name='' | | V_TABLE |''and column_id=1 Select column_name into V_TABLE_TYPE from user_tab_columns where table_name='' | | V_TABLE |''and column_id=5; dbms_lob.createtemporary (V_APPIDS lob IF V_APPIDS is NOT NULL THEN V_SQLWHERE);-- create a temporary SQL (AND t.appid in);-- create a temporary SQL (=' AND t.appid in ('| V_APPIDS | |')'; create a temporary SQL | IF V_CHANNEL IS NOT NULL THEN V_SQLWHERE_CHANNEL: = 'AND t.channel =''| | V_CHANNEL | |'; END IF; IF V_TABLE_DATE = 'MON' THEN V_START_DATE: = SUBSTR; V_END_DATE: = SUBSTR (V_START_DATE,' yyyymm'); v_sdate: = to_date V_edate: = to_date (V_END_DATE, 'yyyymm'); WHILE (v_sdate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.