How do I convert the contents of multiple tables to XML in SQL*Plus?

Question

I have an assignment where I need to write the XML for multiple tables(17). The professor told us we could use DBMS_XMLGEN and linked us to an example PL/SQL script to do it. The problem is that I don't want to rewrite or copy-paste the script 17 times. The script stores the XML content in a table with a field of type CLOB, and then we can use the SPOOL command to write the contents to a file.

This is what I've written so far:

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'); //I belive the error is on this line
  utl_file.put(fhandle, temp_clob_tab);
  utl_file.fclose(fhandle);
 END LOOP;
END;
/

The problem is that I can't use SPOOL within a PL/SQL block, but from googling for what felt like hours last night, I found that I could use UTL_FILE but I keep getting errors. These are the output I get:

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:

I'm using SQL developer, but the error messages don't seem that useful. Any help is appreciated!

utl_file writes to a file on the database server, not on your computer. I doubt that is what you want. Populate the table with pl/sql, then use a sql script to spool to a local file as explained [here](https://stackoverflow.com/questions/16722089/oracle-sql-developer-spool-output) — Koen Lostrie, Dec 02 '21 at 13:11
I understand that is the use of utl_file, but on the UTL page, I link they explained how to switch UTL_DIR to another location like 'c:\temp'. Also, wouldn't I still have to run the spool 17 time manually? — Michael Rivera, Dec 02 '21 at 13:29
There oracle database does not know about your client computer. That example is probably on an environment where the database is installed on a windows machine, probably even that users laptop. About the 17 times - you can generate a script using sql too... — Koen Lostrie, Dec 02 '21 at 14:42

score 0 · Answer 1 · answered Dec 02 '21 at 15:25

As the commenters explained, PL/SQL only runs on the database server, so it can't access your local client files. SPOOL is a Sql*Plus command, which does run on your client computer.

The easiest option is just to spool all of the XML into one file (and then split it up in a text editor).

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'); 
 
BEGIN
FOR i IN 1..tableNameArray.COUNT 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);

 END LOOP;
END;
/

SET HEADING OFF
SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF

SPOOL all_files_combined.xml
select result from temp_clob_tab;

There's not really a way to do a loop in SQL*Plus (or in SQLCL, which is actually what SQL Developer uses iirc). But you can call other scripts, so you could do something hacky, like:

set verify off

DROP TABLE temp_clob_tab; 
CREATE TABLE temp_clob_tab (tab_name varchar2(20), 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..tableNameArray.COUNT 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(tableNameArray(i), result);
  --Close context
  DBMS_XMLGEN.closeContext(qryCtx);

 END LOOP;
END;
/

SET HEADING OFF
SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF

SPOOL nested_script.sql
select 'spool ' || tab_name || '.xml' || chr(10)
    || 'select result from temp_clob_tab where tab_name = ' || tab_name || ';' || chr(10)
    || 'spool off' as cmd 
    from temp_clob_tab;
spool off
@nested_script.sql

I haven't actually tested that, but I think it would probably work.

How do I convert the contents of multiple tables to XML in SQL*Plus?

1 Answers1