XMLTYPE column larger THAN 4000 bytes => ORA-19011

When you try to convert an xml file, which has a tag that contains for example one or more pdf files, into an XMLTYPE object, you will get the following error:
ORA-19011: CHARACTER string BUFFER too small

This error will be thrown because the content of a tag of an XMLTYPE is limited to 4000 bytes(this problem should be solved in 11g).
I have developed a solution/workaround for this.

Continue reading

extract() function gives a ‘problem’ whith spaces and lines in XML

I was developing a dequeue function for an XML file and I wanted to check if a certain returned value from the xml file was correct.
To retrieve the value of a tag I used this=>
v_text := v_record.extract(‘/record/Fruit/text()’,’xmlns=”http://www.example.org”‘).getStringVal();

Ones I captured this record I wanted to check if the content of this tag was equal to, for example, some fruit, let’s say ‘Lemon’.
In most of the cases I received the correct result, but there were some xml files which didn’t gave me the correct result. Nevertheless I was pretty sure that the value in my xml file was ‘Lemon’.
So what happened? I did some checks and then I found out that the syntax of this specific xml file was something like this:

When extracting the content out of the Fruit tag he also took the spaces, tabs and lines with it, and that was the reason why he thought that this wasn’t a ‘Lemon’

Conclusion, always use trim for the spaces and put some replace clauses for lines and tabs round the value. So you should use something like this:
v_text := replace(replace(replace(trim(v_record.extract(‘/record/Fruit/text()’,’xmlns=”http://www.example.org”&#8216;).getStringVal()),chr(10),”),chr(13),”),chr(9),”);

Playing around with 11g

Playing around with Database 11g features and SCA 11g functionality (technology previews), I’ve came across some issues which you may find interesting to now/comprehend:
Native PL/SQL WebServices (11g Feature):

  • Procedures or functions need to have in- and output-parameters when using 11g webservices in bpel processes
  • You can’t work with rowtypes as return variables => not supported
  • XDB Username & Password authentication is needed for native pl/sql web service calls, when invoking native pl/sql webservices from BPEL. Add following properties to your partnerlink:

Jdeveloper 11g Tech Preview:

  • You can’t connect to AppServer 10.1.3 (it will be applicable in the production release)
  • You can run existing bpel processes in Jdev 11g when you’ve installed bpel in the embedded OC4J (nice to know)

You need less clicks to get to view audit data in your bpel console and it’s all using one single source of truth, one console, one management environment. Finally you’re used to working in bpel, esb, Jdeveloper … well now you need to get used to the new, improved environment.