oracle 从 PL/SQL 开发人员读取 XML 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5854209/
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
Reading XML file from PL/SQL developer
提问by Avi
Below is the procedure which i have taken from net..but while executing its giving me error that "xmlgen.setRowsetTag must be declared"
下面是我从网络上获取的程序..但是在执行它时给我的错误是“必须声明 xmlgen.setRowsetTag”
Please tell me if this error is due to some setting mistakes or i have to include some files.. I am using PL/SQL developer 10g
请告诉我这个错误是由于某些设置错误还是我必须包含一些文件。我正在使用 PL/SQL developer 10g
Thankss:-
谢谢：-
create or replace procedure SP_XML_TEST is
begin
declare
xmlString CLOB := null;
-- Here we are reading 250 bytes at a time. We should be really reading a
-- whole chunk. dbms_output.put_line can only accomodate 256 characters per line
-- so we have this limitation.
amount integer:= 255;
position integer := 1;
charString varchar2(255);
begin
xmlgen.setRowTag('EMP_ROW'); -- we want the row element to be named EMP_ROW.
xmlgen.setRowsetTag('EMP_RESULTS'); -- we want the result document root to be EMP_RESULTS.
xmlgen.setMaxRows(3); -- limit the output to 3 rows.
xmlgen.setskipRows(2); -- skip the first two rows in the query before outputing results.
xmlgen.useLowerCaseTagNames(); -- set the tag names to be all in lower case.
xmlgen.setErrorTag('ERROR_RESULT'); -- set the ERROR tag to be ERROR_RESULTS.
xmlgen.setRowIdAttrName('ENO'); -- set the id attribute in the ROW element to be ENO.
xmlgen.setRowIdColumn('EMPNO'); -- use the EMPNO column's value for the id attribute.
xmlgen.useNullAttributeIndicator(false); -- do not use the null indicator to indicate nullness.
xmlgen.setStyleSheet('http://www.oracle.com/xsl'); -- attach the stylesheet PI to the result document.
xmlString := xmlgen.getXML('select * from scott.emp ',1); -- This gets the XML out
dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY); -- Now open the lob data..
loop
dbms_lob.read(xmlString,amount,position,charString); -- read the lob data
dbms_output.put_line(charString);
position := position + amount;
end loop;
exception
when no_data_found then
dbms_lob.close(xmlString); -- end of fetch, free the lob
dbms_lob.freetemporary(xmlString);
xmlgen.resetOptions;
when others then
xmlgen.resetOptions;
end;
end SP_XML_TEST;
回答by APC
The problem with picking stuff up from Das Interweb is that quality control can be very poor. In particular you need to pay attention to version numbers. You're on Oracle 10g. I think XMLGEN was introduced in the XDK for Oracle 8i; it was deprecated in favour of the PL/SQL package DBMS_XMLGENin 9i, and (as far as I know) hasn't been included in the database for some years.
从 Das Interweb 上取货的问题在于质量控制可能非常差。尤其需要注意版本号。您使用的是 Oracle 10g。我认为 XMLGEN 是在 Oracle 8i 的 XDK 中引入的；它被9i中的 PL/SQL 包DBMS_XMLGEN弃用，并且（据我所知）已经有几年没有包含在数据库中了。
DBMS_XMLGEN does similar sorts of things to XMLGEN, although some of the procedures have slightly different signatures and some have been dropped altogether. I have rewritten the posted code to use DBMS_XMLGEN.
DBMS_XMLGEN 与 XMLGEN 做类似的事情，尽管有些过程的签名略有不同，有些已经完全删除。我已经重写了发布的代码以使用 DBMS_XMLGEN。
create or replace procedure SP_XML_TEST is
xmlString CLOB;
amount integer:= 255;
position integer := 1;
charString varchar2(255);
len pls_integer;
l_ctx dbms_xmlgen.ctxHandle;
begin
l_ctx := dbms_xmlgen.newcontext('select * from apc.emp');
dbms_xmlgen.setRowTag(l_ctx, 'EMP_ROW'); -- we want the row element to be named EMP_ROW.
dbms_xmlgen.setRowsetTag(l_ctx, 'EMP_RESULTS'); -- we want the result document root to be EMP_RESULTS.
dbms_xmlgen.setMaxRows(l_ctx, 3); -- limit the output to 3 rows.
dbms_xmlgen.setskipRows(l_ctx, 2); -- skip the first two rows in the query before outputing results.
xmlString := dbms_xmlgen.getXML(l_ctx ); -- This gets the XML out
dbms_output.put_line('rows read = '||to_char(dbms_xmlgen.GETNUMROWSPROCESSED(l_ctx)));
len := dbms_lob.getlength (xmlString);
dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY); -- Now open the lob data..
loop
dbms_lob.read(xmlString,amount,position,charString); -- read the lob data
dbms_output.put_line(charString);
position := position + amount;
EXIT when position > len;
end loop;
dbms_lob.close(xmlString);
dbms_xmlgen.closecontext(l_ctx);
end SP_XML_TEST;
/
And lo!
还有！
SQL> exec SP_XML_TEST
rows read = 3
<?xml version="1.0"?>
<EMP_RESULTS>
<EMP_ROW>
<EMPNO>8085</EMPNO>
<ENAME>TRICHLER</ENAME>
<JOB>PLUMBER</JOB>
<MGR>8061</MGR>
<HIREDATE>08-APR-10</HIREDATE>
<SAL>3500</SAL>
<DEPTNO>50</DEPTNO>
</EMP_ROW>
<EMP_ROW>
<EMPNO>7369</EMPNO>
<
ENAME>CLARKE</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</EMP_ROW>
<EMP_ROW>
<EMPNO>7499</EMPNO>
<ENAME>VAN WIJK</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>20
-FEB-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</EMP_ROW>
</EMP_RESULTS>
Incidentally, something else which has changed since the original coder hacked that procedure is the limitation on the DBMS_OUTPUT buffer. In 10g it can go up to 32767. Find out more.
顺便说一句，自从原始编码员入侵该程序以来发生的其他变化是对 DBMS_OUTPUT 缓冲区的限制。在 10g 中它可以达到 32767。 了解更多信息。
