IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I got ” ORA-19025: EXTRACTVALUE returns value of only one node” error in the Oracle database.
ORA-19025: EXTRACTVALUE returns value of only one node
Details of error are as follows.
SQL> SELECT * from (
2 SELECT PARTITION_NAME,
3 extractvalue
4 ( dbms_xmlgen.getxmltype
5 ( 'select high_value
6 from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
7 '//text()' ) as high_value
8 FROM DBA_TAB_PARTITIONS t
9 WHERE TABLE_NAME = 'F_SUBSCRIPTION_RATED_USAGE_M')
10 WHERE to_char(add_months(sysdate,-1),'SYYYYMMDD') < high_value;
FROM DBA_TAB_PARTITIONS t
*
ERROR at line 8:
ORA-19025: EXTRACTVALUE returns value of only one node
SQL>
EXTRACTVALUE returns value of only one node
This ORA-19025 errors are related with the EXTRACT and EXTRACTVALUE XML functions are deprecated.
To solve this error, Use XMLTABLE instead of EXTRACT and EXTRACTVALUE XML functions .
The following sample code illustrates EXTRACTVALUE working for a single value.
Create the following table and insert a single record. This record has only 1 employee (Ename) under the EmployeeList.
CREATE TABLE DEPT_SAMPLE (FIELD1 XMLType);INSERT INTO DEPT_SAMPLE VALUES
(XMLType('
<Department deptno="200">
<DeptName>Purchasing</DeptName>
<EmployeeList>
<Ename>Scott</Ename>
</EmployeeList>
</Department>'));
With only 1 employee, the EXTRACVALUE command works as expected.
SELECT extractValue(value(em), '/EmployeeList/Ename') AS ename
FROM DEPT_SAMPLE, table(XMLSequence(extract(FIELD1,'/Department/EmployeeList'))) em;ENAME
---------------
Scott
Insert a second record. This record contains two Ename values under the EmployeeList.
INSERT INTO DEPT_SAMPLE VALUES (
XMLType('
<Department deptno="300">
<DeptName>Sales</DeptName>
<EmployeeList>
<Ename>Robert</Ename>
<Ename>Billy</Ename>
</EmployeeList>
</Department>')); Executing the same SQL Select as above will now produce an error.
SELECT extractValue(value(em), '/EmployeeList/Ename') AS ename
FROM DEPT_SAMPLE, table(XMLSequence(extract(FIELD1,'/Department/EmployeeList'))) em;ORA-19025: EXTRACTVALUE returns value of only one node
The error is expected as ExtractValue is expecting only one element to exist.
The following shows what command should be used to extract multiple values. In fact, it works whether there are one or more elements listed.
Select ENAME From DEPT_SAMPLE,
XMLTABLE ('/Department/EmployeeList/Ename' Passing FIELD1
COLUMNS ENAME VARCHAR2(32) path '.');ENAME
--------------
Scott
Robert
Billy
As a added bonus, the following will illustrate a cursor that is used to navigate through a record of XML Types and within that cursor loop is a FOR LOOP to iterate through the employee names.
SET SERVEROUTPUT ON
DECLARE
v_XMLTYPE_DATA SYS.XMLTYPE;
CURSOR c_SAMPLE is SELECT * FROM DEPT_SAMPLE;
BEGIN
FOR REC_IN IN C_SAMPLE LOOP
v_XMLTYPE_DATA := REC_IN.FIELD1;
DBMS_OUTPUT.PUT_LINE('------------');
DBMS_OUTPUT.PUT_LINE(UPPER(v_XMLTYPE_DATA.EXTRACT('/Department/DeptName/text()').getStringVal()));
DBMS_OUTPUT.PUT_LINE('------------');
FOR record_picked IN (SELECT ENAME FROM
XMLTABLE ('/Department/EmployeeList/Ename' Passing v_XMLTYPE_DATA
COLUMNS ENAME VARCHAR2(32) path '.')) LOOP
DBMS_OUTPUT.PUT_LINE( record_picked.ENAME);
END LOOP;
END LOOP;
END;
/------------
PURCHASING
------------
Scott
------------
SALES
------------
Robert
Billy
PL/SQL procedure successfully completed.
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/
