使用 PL/SQL 查看从 Oracle 函数返回的 SYS.XMLTYPE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2559871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
View a SYS.XMLTYPE returned from an Oracle function, using PL/SQL
提问by David
I have an Oracle function that dynamically creates an XML document, and returns it in a SYS.XMLTYPE value.
我有一个 Oracle 函数，它动态创建一个 XML 文档，并在 SYS.XMLTYPE 值中返回它。
I want to run a query from SQL Developer that calls that function and prints the document (either via a select, or dbms_output - I don't care).
我想从 SQL Developer 运行一个查询，该查询调用该函数并打印文档（通过选择或 dbms_output - 我不在乎）。
But all the examples/documentation seem to refer to querying XML columns in tables, and I can't seem to get the syntax right for my particular usage. I'd like something like this:
但是所有示例/文档似乎都涉及查询表中的 XML 列，而且我似乎无法获得适合我特定用法的语法。我想要这样的东西：
declare
x SYS.XMLTYPE;
begin
x := my_package.my_function();
select x.getclobval() from x; -- doesn't work!
end;
How can I print out the value of the XML type variable 'x' in the above code?
如何打印出上述代码中 XML 类型变量 'x' 的值？
采纳答案by APC
Here is a function which returns an XMLType...
这是一个返回 XMLType 的函数...
SQL> create or replace function get_emp_xml
2 (p_eno in emp.empno%type)
3 return xmltype
4 is
5 return_value xmltype;
6 begin
7 select value(emprec) as "EMP_REC"
8 into return_value
9 from table (xmlsequence
10 (cursor
11 ( select * from emp e
12 where e.empno = p_eno
13 )
14 )
15 ) emprec
16 ;
17
18 return return_value;
19 end;
20 /
Function created.
SQL>
Querying it from a SELECT statement is just as easy as you might hope it would be:
从 SELECT 语句中查询它就像您希望的那样简单：
SQL> set long 5000
SQL>
SQL> select get_emp_xml(8060) from dual
2 /
GET_EMP_XML(8060)
--------------------------------------------------------------------------
<ROW>
<EMPNO>8060</EMPNO>
<ENAME>VERREYNNE</ENAME>
<JOB>PLUMBER</JOB>
<MGR>7839</MGR>
<HIREDATE>08-APR-08</HIREDATE>
<SAL>4500</SAL>
<DEPTNO>50</DEPTNO>
</ROW>
SQL>
This also works in SQL Developer.
这也适用于 SQL Developer。
回答by Matthew Watson
Try this ( No guarantee, I haven't really used XML stuff )
试试这个（不能保证，我还没有真正使用过 XML 的东西）
declare
x SYS.XMLTYPE;
begin
x := my_package.my_function();
dbms_output.put_line ( x.getCLOBVal() );
end;
回答by kurosch
Depending on the length of your XML, and the limitations of dbms_output.put_line in your version of Oracle, you may need to wrap it with a little bit of intelligence like so:
根据您的 XML 的长度，以及您的 Oracle 版本中 dbms_output.put_line 的限制，您可能需要像这样用一点智能来包装它：
PROCEDURE put_string
(
p_text IN CLOB
)
IS
v_index INTEGER;
BEGIN
IF p_text IS NULL THEN
RETURN;
END IF;
v_index := instr(p_text, chr(10));
IF v_index > 0 AND v_index < 256 THEN
dbms_output.put_line(substr(p_text, 1, v_index-1));
put_string(substr(p_text, v_index+1));
ELSE
IF length(p_text) <= 255 THEN
dbms_output.put_line(p_text);
ELSE
dbms_output.put_line(substr(p_text, 1, 255));
put_string(substr(p_text, 256));
END IF;
END IF;
END;
