Using the Oracle XMLType 15 July 2005 at 00:00

Getting the value of an xml element in a CLOB

In this example, xml is stored in a CLOB, and you want to retrieve the value of a certain element:

  SELECT EXTRACTVALUE(VALUE(xml), '/your/xpath/query')
  INTO yourVarChar
  FROM TABLE(XMLSEQUENCE(XMLTYPE(rec.manual_xml).EXTRACT('/'))) xml;

Updating an XML element stored as a CLOB

This is the reverse of the previous example - if you want to update the value of the same XML element stored in a CLOB field:

  UPDATE YourTable t 
  SET t.xml_field = UPDATEXML(XMLTYPE(t.xml_field), '/your/xpath/query/text()', 'new_value').getClobVal()
  WHERE t.id = 100;
Returning a RefCursor with Oracle 21 July 2005 at 00:00
  FUNCTION my_function() RETURN SYS_REFCURSOR IS
    ref_cursor SYS_REFCURSOR;
  BEGIN
    OPEN ref_cursor FOR
      SELECT * FROM my_table;
      RETURN (ref_cursor);
  END;