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?

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: