XMLType field to small for large xml

Posted on 2003-10-22
Last Modified: 2012-08-14

I'm currently testing XMLType in oracle on windows 2000 server . I need to insert a quite large XML file into an XMLType field (as far as I know XMLType is a CLOB, capable of storing a couple of GB) but when I try to insert an XML file with more than approx 100 lines, I get this error:

ORA-01704: String lateral too long.

My XML file is 4000 characters long (including whitespaces), 123 lines...

Question by:venkatrmn
  • 2

Accepted Solution

patelgokul earned 125 total points
ID: 9597844
LVL 47

Expert Comment

ID: 9597869

Oracle9 i
XML Database Developer’s Guide - Oracle XML DB
Release 2 (9.2)
October 2002
Part No. A96620-02

The XMLType column can be created like any other user-defined type column:
CREATE TABLE warehouses(
warehouse_id NUMBER(4),
warehouse_spec XMLTYPE,
warehouse_name VARCHAR2(35),
location_id NUMBER(4));
Example 4–2 Creating XMLType: Creating XMLType Columns
As explained, you can create XMLType columns by simply using the XMLType as
the datatype. The following statement creates a purchase order document column,
poDoc, of XMLType:
CREATE TABLE po_xml_tab(
poid number,
CREATE TABLE po_xtab of XMLType; -- this creates a table of XMLType. The default
-- is CLOB based storage.
Example 4–3 Adding XMLType Columns
You can alter tables to add XMLType columns as well. This is similar to any other
datatype. The following statement adds a new customer document column to the
ALTER TABLE po_xml_tab add (custDoc XMLType);
Example 4–4 Dropping XMLType Columns
You can alter tables to drop XMLType columns, similar to any other datatype. The
following statement drops column custDoc:
ALTER TABLE po_xml_tab drop (custDoc);

Inserting Values into an XMLType Column

To insert values into the XMLType column, you need to bind an XMLType instance.
Example 4–5 Inserting into XMLTYpe Using the XMLType() Constructor
An XMLType instance can be easily created from a VARCHAR or a Character Large
Object (CLOB) by using the XMLType() constructor:
( 100, XMLType(
’<Warehouse whNo="100">
</Warehouse>’), ’Tower Records’, 1003);
This example creates an XMLType instance from a string literal. The input to
createXML() can be any expression that returns a VARCHAR2 or CLOB.
createXML() also checks that the input XML is well-formed.

Using XMLType in an SQL Statement
The following simple SELECT statement shows how you can use XMLType in an
SQL statement:
Example 4–6 Using XMLType and in a SELECT Statement
FROM warehouses w;
where warehouse_spec is an XMLType column operated on by member function
extract(). The result of this simple query is a string (varchar2):

Updating an XMLType Column
An XML document in an XMLType can be stored packed in a CLOB. Then updates
have to replace the whole document in place.
Example 4–7 Updating XMLType
To update an XML document, you can execute a standard SQL UPDATE statement.
You need to bind an XMLType instance, as follows:
UPDATE warehouses SET warehouse_spec = XMLType
(’<Warehouse whono="200">
This example created an XMLType instance from a string literal and updates column
warehouse_spec with the new value.
Note: Any triggers would get fired on the UPDATE statement
You can see and modify the XML value inside the triggers.

Using XMLType 4-11
Guidelines for Using XMLType Tables and Columns
The following are guidelines for storing XML data in XMLType tables and columns:
Define table/column of XMLType
First, define a table/column of XMLType. You can include optional storage
characteristics with the table/column definition.
Create an XMLType Instance
Use the XMLType constructor to create the XMLType instance before inserting into
the column/table. You can also use a variety of other functions that return
Select or Extract a Particular XMLType Instance
You can select out the XMLType instance from the column. XMLType also offers a
choice of member functions, such as extract() and existsNode(), to extract a
particular node and to check to see if a node exists respectively. See the table of
XMLType member functions in Oracle9i XML API Reference - XDK and Oracle XML
Note: This release of Oracle supports creating tables of XMLType.
You can create object references (REFs) to these tables and use them
in the object cache.
See Also: "SYS_XMLGEN(): Converting an XMLType Instance"
LVL 47

Expert Comment

ID: 9597886
To load internal LOBs (BLOBs, CLOBs, and NCLOBs) or XML columns from a
primary datafile, you can use the following standard SQL*Loader formats:
n Predetermined size fields
n Delimited fields
n Length-value pair fields
These formats is described in the following sections and in Oracle9i Database
LOB Data in Predetermined Size Fields
This is a very fast and conceptually simple format in which to load LOBs.
Note: Because the LOBs you are loading may not be of equal size, you can use
whitespace to pad the LOB data to make the LOBs all of equal length within a
particular data field.
LOB Data in Delimited Fields
This format handles LOBs of different sizes within the same column (datafile field)
without problem. However, this added flexibility can affect performance, because
SQL*Loader must scan through the data, looking for the delimiter string.
As with single-character delimiters, when you specify string delimiters, you should
consider the character set of the datafile. When the character set of the datafile is
different than that of the control file, you can specify the delimiters in hexadecimal
(that is, ’hexadecimal string’). If the delimiters are specified in hexadecimal
notation, the specification must consist of characters that are valid in the character
set of the input datafile. In contrast, if hexadecimal specification is not used, the
delimiter specification is considered to be in the client’s (that is, the control file’s)
character set. In this case, the delimiter is converted into the datafile’s character set
before SQL*Loader searches for the delimiter in the datafile.
Loading LOB Data from LOBFILEs
LOB data can be lengthy enough so that it makes sense to load it from a LOBFILE
instead of from a primary datafile. In LOBFILEs, LOB data instances are still
considered to be in fields (predetermined size, delimited, length-value), but these
fields are not organized into records (the concept of a record does not exist within
LOBFILEs). Therefore, the processing overhead of dealing with records is avoided.
This type of organization of data is ideal for LOB loading.
There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader
reads LOBFILEs in 64 KB chunks.
In LOBFILEs the data can be in any of the following types of fields:
n A single LOB field into which the entire contents of a file can be read
n Predetermined size fields (fixed-length fields)
n Delimited fields (that is, TERMINATED BY or ENCLOSED BY)
n The clause PRESERVE BLANKS is not applicable to fields read from a
n Length-value pair fields (variable-length fields)--VARRAW, VARCHAR, or
VARCHARC loader datatypes--are used for loading from this type of field.
All of the previously mentioned field types can be used to load XML columns.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question