WORKFLOW_TRACKING_PKG compile error

Jan 22, 2008 at 4:31 PM
Hi all.

I'm just installing WFTools v0.3. When I install using @@install.sql on the tracking system, the scripts return:

"Advertencia: cuerpo del paquete creado con errores de compilación" (warning: body package made with compilation errors)

Using Toad I try to compile the body and it says:

"On line: 443
PLS-00561: character set mismatch on value for parameter 'XMLDATA'"

The server is an Oracle 9i Enterprise Edition Release 9.2.0.6.0 - 64 bit

Thank in advance for the help!
Coordinator
Jan 22, 2008 at 10:05 PM
Edited Jan 22, 2008 at 10:06 PM
Hi there,

This appears to be an issue with NCLOB and XMLTYPE under Oracle 9i. Unfortunately I don't have a 9i box to hand just now, but I'll give it a go when I'm in the office tomorrow.

I suspect a workaround might be the use of TO_CLOB when constructing the XMLTYPE used in WORKFLOW_TRACKING_PKG.InsertActivities.

Will update you as soon as I know more!

Thanks,

Dean Ward
WFTools
Project Co-ordinator
Jan 23, 2008 at 1:47 PM
Thanks Dean! I'll be waiting for an update then :-)
Coordinator
Jan 23, 2008 at 4:22 PM
Edited Jan 23, 2008 at 4:23 PM
Suspicion confirmed... NCLOB and XMLTYPE under 9i just don't play well together :o( We use NCLOBs wherever we manipulate or store XML data.

10g provides implicit conversion of NCLOB into a CLOB when it is passed into an XMLTYPE, so the workaround is to use an explicit conversion instead which can be performed using TO_CLOB.

I'll check-in a fix for this when I'm home later this evening, but in the meantime, you can make the change yourself by changing...

l_ACTIVITY_XML XMLTYPE := XMLTYPE(p_ACTIVITY_XML);
to
l_ACTIVITY_XML XMLTYPE := XMLTYPE(TO_CLOB(p_ACTIVITY_XML));

in the procedure WORKFLOW_TRACKING_PKG.InsertActivities.

Cheers,

Dean
Jan 24, 2008 at 2:50 PM
Thanks Dean! now I can compile without problems the procedure.

Hope when I finish my application I have no problems with the tracking :-)

Thanks again and good luck!