Is there a method in PL/SQL to convert/encode text

Question

I have a colleague who needs to convert text from a PL/SQL method into XML compliant text, as he is constructing a excel spreadsheet by updating a text template.

Is there a method in PL/SQL to convert/encode text to XML compliant text?

Answer 1

Later versions of oracle have a built in XML package for manipulating XML data.
For example, is this the sort of thing your colleague wants to do?:

SELECT DBMS_XMLGEN.getXML('SELECT * FROM emp') FROM dual;

Answer 2

hamischmcn option is easy and clear but it does not work in oracle forms, I tried a lot using that and nothing...you can generate the xml manually, using something like this

utl_file.put_line_nchar (file_id, 'xml version="1.0" encoding="utf-8"');

utl_file.put_line_nchar (file_id, 'Start');

--A FOR LOOP WHERE YOU DEFINE YOUR TAGS

utl_file.put_line_nchar (file_id, '/Start');

Note: Editor does not allow me to put the < > signs in the xml and tags title

Answer 3

Well, if you just want to convert XML characters, you'll want to do something like...

  outgoing_text := DBMS_XMLGEN.CONVERT(incoming_text)

Where outgoing_text and incoming_text are both VARCHAR2 or CLOB.

You can specify a second argument, but it defaults to DBMS_XMLGEN.ENTITY_ENCODE... it can also decode XML entities by passing DBMS_XMLGEN.ENTITY_DECODE as a second argument.

Answer 4

In addition to dbms_xmlgen, you can use the sql method, xmlelement and then extract the value back out.

select extract(xmlelement("mytest",my_variable),'mytest/text()') from dual;

The xmlelement function will make the text XML compliant, then using the extract function, it will extract the text out as is. (Note: The extractValue function will convert the text back to the non-XML compliant version.).

Answer 5

With respect to Rulas's issue on Oracle Forms, there are a lot of things Forms can't do (in various versions), where you need to use a database package.

So write a database PL/SQL function that does the dbms_xmlgen and return a varchar2 or clob, depending on the size of your XML. Then you can call that function from Forms. when you get the data back into Forms, use text_io or webutil to push the XML file to Excel.

Or stay over on the database side and use utl_file to push the XML output to a directory where you can get at it.