代码之家 › 专栏 › 技术社区 › 90mph

要输出为XML的SQL查询

plsql powershell oracle xml

90mph · 技术社区 · 6 年前

作为试图将此数据拉入Powershell的大型项目的一部分,我正在使用PL/SQL脚本从Oracle中拉取撤消值(当前值和建议值),并输出到XML文件,然后可以使用Powershell进行解析。最后,我还希望建议的undo参数返回一个运行查询的时间戳,以比较它在一段时间内的变化。

但此时,我正在尝试如下输出:

<currundo>903</currundo>
<recundo>14400</recundo>

运行此pl/sql时,我得到:

ERROR at line 5:
ORA-06550: line 5, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

代码如下:

set heading off

DECLARE
    l_xmltype XMLTYPE;

BEGIN
select dbms_xmlgen.getxml('SELECT SUBSTR(e.value,1,25) "curundo", ROUND(d.undo_size / (to_number(f.value) * g.undo_block_per_sec)) "recundo"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = ''UNDO''
           AND c.status = ''ONLINE''
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) into d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) into g
WHERE e.name = ''undo_retention''
  AND f.name = ''db_block_size''') from dual;

END;
/

1 回复 | 直到 6 年前

Connor McDonald 6 年前

我修复了您的“into”引用和数据类型以生成此

SQL> DECLARE
  2      l_xmltype clob;
  3
  4  BEGIN
  5  select dbms_xmlgen.getxml('SELECT SUBSTR(e.value,1,25) "curundo",
  6        ROUND(d.undo_size / (to_number(f.value) * g.undo_block_per_sec)) "recundo"
  7    FROM (
  8         SELECT SUM(a.bytes) undo_size
  9            FROM v$datafile a,
 10                 v$tablespace b,
 11                 dba_tablespaces c
 12           WHERE c.contents = ''UNDO''
 13             AND c.status = ''ONLINE''
 14             AND b.name = c.tablespace_name
 15             AND a.ts# = b.ts#
 16         )  d,
 17         v$parameter e,
 18         v$parameter f,
 19         (
 20         SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 21                undo_block_per_sec
 22           FROM v$undostat
 23         )  g
 24  WHERE e.name = ''undo_retention''
 25    AND f.name = ''db_block_size''')
 26    into l_xmltype from dual;
 27
 28  END;
 29  /

PL/SQL procedure successfully completed.

当然,这只是将输出放入变量中。如果你想让它出现在屏幕上,你只需在没有PLSQL的情况下进行选择,例如

SQL> spool myfile.out
SQL> select dbms_xmlgen.getxml('SELECT SUBSTR(e.value,1,25) "curundo",
  2    ROUND(d.undo_size / (to_number(f.value) * g.undo_block_per_sec)) "recundo"
  3    FROM (
  4         SELECT SUM(a.bytes) undo_size
  5            FROM v$datafile a,
  6                 v$tablespace b,
  7                 dba_tablespaces c
  8           WHERE c.contents = ''UNDO''
  9             AND c.status = ''ONLINE''
 10             AND b.name = c.tablespace_name
 11             AND a.ts# = b.ts#
 12         )  d,
 13         v$parameter e,
 14         v$parameter f,
 15         (
 16         SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 17                undo_block_per_sec
 18           FROM v$undostat
 19         )  g
 20  WHERE e.name = ''undo_retention''
 21    AND f.name = ''db_block_size''')
 22    from dual;

DBMS_XMLGEN.GETXML('SELECTSUBSTR(E.VALUE,1,25)"CURUNDO",ROUND(D.UNDO_SIZE/(TO_NU
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <curundo>900</curundo>
  <recundo>41668</recundo>
 </ROW>
</ROWSET>