Problem: “I am having trouble parsing XML in PL/SQL using the XMLType.extract() function.  I've done this dozens of times in the past with no issue - what am I doing wrong?”

ORA-30625: Method Dispatch on NULL SELF Argument Is Disallowed

Solution: Your XML tags are defined within a namespace.  You need to pass the “nsmap” parameter to the extract function.

Oracle Database > Release 21

Code Example

Take a look at this XML - notice how each tag name starts with  ns1:.

<ns1:root xmlns:ns1="http://www.example.com/">
    <ns1:childNode>12345678</ns1:childNode>
</ns1:root>

This PL/SQL shows how to create the nsmap parameter and extract XML when tags are defined with a namespace.  

DECLARE
  l_raw_xml VARCHAR2(4000) := '
<ns1:root xmlns:ns1="http://www.example.com/">
  <ns1:childNode>ChildNode - Contents</ns1:childNode>
</ns1:root>';
  l_xml      XMLType;
  l_nsmap    VARCHAR2(255);
BEGIN
  l_xml := XMLType(l_raw_xml);
  l_nsmap := 'xmlns="' || l_xml.getNamespace || '"';
  dbms_output.put_line(
    l_xml.existsnode(xpath => '/root/childNode',
                     nsmap => l_nsmap)
  );
  dbms_output.put_line(
    l_xml.extract(xpath => '/root/childNode/text()',
                  nsmap => l_nsmap).getstringval()
  );
END;

When you try to extract the xml with just the xpath parameter you get an error.

ORA-30625: Method Dispatch on NULL SELF Argument Is Disallowed

Real documentation on XML and namespaces can be found here.

Issue

Every once in a while I encounter an error when trying to update a Purchase Order line estimated delivery date using Oracle Forms. The form does not allow the line to be updated. We have found that the issue is a result of un-recognized characters in the Purchase Order line part description. Once the bad/special character is removed the line can be updated.

Challenge

Identify special* characters in a string - Oracle 12c

*special characters are defined (for this post) as any character with an ascii code greater than 127 - full list of ascii characters

Solution

Create a function that, given a string, returns the char location in string of special characters separated by commas

e.g. function list_bad_characters (p_string IN NUMBER) RETURN VARCHAR2;

Code

CREATE OR REPLACE FUNCTION apps.list_bad_characters (p_string IN VARCHAR2) RETURN VARCHAR2 AS l_curr_char VARCHAR2 (10 BYTE); l_return VARCHAR2 (2048 BYTE); BEGIN FOR i IN 1 .. LENGTHB (p_string) LOOP l_curr_char := SUBSTR (p_string, i, 1); IF (ASCII (l_curr_char) > 127) THEN IF (l_return IS NOT NULL) THEN l_return := l_return || ','; END IF; l_return := l_return || TO_CHAR (i) || '(' || l_curr_char || ')'; END IF; END LOOP; RETURN l_return; END;

Execute Immediate

-- CODE EXAMPLE
declare
  v_dummy NUMBER;
begin
  EXECUTE IMMEDIATE
  'select 1 from dual'
  INTO v_dummy;
  dbms_output.put_line(v_dummy);
end;

apex.server.process(
    "APPLICATION_PROCESS_AJAX",
    {f01:results},
    {
        success: function( pData ) {
            apex.message.showPageSuccess("Success.");
            apex.event.trigger(pRegionID, 'apexrefresh');
        }
    }
);