DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Problems with XMLQuery / XQuery in Oracle 10gR2

  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Unhappy 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

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    Describe the employee_new table.

    The emp column should be of XMLType

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width