Extracting value from xml clob with Namespace using Oracle pl/sql

In your code you use XML_BLOB as the variable - so I assumed a BLOB data type, which needs converting to a CLOB. However, if you have a CLOB value then you can skip using the BLOB_TO_CLOB function:

Oracle 11 Setup:

CREATE OR REPLACE FUNCTION BLOB_TO_CLOB( b BLOB )
RETURN CLOB 
IS
  c            CLOB;
  n            INTEGER     := 1;
  w   CONSTANT PLS_INTEGER := 32767;
  len CONSTANT INTEGER     := LENGTH( b );
BEGIN
  IF b IS NULL THEN
    RETURN NULL;
  END IF;

  IF len = 0 THEN
    RETURN EMPTY_CLOB();
  END IF;

  DBMS_LOB.CREATETEMPORARY( c, TRUE );
  WHILE ( n + w <= len ) LOOP
    DBMS_LOB.WRITEAPPEND( c, w, UTL_RAW.CAST_TO_VARCHAR2( DBMS_LOB.SUBSTR( b, w, n ) ) );
    n := n + w;
  END LOOP;
  DBMS_LOB.WRITEAPPEND( c, len - n + 1, UTL_RAW.CAST_TO_VARCHAR2( DBMS_LOB.SUBSTR( b, len - n + 1, n ) ) );
  RETURN c;
END;
/

CREATE TABLE blob_test ( value BLOB );

INSERT INTO blob_test VALUES (
  UTL_RAW.CAST_TO_RAW(
    '<Header>' ||
      '<SecondHeader>' ||
        '<ThirdHeader>' ||
          '<FourthHeader>' ||
            '<Value1>abcd</Value1>' ||
            '<Value2>xyz</Value2>' ||
            '<Value3>abxy</Value3>' ||
          '</FourthHeader>' ||
        '</ThirdHeader>' ||
      '</SecondHeader>' ||
    '</Header>'
  )
);

Query:

EXTRACT and EXTRACTVALUE are deprecated - use XMLTABLE instead:

SELECT Value1, Value2, Value3
FROM   blob_test b
       CROSS JOIN
       XMLTABLE(
         '/Header/SecondHeader/ThirdHeader/FourthHeader'
         PASSING XMLTYPE( BLOB_TO_CLOB( b.value ) )
         COLUMNS Value1 VARCHAR2(50) PATH 'Value1/text()',
                 Value2 VARCHAR2(50) PATH 'Value2/text()',
                 Value3 VARCHAR2(50) PATH 'Value3/text()'
       ) x;

Output:

VALUE1 VALUE2 VALUE3
------ ------ ------
abcd   xyz    abxy

Query 2:

If you do want to use EXTRACTVALUE you can remove the /text() from the XPath as EXTRACTVALUE will do that for you (but it should still work with it there too) and convert the BLOB to a CLOB:

SELECT EXTRACTVALUE(
         XMLTYPE( BLOB_TO_CLOB( value ) ), 
         '/Header/SecondHeader/ThirdHeader/FourthHeader/Value3'
       )
FROM   blob_test

Query - Update:

Simplification from @AlexPoole. The call to BLOB_TO_CLOB is not necessary as the XMLTYPE constructor can take a BLOB and a character set id:

SELECT Value1, Value2, Value3
FROM   blob_test b
       CROSS JOIN
       XMLTABLE(
         '/Header/SecondHeader/ThirdHeader/FourthHeader'
         PASSING XMLTYPE( b.value, NLS_CHARSET_ID('UTF8') )
         COLUMNS Value1 VARCHAR2(50) PATH 'Value1/text()',
                 Value2 VARCHAR2(50) PATH 'Value2/text()',
                 Value3 VARCHAR2(50) PATH 'Value3/text()'
       ) x;