Q

CLOB string too long

When you concatenate, Oracle treats a clob as a varchar2 and then, at over 4000 characters, the string becomes too long to handle. Do you have any ideas to get around this so that I can have larger HTML pages?

I am using
html := xmldata.transform(xsldata)
where HTML is a CLOB, to transform XML into HTML from PL/SQL.

As there seems to be no special functionality to pass parameters from PL/SQL to XSL, I insert an xmltype row into

a table at package runtime that contains the XSL stylesheet. I pass parameters to a PL/SQL function that constructs the insert statement and concatenate the parameters to the insert statement at runtime. This works fine until the clob reaches 4000 characters. Apparently when you concatenate, Oracle treats a clob as a varchar2 and then, at over 4000 characters, the string becomes too long to handle.

Do you have any ideas to get around this so that I can have larger HTML pages?

I have two answers for you.

First, if you can, you should switch from CLOB to XMLType. With XMLType, you have built-in functionality to support what you are trying to do. Check out the documentation on XML DB developer's guide and XMLType. XMLTransform is a wonderful thing. Search the XML DB guide for it.

Second, your problem is not really an XML question, it's more of an implicit conversion question between VARCHAR2 and CLOB. When you use SQL functions like concatenate (||), the CLOB is implicitly converted to a VARCHAR2 type. In SQL, using || limits you to 4000 bytes. Using || in PLSQL limits you to 32000 bytes.

To concatenate CLOBs, use the DBMS_LOB.APPEND procedure. The parameters are 2 CLOBs. Check the PL/SQL supplied packages guide for details. If you can work in 32k chunks, you can use || to build it up to that point and then append it to a CLOB. NULL out the VARCHAR2 variable and start over.

And finally, as far as passing parameters to your XSLT, I'm not sure what you need. It appears that you are dynamically building your transform. When I need to do that, I typically store the XSLT in an XMLType table and modify that when needed.

I would really recommend you switch to XMLType if at all possible.

This was first published in July 2005

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close