环境是ORACLE10g(10.1)
该语句在sql中执行能返回结果:SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Employee",
e.job_id||' '||e.last_name)
ORDER BY last_name))
as "Dept_list"
FROM employees e
WHERE e.department_id = 30;但如果放在存储过程中,编译时即报错:
create or replace procedure PRO_XML_LOADPLANTEST
(
p_result out pack_result.return_result
)
is
begin
SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Employee",
e.job_id||' '||e.last_name)
ORDER BY last_name))
as "Dept_list"
FROM employees e
WHERE e.department_id = 30;
end ;错误如下:
Compilation errors for PROCEDURE PRO_XML_LOADPLANTESTError: PLS-00306: 调用 'XMLAGG' 时参数个数或类型错误Error: PL/SQL: ORA-00904: "XMLAGG": 标识符无效
实在不明白,希望有大虾帮助!!
该语句在sql中执行能返回结果:SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Employee",
e.job_id||' '||e.last_name)
ORDER BY last_name))
as "Dept_list"
FROM employees e
WHERE e.department_id = 30;但如果放在存储过程中,编译时即报错:
create or replace procedure PRO_XML_LOADPLANTEST
(
p_result out pack_result.return_result
)
is
begin
SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Employee",
e.job_id||' '||e.last_name)
ORDER BY last_name))
as "Dept_list"
FROM employees e
WHERE e.department_id = 30;
end ;错误如下:
Compilation errors for PROCEDURE PRO_XML_LOADPLANTESTError: PLS-00306: 调用 'XMLAGG' 时参数个数或类型错误Error: PL/SQL: ORA-00904: "XMLAGG": 标识符无效
实在不明白,希望有大虾帮助!!
open p_result for
SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Employee",
e.job_id||' '||e.last_name)
ORDER BY last_name))
as "Dept_list"
FROM employees e
WHERE e.department_id = 30;
存储过程写法应该没问题,主要问题是同样SQL语句在存储过程报错.而在别的地方执行能正确返回结果.