Problems with XMLQuery / XQuery in Oracle 10gR2
I have to do a XPath and XQuery Sample but there is always the same error (ORA-06553).
I have the following view for Oracle's HR sample:
CREATE OR REPLACE VIEW employee_new OF XMLType
WITH OBJECT ID (extract(OBJECT_VALUE,
'/Employee/EmployeeId/text()').getnumberval()) AS
SELECT
XMLElement("Employee",
XMLForest(e.employee_id AS "EmployeeId",
e.last_name AS "LastName",
e.first_name AS "FirstName",
j.job_title AS "Job",
e.manager_id AS "Manager",
e.hire_date AS "HireDate",
e.salary AS "Salary",
e.commission_pct AS "Commission",
e.email AS "EMail",
e.phone_number AS "PhoneNumber",
XMLForest(
d.department_name AS "DepartmentName",
d.manager_id AS "LocManager",
l.street_address AS "StreetAddress",
l.postal_code AS "PostalCode",
l.city AS "City",
c.country_name AS "Country"
) AS "Department")) AS "emp"
FROM employees e, departments d, jobs j, locations l, countries c
WHERE (e.job_id = j.job_id AND e.department_id = d.department_id
AND d.location_id = l.location_id AND l.country_id = c.country_id);
XPath-Queries with EXTRACTVALUE do function as expected.
But when I try XQuery-Queries with the function XMLQuery like:
SELECT XMLQuery(
'for $i in ./Employee
where $i/LastName = "King"
return $i/Salary'
PASSING BY VALUE x.emp
RETURNING CONTENT)
FROM employee_new x;
I always get the same ERROR as following:
PASSING BY VALUE x.emp
*
ERROR at line 5:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'
I already tried a number of things. I created a XML-Scheme for the data or created the view upon the scheme. But it didn't help...
Can anybody help me? I am realy desperate.
Greetings,
Martin
