ORA-19025: EXTRACTVALUE returns value of only one node

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/

 941 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply