Clob datatype in oracle. The max size of a lob is 4Gb.

Clob datatype in oracle – APC. The name of field is XMLString. Database character set is defined during database creation (and can't be changed unless you recreate the database) and all character datatype columns store data in that character set. SerialClob cannot be cast to oracle. This data type is often used to store text, json or xml documents. Stack Overflow. Therefore if you try to put more than 4000 bytes of characters into it then it will fail as the literal (denoted by the surrounding TBL_NOTES - NOTE - text datatype at sql server. 379012 is the count of the rows in sql server. sql select /*insert*/ * from your_table; spool off As clients need to get LOBs via oracle server, you may consider the following interesting situation. substr(ADD_INFO_MASTER) from USER This select return me the CLOB field HUMAN READABLE. (MaxLenth = 33974) I am using oracle provider for oledb to connect to the oracle db. To solve your problem you have to build your CLOB step by step, using the The following table provides the details on each of the operations that accept VARCHAR2 types as operands or arguments, or return a VARCHAR2 value. The Built-In Data Type Summary table lists the built-in data types available. For details on how Oracle calculates It seems like passing DbType. substr. How to handle CLOB datatype in OBIEE reporting? Received Response Are you looking to display the clob contents or have individual columns reflect the content of individual xml elements from Alternative for CLOB data type in oracle 12g I wanted to know what is the best alternative data type for CLOB? My current database have a few CLOB data type. For example (using @AlenOblak's example): I have two tables I would like to complare. If i run the query like normal, all of those fields just have (CLOB) as the value. In the control file, i have mentioned the datatype for this field as CHAR(4000), since the usage of CLOB in control file was not working. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. DOMNodeList; node_len_ NUMBER; -- Convert the CLOB into a XML-document enter code here P := You can try creating DBMS_XMLPARSER. Inserting a CLOB data Hi Tom, This is in continuation of my question on how can I insert streams of characters coming from HTML form into a oracle database table CLOB column. If you don't want to (or can't) export and import your data, and really want it as a set of insert statements, you can use SQL Developer's built-in formatting tools to automatically split your CLOBs into multiple chunks that are small enough to be valid as string literals, and then spool the result to a file: spool clob_export. What can I do for solving this problem? My Table, Clob Data, Parsed Results Table shoul On the toolbar, click New Data Set and then select SQL Query. The CLOB (Character Large Object) datatype in Oracle 21c is used to store character data that can be larger in size than other data types such as varchar2 or char. Usage of std::string typed container to bind clob data to If I change my record group in my stored procedure to a LONG instead of a CLOB it seems to work. Does CLOB Data type is going to be deprecated in newer version? I tried to search around and seems like varchar2 will be the alternative but it has a limit size of 4000 which is n. When I query that data I have a one clob column in table and i want to clob column modified to varchar2 data type. Learn how to use the Oracle CLOB data type to store large amounts of text data up to 4GB. The Clob Column "MyClob" has following text data: --- To read the CLOB field. The NCBLOB data type stores character large objects in multibyte national character set. The Oracle CLOB data type is used to store large amounts of text data. serial. testing_text_id%TYPE, You can do something like this using DBMS_XMLPARSER package:-- Variables used for parsing the XML document xml_ CLOB := 'X'; p DBMS_XMLPARSER. The LENGTH function is also supported for the BLOB data type. If you are not using the default data source for this data set, select the Data Source from the list. Tony Andrews No, there is no bigger datatype for storage in the database. Example. Please help me to find out the root cause. Does anyone know how to accomplish this task? This is what I have done but I am getting an IndexOutofRange when calculating the GetOrdinal of the field. You can verify the codes in the table using the DUMP function. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data. I'm trying to run a query that has a few columns that are a CLOB datatype. Share. 4. I want to use DBMS_REDEFINITION of table so is it possible? I'm trying to get the values of the attributes from table MVR_DTL in column VENDOR_XML. Inserting into CLOB very very slow. clob. For CLOBs in a varying-width character set, n is the maximum byte-width used for characters in the CLOB clob datatype Tom I am in process of devloping a new application using Powerbuiler/Oracle. Is there any data type greater than CLOB in oracle. Built-In type overview; Numeric types; Data type assignments and comparison, sorting, and ordering type VARCHAR2 is new CHAR_BASE; type MLSLABEL is new CHAR_BASE; type UROWID is new CHAR_BASE; DBMS_ID and DBMS_QUOTED_ID define the length of identifiers in objects for SQL, PL/SQL and users. call); @Littlefoot, there is no need in ID column. Data in this column are 100 lines. Follow answered Feb 10, 2010 at 11:54. createtemporary(lob_loc => l_clob, cache => true, dur => dbms_lob. In Oracle 11G the limit has increased from 4GB to "(4 GB - 1) * DB_BLOCK_SIZE initialization parameter TO_CLOB converts NCLOB values in a LOB column or other character strings to CLOB values. Note that CLOB supports both fixed-with and variable-with character sets. 0. I tried using Learn how to use the CLOB (Character Large Object) datatype in Oracle 21c to store large amounts of text-based data. Ask Question Asked 6 years, 6 months ago. Extremely useful all the documentation that refered to LOB conversions in the ORACLE Docs , and that stated that the dbms_lob. 2. executing it. 12 How to change a dataype CLOB TO VARCHAR2(sql) 2 Converting CLOB to NUMBER to compare - Oracle. My clients want a field with word processing capability. Oracle Forms supports the LONG data type and is able to pass the known data type to the stored procedure. getlength(emp_details) from employee from emp_id=1; To get CLOB size for a given column in a given table that is allocated in the tablespace, you need to identify both segments implementing the LOB. package = 'intl') select (select content from r where I solved my problem with a solution that is simpler than the most voted answer. The CLOB datatype stores single-byte and multibyte character data. When you use CAST to convert a CLOB value into a character datatype or a BLOB value into the RAW datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype. Modified 2 years, 2 months ago. PostgreSQL supports both clob and text data types for storing large texts. In fact it was actually faster in some cases to just use two tables, and a varchar column (concatenate the varchars together and you have your clob). See the syntax, use cases, and examples of creating and inserting CLOB columns in tables. append() to append chunks of (maximum) 4000 characters to the clob:. Don't know about your code. Try to split the characters into multiple chunks and then insert. Syntax. TYPE rec_def IS RECORD +(+ testing_text_id testing_text. See Using the SQL Query Builder CLOB Datatype. So can I use PostgreSQL clob As far as I can tell, you can't do that. CLOB Datatype . The LONG datatype is like the VARCHAR2 datatype, except that the maximum size of a LONG value is 32760 bytes. Still i need a solution . The SQL column identifies the built-in functions and operators that are supported for CLOB and NCLOB data types. Oracle also recommends that you convert existing LONG columns to Oracle unable to convert from LONG datatype to CLOB datatype( incosistent datatype error) Defining size of CLOB in Oracle. LOB (Large OBjects) is a set of data types for storing large amounts of unstructured or semi-structured data. for example: Substr - The function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB. converting CLOB datatype to VARCHAR2 datatype having multi-byte characters Reader, August 09, 2022 - 6:15 pm UTC Oracle Update data in Clob datatype column. 3. In Oracle 19c I created the table: create table SAMPLE_TABLE ( id NUMBER not null, display_name NVARCHAR2(4000), ) When I run the script: declare i integer; p_tmp_clob clob; begi Use soci::long_string instead of std::string when binding clob typed data to soci statement. rowset. I am able to extract data which has length less than 32768 how ever I get ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind when the length is more than 32768. Improve this question. The Clob Column "MyClob" has following text data: { "UD_1":"J Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers; Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand; OverflowAI GenAI features for Teams; OverflowAPI Train & fine-tune LLMs; Labs The future of collective knowledge sharing; About the company As for the difference between BLOB and CLOB, I believe CLOB has character encoding associated with it, which implies it can be suited well for very large amounts of text. Clob. If you don't want to (or can't) export and import your data, and really want it as a set of insert statements, you can use SQL Developer's built-in formatting tools to automatically split your CLOBs into multiple chunks that are small enough to Subdivide your problem. About; Products The four large object data types BFILE, BLOB, CLOB, and NCLOB all store up to 4 GB of data. In addition to the built-in data types listed in the Built-In Data Type Summary table, Oracle This section describes the data types used in Derby. After retrieving your data, you can use the getClob method to return your Clob. Actually I am passing some free flow text (series of characters say something like what I am writing in this question) from html form. To be able to do such comparison in SQL statements, dbms_lob. Improve this answer. I added the where clause to reduce the time it takes to run which is high, with r as (select rownum i, content from profile where package = 'intl'), s as (select distinct (select min(i) from r where dbms_lob. You want to extract a line of text from your CLOB which contains a particular substring. blog that give additionally a function to convert blob to clob ! Can somebody test the 2 commands in Unicode(UTF-8) CLOB so I'm sure that this works with I have a table in Oracle database with field with data type CLOB. Use LOB columns (CLOB, NCLOB) instead. MACHINEINWARD_VALIDATING you are defining your CLOB. If these new large. Thanks in advance. The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. GETLENGTH function: select dbms_lob. These columns are useful for storing large documents or significant text-based data. Skip to main content. W If I change my record group in my stored procedure to a LONG instead of a CLOB it seems to work. Both have data storage limits of (4 GB - 1) * DB_BLOCK_SIZE. Poor performance getting clob field from Oracle in . sql. See syntax, default, Java type, JDBC metadata type, and example code. 10 LOOP INSERT INTO don (x) VALUES (empty_clob()) --Insert an "empty In Oracle 10g (early versions) there were massive issues with clob performance. Here is how it works for me: Whenever I create a function returning a clob value I do this: function foo return clob is l_clob clob; begin dbms_lob. compare() function can be used. The absolute maximum number of columns in a table is 1000. varchar2 data type cannot use to store large data. values are passed to old custom programs that continue to use old LONG/LONG RAW Converting Long Data Type to CLOB got ORA-06502 Hi,I am running following pl/sql code to extract image data from the table. Extract Oracle column with CLOB datatype using SSIS. Commented Sep 4, 2017 at 8:50. CLOB also supports charactor encoding meaning it is not just ascii charactors. select * from CLOB can store up to 4 gigabytes of character data. Follow edited Feb 11, 2010 at 16:15. CLOB stands for character large object. Learn how to use the CLOB data type in Oracle PL/SQL to store character-based data such as text, json or xml. See examples of creating and inserting C Learn about the CLOB data type and how it differs from other character types in Oracle. I want to find best practices. The New Data Set - SQL Query dialog launches. Here is the Oracle official documentation about TO_CLOB. select DBMS_LOB. If the clob is known to be a plain string, you maybe also wish to use . Then you needs to open the Clob's stream to read the data (Mayb be char or binary data). CLOB Datatype Attribute in Oracle: Column_Name CLOB I think you are mistaken what a CLOB datatype can store with the procedure put_line of dbms_output. PUT_LINE ( item IN VARCHAR2); The item is a varchar2 object. NCLOB can store up to 4 gigabytes of character data. alter table atable modify details clob; update atable set details Why is LONG an issue with Oracle? Is it possible to read a CLOB from a remote Oracle database? LONG is archaic and deprecated. compare(r. Please help. Definition of the remote table - ID-int,REPORT_ID- int,UNIQUE_ID- varchar,user_id -int,NOTE-text,DTS-datetime,SalesNote- text,userrole-varchar,userein- varchar I want render thanks to Nashev that has posted a solution to convert clob to blob How convert CLOB to BLOB in Oracle? and to this post written in german (the code is in PL/SQL) 13ter. BLOB and CLOB data can take a long time to retrieve, relative to how quick data from a TEXT field can be retrieved. It might be procedure or function or script. But this also fails when there is a EndOfLine present in the field. All of the seed Database Objects/Oracle Forms/JAVA Pages/PRO C code/APIs have. testing_text_id%TYPE,. One of the columns is type CLOB. A text string which is longer than 4000 bytes can not fit in It's like storing dates in DATE columns and numeric values in NUMBER columns: use the most appropriate datatype for the data you want to store A CLOB is a Charactor Large Object, which will hold charactors (text), Basically this makes it a huge string field. content) = 0) min_i from profile t where t. Because, if clob data is bound using std::string while writing clob data into a table using soci, soci library consider that data as a varchar2 type instead of clob type. DOMDocument object from it. Relying on Oracle's implicit CLOB <-> VARCHAR2 conversions tends to be slow. The BFILE data type enables access to binary file LOBs that are stored in file systems outside the Oracle database. When I query that data For fixed-width n-byte CLOBs, if the input amount for SUBSTR is greater than (32767/n), then SUBSTR returns a character buffer of length (32767/n), or the length of the CLOB, whichever is lesser. 1 how to get clob data with vb6. The procedure is compiled but I'm having problem . NCLOB Datatype. Storage wise it will use space as follows: if the lob is defined as in-row, and the lob is less than ~ 4kb , it will just take I want to know what does Oracle's CLOB has to offer over BLOB data type. In addition to the built-in data types listed in the Built-In Data Type Summary table, Oracle I am going to assume that your query is: insert into t1 ( clob_column ) values( '< more than 4000 characters . data types from the CLOB data type, then a VALUE_ERROR exception will be raised. subtype DBMS_ID is VARCHAR2(ORA_MAX_NAME_LEN); subtype DBMS_QUOTED_ID is VARCHAR2(ORA_MAX_NAME_LEN+2); DBMS_ID_30 and Oracle Analytics was named as a Leader Oracle Analytics was named as a Leader in the 2024 Gartner® Magic Quadrant for Analytics and BI Platforms. Code: create or replace FUNCTION F_long_to_clob( p_query in varchar2, p_name in varchar2, p_value in varchar2 ) RETURN In the control file, i have mentioned the datatype for this field as CHAR(4000), since the usage of CLOB in control file was not working. content, t. The below doesn't seem to be valid syntax: GLOBAL TT GlobalTemp_EMP( //this already exists emp_refno (30 byte); ) I don't know the reason for using byte semantics, or whether you defined it as a clob or char or varchar2. As clob data are general big, so it may push other data ; lob data get poor disk read if oracle decide not to cache it, and stream the data to the client. Perhaps you could try with NCLOB data type, where "N" represents "national character set" and it'll store Unicode character data. I can think of two possible interpretations of your requirements: How to work with BLOB and CLOB data using dotConnect for Oracle. I would like to do something like this: select key, clob_value source_table minus select key, clob_value target_table Unfortunately, Oracle can't perform minus operations on clobs. lob data will compete limited SGA cache with other data type if oracle decide to cache it. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. When you execute the query, if the CLOB column contains well-formed XML, and you select the XML data type, the data engine returns the XML data, structured within the CLOB column tag name. The max size of a lob is 4Gb. Example output when data type is XML: Note the <DESCRIPTION> element contains the XML data stored in the CLOB column, as shown below. 121k 30 30 CLOB Hi Tom, I have a table in which one column is CLOB datatype. LONG columns are supported only for backward compatibility. In the end you should as was noted earlier, use the right datatype for the right job. substr should be returned into a RWS(32767), not a LONG for CLOBS, thanks once again. ; Insrt - This function returns the matching position of the nth occurrence of the pattern in the LOB, starting A CLOB (character large object) value can be up to 2,147,483,647 characters long. I am trying to extract data from an oracle table which has a column with CLOB datatype. it is just simply clob. BLOB objects can be thought of as Oracle has deprecated LONG datatype in favour of CLOB for about twenty years now. I am very new to Oracle and Clob datatype. We use the LONG datatype to store variable-length character strings. ----- this is a test string xxxxxxxxxxxxxxxxxxxx SQL> declare 2 v_lob clob; 3 begin 4 5 for r_lob in (select c 6 from foo 7 for update) 8 loop 9 dbms_lob. Learn how to use CLOB (character large object) to store unicode character-based data, such as large documents, in Oracle Database. 0 - 64bit Production) I have a table like this:CREATE TABLE testing_text (testing_text_id NUMBER (9), test_text CLOB); If I manually insert data from the SQL prompt it is fine. Since my text size is well below 1GB, I am fine with using either of these types. 0 How to fetch a CLOB column using dbUtils in oracle11g? Clob datatype in PL/SQL program going to exception when it crosses the varchar2 limit and giving the "Error:ORA-06502: PL/SQL: numeric or value error" , Why Clob datatype is behaving like varchar2 datatype. I understand that the clob data type in PostgreSQL can only store up to 1GB of text rather than the 4GB in oracle. length()); So try this In your first line declare stmt clob := 'INWARDPKG. This is the number in the Code column of the Built-In Data Type Summary table. A CLOB is used to store unicode character-based data, such as large documents in any character set. DOMDocument; node_list_ DBMS_XMLDOM. CLOB data type can store up to 4GB of character data and is typically used to store large text documents such as books, articles, or web content. 0" encoding="UT The below doesn't seem to be valid syntax: GLOBAL TT GlobalTemp_EMP( //this already exists emp_refno (30 byte); ) I don't know the reason for using byte semantics, or whether you defined it as a clob or char or varchar2. Clob interface in the JDBC API in Java. 2. overcome 32k limit when inserting oracle A CLOB column in Oracle stores large amounts of character data, capable of holding up to 4GB of text. How to convert CLOB to CHAR datatype in Oracle? Hot Network Questions The maximum for one time insertion is 4000 characters (the maximum string literal in Oracle). DBMS_OUTPUT. How can I do this? Introduction to Oracle Clob datatype. write(r_lob. Ask Question Asked 2 years, 3 months ago. table struct The most straightforward way, given that the operation of moving from a varchar column to a CLOB is disallowed, would be to create a new column and move the data from the old column to the new column:. TBL_NOTES- NOTE - CLOB datatype at Oracle. NCLOB. However you can use the lob function dbms_lob. info. I've used clob data type as we are migrating the DB from Oracle. The two links to Oracle's FAQ will provide specific information for The CLOB datatype is represented using the java. c, 6 I would suggest you have a look on the package DBMS_LOB (). . My question is: Is there any way to extract the one single value like ACCOUNT value from the line above? Keep in mind that this CLOB field can variate and the __17__ACCOUNT= will not be in the same place every time. Oracle says, Do not create tables with LONG columns. Working with the dbms_lob package is reasonably performant. It has some functions useful for working with LOB . Why Do I get OutOfRange Exception in GetOrdinal Function of this CLOB field? I need to read a CLOB field from an ORACLE table int a C# variable of type String. Solution: Use Oracle's CONCAT function in aSELECTquery, theCONCATfunction works for theCLOB` data type. sql; oracle-database; oracle10g; Share. Your solution rock across the dblink. Clob? java; oracle-database; hibernate; oracle10g; clob; Share. Commented Sep 4, 2017 at 7:13. You use CLOB to store single-byte or multibyte characters with the maximum size is (4 gigabytes – 1) * (database block size). You can just convert dataora from PK to UK and that will do the trick. This helped me to overcome copying data from legacy database with LONG datatype to CLOB dataype in Oracle 11g. See the syntax, examples and functions for creating, inserting and I am creating a stored procedure which appends a value to a CLOB . We have a table with name ABC which has a column of CLOB data type. Just do this in a loop: The Oracle PL/SQL CLOB or Character Large Object data type is used to store character-based data in a database. you don't define a size exactly when setting a clob (unlike a varchar). datatype. BFILE. This section describes the explicit conversion functions in SQL and PL/SQL to convert other data types to and from CLOB, NCLOB, and BLOB data types. The procedure has 2 in parameter (one CLOB and one . Oracle tries to reclaim the old versions and reuse them. getSubString(1, (int) clob. Here is an example of the final insert statement: Quote (read here)-. To insert the data into CLOB column, use the below query. Is there a way ? – Skadoosh95. 1) if you are storing 4000 bytes or less in the database, use VARCHAR2 otherwise use CLOB. been modified to use the CLOB data type and provides more storage. Cannot save clob data type in database (Struts, Spring, Hibernate) 4. The length is given in number characters for both CLOB, unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 Is there any data type greater than CLOB in oracle. Since you are using a string literal to define your CLOB, you are facing the limits of string literals (see Oracle 12c Documenation). Follow edited Apr 3, 2014 at 22:18. The CLOB object in JDBC holds a logical pointer to SQL CLOB. Oracle PL/SQL: Performance of CLOB data type in PL/SQL. In Oracle there are several different kinds of LOBs: BLOB (Binary Large Object) datatype stores unstructured binary large objects. Net. CLOB). Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers; Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand; OverflowAI GenAI features for Teams; OverflowAPI Train & fine-tune LLMs; Labs The future of collective knowledge sharing; About the company Visit the blog This section describes the explicit conversion functions in SQL and PL/SQL to convert other data types to and from CLOB, NCLOB, and BLOB data types. TO_CLOB(): Converts from VARCHAR2, NVARCHAR2, or NCLOB to a CLOB TO_NCLOB(): Converts from VARCHAR2, NVARCHAR2, or CLOB to an NCLOB javax. alter table atable add (tempdetails varchar2(4000)); update atable set tempdetails = details; update atable set details = null; -- this is necessary to change data type alter table atable modify details long; -- this is required because you can not change directly to clob. Oracle Database uses a code to identify the data type internally. Both fixed-width and variable-width character sets are supported, and both use the database character set. Viewed 277 times 0 . Klaus Byskov Pedersen Klaus Byskov Pedersen. ; Enter a name for the data set. I am storing XML string which is 10,000 characters long for each record. NCLOB is similar to CLOB except that it can store the Unicode characters. CLOB can store single-byte or multibyte characters with a maximum size of 4 GB. Then use DBMS_XMLDOM package methods to get the value of any node. My requirement We use the VARCHAR2 datatype to store variable-length character data. However, querying them can be tricky, especially when the column’s content appears as (CLOB) in certain query tools like SQL*Plus. In table profile column content is NCLOB. An example of using a CLOB data type is as follows: CREATE TABLE clob_table ( id NUMBER, myclob CLOB ); INSERT INTO mytable (id, myclob) values (1, ' This I need to parse clob data in oracle. CREATE TABLE don (x clob); DECLARE l_clob clob; BEGIN FOR i IN 1. How to return CLOB datatype as output Parameter in Oracle stored procedure? I want to return an output param of CLOB data type(XML data) from an oracle stored procedure. On the other hand, if data modeler prefers to have PK in each and every table you can add virtual column like "virtual_pk number generated always as (to_number(to_char(dataora,'yyyymmddhh24missff'))) primary key". So, if you want to print the output of a clob greater than the limit of a varchar, you need to create a different kind of code. >' ); '' delimits a string literal that will be a CHAR or VARCHAR2 data type; it does not indicate that the literal is a CLOB data type. My procedure looks like thiscreate or replace PROCEDURE myProcedure( myParam1 IN NUMBER ,myParam2 IN NUMBER ,myParam3 OUT CLOB ) AS xml_bits CLOB;BEGIN select I am very new to Oracle and Clob datatype. How to improve performance while update column Clob in oracle? 5. Not sure how to convert the String into java. Object to your data layer would indicate that the data type of the parameter is supposed to be a BLOB (binary) yet you have indicated you want it to be a CLOB (character). In other words, the object points to the location of the data instead of holding its character data. ; Enter the SQL query or use the Query Builder to construct your query to retrieve the CLOB data column. I enhanced your SHOWLONG procedure as Function to return CLOB. VENDOR_XML is of datatype clob and contains an xml that looks like this &lt;?xml version="1. Utilizing CLOB data type from store procedures in Oracle Forms In the Oracle Database (Oracle Database 10g Enterprise Edition Release 10. Let's assume your stored procedure is defined like this: CREATE PROCEDURE insert_XMLDOC (pid IN NUMBER, repo_document IN CLOB) To get CLOB size for a given column in a given row, use DBMS_LOB. This type of data is Yes, it's not allowed (this restriction does not affect CLOBs comparison in PL/SQL) to use comparison operators like =, !=, <> and so on in SQL statements, when trying to compare two CLOB columns or CLOB column and a character literal, like you do. CLOB All the Clob mentioned above are of type java. You must divide your big clob string into multiple strings, each one with less than 4000 chars, convert each one using the to_clob method, and concatenate them with the || operator. ( single record has 100 lines) Each line has been separated by chr(13) ( enter key's ascii value). ALTER TABLE some_table ADD (foo CLOB); UPDATE some_table SET foo = old_column; ALTER TABLE some_table DROP COLUMN old_column; ALTER TABLE Depending on the amount of information provided you can use the below logic if you face the issue : ORA-01704: string literal too long clob. Modified 6 years, 6 months ago. parser; doc_ DBMS_XMLDOM. I think clob can hold upto 4 GB of data. So I not want create new column with varchar2 data-type and convert clob to varchar2 using dbms_clob. The CLOB data type can be defined in Oracle tables using the following syntax: Footnote 1 . parser object from the CLOB XML and get a DBMS_XMLDOM. So, use only what you need. You can compare both size with following query: Use this approach. cxvkgf cqwtt jmesjar ddivs hibkgu kbuwbyo osdpe dpt amsr tmsmh