SQL*Plus中将多表内容转换为XML导出时的报错如何解决？

有一项作业需要为17张表生成对应的XML，教授告知可以使用DBMS_XMLGEN实现，还提供了PL/SQL示例脚本，该脚本会将生成的XML内容存入含CLOB类型字段的表中，再通过SPOOL命令写入文件。不想重复编写或复制粘贴17次脚本，因此编写了批量处理代码，但运行时报错。

原始代码 #

SET SERVEROUTPUT ON;
set verify off;

DROP TABLE temp_clob_tab; 
CREATE TABLE temp_clob_tab (result CLOB);

DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
  type array_t is varray(17) of varchar2(20);
  tableNameArray array_t := array_t('DEPT', 'EMP', 'BONUS', 'SALGRADE', 'DUMMY', 'CUSTOMER',
  'ORD', 'ITEM', 'PRODUCT', 'PRICE', 'MANAGER'); 
  fhandle utl_file.file_type;
 
BEGIN
FOR i IN 1..17 LOOP
  qryCtx := DBMS_XMLGEN.newContext(
              'SELECT * FROM '||tableNameArray(i));
  -- Set the row header to be the name of table
  DBMS_XMLGEN.setRowTag(qryCtx, tableNameArray(i));
  -- Get the result
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  --Close context
  DBMS_XMLGEN.closeContext(qryCtx);
  
  fhandle := utl_file.fopen(
  'UTL_DIR', tableNameArray(i)||'.xml', 'w'); -- 原代码此处用了//注释不符合PL/SQL语法
  utl_file.put(fhandle, temp_clob_tab);
  utl_file.fclose(fhandle);
 END LOOP;
END;
/

错误信息 #

Table TEMP_CLOB_TAB dropped.

Table TEMP_CLOB_TAB created.

Error starting at line : 7 in command -
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
  type array_t is varray(17) of varchar2(20);
  tableNameArray array_t := array_t('DEPT', 'EMP', 'BONUS', 'SALGRADE', 'DUMMY', 'CUSTOMER',
  'ORD', 'ITEM', 'PRODUCT', 'PRICE', 'MANAGER'); 
  fhandle utl_file.file_type;
  /*xml_Table temp_clob_tab%TYPE; */
 
BEGIN
FOR i IN 1..17 LOOP
  qryCtx := DBMS_XMLGEN.newContext(
              'SELECT * FROM '||tableNameArray(i));
  -- Set the row header to be the name of table
  DBMS_XMLGEN.setRowTag(qryCtx, tableNameArray(i));
  -- Get the result
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  --Close context
  DBMS_XMLGEN.closeContext(qryCtx);
  
  /*SELECT * INTO xml_Table FROM temp_clob_tab;*/
  
  fhandle := utl_file.fopen(
  'UTL_DIR', tableNameArray(i)||'.xml', 'w');
  utl_file.put(fhandle, temp_clob_tab);
  utl_file.fclose(fhandle);
 END LOOP;
END;
Error report -
ORA-06550: line 26, column 25:
PLS-00357: Table,View Or Sequence reference 'TEMP_CLOB_TAB' not allowed in this context
ORA-06550: line 26, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

核心错误原因 #

报错PLS-00357的直接原因是utl_file.put(fhandle, temp_clob_tab);这一行，你直接把表名TEMP_CLOB_TAB当作参数传给了utl_file.put，PL/SQL不允许直接将表名作为变量值传入函数。实际上你已经将生成的XML内容存储在result变量中，完全不需要先插入临时表再读取，可以直接使用result变量写入文件。

其他需要修复的问题 #

1. 数组定义的长度为17，但你当前只初始化了11个表名，写死循环1..17会触发数组下标越界错误，建议改为用tableNameArray.COUNT动态获取数组长度作为循环上限。
2. utl_file.put单次最多写入32767字节的内容，如果生成的XML大于32K会触发报错，需要补充CLOB分段写入逻辑。
3. 注意PL/SQL的单行注释符是--，不要用//。
4. UTL_DIR是数据库服务端的目录对象，生成的文件会存储在数据库服务器对应的路径下，不会存储在你运行SQL Developer的本地电脑上，如果需要生成本地文件可以改用SPOOL+DBMS_OUTPUT的方案。
5. 运行代码前需要确认当前用户有UTL_FILE的使用权限，以及对UTL_DIR目录的读写权限。

修复后的完整代码 #

SET SERVEROUTPUT ON;
SET VERIFY OFF;

-- 临时表可以省略，此处保留兼容原逻辑，也可以直接删除相关代码
DROP TABLE temp_clob_tab; 
CREATE TABLE temp_clob_tab (result CLOB);

DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
  type array_t is varray(17) of varchar2(20);
  -- 补全17个表名后再运行，当前示例为11个
  tableNameArray array_t := array_t('DEPT', 'EMP', 'BONUS', 'SALGRADE', 'DUMMY', 'CUSTOMER',
  'ORD', 'ITEM', 'PRODUCT', 'PRICE', 'MANAGER'); 
  fhandle utl_file.file_type;
  v_offset PLS_INTEGER := 1;
  v_buffer VARCHAR2(32767);
  v_clob_len PLS_INTEGER;
BEGIN
-- 动态获取数组长度作为循环上限，避免越界
FOR i IN 1..tableNameArray.COUNT LOOP
  qryCtx := DBMS_XMLGEN.newContext(
              'SELECT * FROM '||tableNameArray(i));
  -- 设置行标签为表名
  DBMS_XMLGEN.setRowTag(qryCtx, tableNameArray(i));
  -- 生成XML
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  DBMS_XMLGEN.closeContext(qryCtx);
  
  -- 打开文件
  fhandle := utl_file.fopen('UTL_DIR', tableNameArray(i)||'.xml', 'w', 32767);
  -- 分段写入CLOB
  v_clob_len := DBMS_LOB.GETLENGTH(result);
  v_offset := 1;
  WHILE v_offset <= v_clob_len LOOP
    DBMS_LOB.READ(result, 32767, v_offset, v_buffer);
    utl_file.put(fhandle, v_buffer);
    utl_file.fflush(fhandle);
    v_offset := v_offset + 32767;
  END LOOP;
  utl_file.fclose(fhandle);
  
  COMMIT;
 END LOOP;
END;
/

内容的提问来源于stack exchange，提问作者Michael Rivera