Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Reji.

Asked: June 22, 2001 - 11:32 am UTC

Last updated: January 28, 2005 - 7:17 pm UTC

Version: 9i

Viewed 1000+ times

You Asked

Tom:
Would you please elaborate on the XML data type in Oracle9i? Can we do SQL operations against an XML type columns?

Is there any plan to increase the max size of VARCHAR2 from 4000 to a higher number?

Thx
Reji

and Tom said...

The Oracle supplied type, XMLType, can be used to store and query XML data in the database. XMLType provides member functions to access, extract and query the XML data using XPath expressions. XPath is another standard developed by the W3C committee to traverse XML documents. In Oracle9i, XMLType functions only support a limited subset of the XPath expressions. Oracle9i also provides a set of SQL functions such as SYS_XMLGEN,SYS_XMLAGG, and other PL/SQL packages (DBMS_XMLGEN) to create these XMLType values from existing relational or object relational data.

XMLType, a system defined type, can be used as arguments to functions or as table or view columns. When you create a XMLType column in a table Oracle internally uses a CLOB to actually store the XML data associated with this column. You can create Oracle Text indexing on the XMLType column and other functional indexes. In Oracle9i, since the XMLType is stored as a CLOB, updates can only be made to the entire document.



There is no plan that I am aware of. for data over 4000 bytes -- the correct and proper datatype is a CLOB. That is what that is there for.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Developer, April 21, 2004 - 8:34 am UTC

Tom:

Could I create the table with more than one XMLTYPE fields? I had a trouble to create it. The book's examples I can found is one XMLTYPE field in the table.

Please adavice.

Tom Kyte
April 21, 2004 - 8:23 pm UTC 

can you define "trouble" please.

ops$tkyte@ORA9IR2> create table ttt ( x xmltype, y xmltype );
 
Table created.

Developer, April 22, 2004 - 7:52 am UTC 

I have a trouble when I define xmltype column store as clob.

If there is one xmltype, there is no syntax error.
 create table ttt (
    id      number,
     xcol xmltype
 ) xmltype column xcol store as clob

But, if there are two column with xmltype, the error  is :
  1   create table ttt (
  2      id      number,
  3       xcol xmltype,
  4       ycol  xmltype
  5  )  xmltype column xcol store as clob
  6* xmltype column ycol store as clob
SQL> 
SQL> /
xmltype column ycol store as clob
*
ERROR at line 6:
ORA-00906: missing left parenthesis

I want to define both xmwltype column store as clob. This is a my trouble.

Please adavice.

Tom Kyte
April 22, 2004 - 7:09 pm UTC 

ops$tkyte@ORA9IR2> create table t ( x xmltype, y xmltype )
  2  xmltype y store as clob y_clob
  3  xmltype x store as clob x_clob
  4  /
 
Table created.


for whatever reason, it is wanting you to name the clob segment, I guess it can only system generate a single clob segment name per create, though that is not clear from the wire diagram.

Developer, April 23, 2004 - 7:49 am UTC

Yes. It is work for me.
Thank you

Stored proc

VA, January 28, 2005 - 3:26 pm UTC

Oracle 9iR2

I have a stored proc that needs to accept a XML document as input and do some processing with it.

What should the IN parameter for this stored proc be? If I set it to VARCHAR2, my XML document is limited to 4000 characters. If I set it to LONG, it is limited to 32k.

Suppose I define the stored proc as

create or replace procedure p (p_xml in xmltype) -- or clob
is
begin
null;
end;

How can I pass in a XML message more than 4000 characters to this proc?

Thanks

Tom Kyte
January 28, 2005 - 7:17 pm UTC

if you set it to varchar2 the limit is 32k as well (long in plsql is a subtype based on -- varchar2!)

If you want to pass more than 32k, you'll be sending a clob -- and how you do that, totally totally depends on the client language you choose.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here