Home » SQL & PL/SQL » SQL & PL/SQL » Loading XML Type into ProC Variable
Loading XML Type into ProC Variable [message #234975] Thu, 03 May 2007 10:38 Go to next message
lewp
Messages: 2
Registered: May 2007
Junior Member
Using Oracle 1021, Solaris. I am writing a very simple "select into" query in which I take an XMLTYPE field (which I know will be short, by the way) and load it into a VARCHAR ProC variable.

Not surprisingly, if I try to load the XMLTYPE field directly, I get a type error.

But, now the trouble. Methods that convert the Xml type to a string/varchar, and that work just fine in sqlplus, won't get through the ProC compiler.

For example, If I try substr(XmlFieldName, 1, 1000) I get the message:

PLS-S-00306, wrong number or types of arguments in call to 'SUBSTR'

(I get this even though the exact same call works fine in SQLPlus (outside of the EXEC SQL wrapper of the ProC call))

(I have also tried dbms_lob.substr, to no avail)

I have also tried the XMLSerialize function, as in:

select xmlserialize(content XmlFieldName) and the ProC compiler complains:

PCC-S-02201, Encountered the symbol "XmlFieldName" when expecting one of the following: ,()*+... etc.

So my question is, how do I get the XMLType into a VarChar variable in ProC? Thanks.
Re: Loading XML Type into ProC Variable [message #234978 is a reply to message #234975] Thu, 03 May 2007 10:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I am not into Pro*C, but the first thing that came to mind was that you might be using the wrong library versions?
Re: Loading XML Type into ProC Variable [message #234982 is a reply to message #234978] Thu, 03 May 2007 10:57 Go to previous messageGo to next message
lewp
Messages: 2
Registered: May 2007
Junior Member
It's a good theory... but I have double-checked that point. All the libraries are from the 1021 release.
Re: Loading XML Type into ProC Variable [message #235023 is a reply to message #234982] Thu, 03 May 2007 15:13 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check Note:283146.1 on Metalink. This handles the (lack of) the Unified SQL Parser in 10.2

Apart from that, can you use XMLType.getClobVal()? There is a bug stating that this does not return Unicode characters in Pro*C, indicating that it should work for the rest..

[Updated on: Thu, 03 May 2007 15:18]

Report message to a moderator

Previous Topic: Formatted output from Oracle
Next Topic: small doubt in sql
Goto Forum:
  


Current Time: Tue Dec 06 04:47:28 CST 2016

Total time taken to generate the page: 0.14217 seconds