Re: Getting well-formatted XML using SQL

From: John Haskins <donoteventrytospamme_at_thisaddress.com>
Date: Sun, 20 Jun 2004 15:49:31 -0700
Message-ID: <m6WdnW-PXsYGiUvdRVn_iw@adelphia.com>

Thanks Daniel. Unfortunately the demo at the site you referred does not appear to work.

SQL> DECLARE

  2    v_ctx  dbms_xmlgen.ctxHandle;
  3    v_file Utl_File.File_Type;
  4    v_xml  CLOB;
  5    v_more BOOLEAN := TRUE;

  6 BEGIN
  7 v_ctx := DBMS_XMLGen.newContext(

  8      'SELECT table_name, tablespace_name
  9       FROM user_tables
 10       WHERE rownum < 6');

 11 dbms_xmlgen.setRowsetTag(v_ctx, 'USER_TABLES');  12 dbms_xmlgen.setRowTag(v_ctx, 'TABLE');  13 v_xml := DBMS_XMLGen.GetXML(v_ctx);  14 dbms_xmlgen.closeContext(v_ctx);
 15 v_file := Utl_File.fopen('C:\', 'test1.xml', 'w');  16 WHILE v_more LOOP
 17 utl_file.put(v_file, SUBSTR(v_xml, 1, 32767));  18 END LOOP;
 19 utl_file.fclose(v_file);
 20 END;
 21 /
DECLARE
*
ERROR at line 1:

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 15

I have a hard time believing C:\ is an invalid path! Something else must be wrong. I've looked at length but cannot find the problem.

Any further thoughts would be very welcome.

Thanks.

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1086757032.537327_at_yasure...
> John Haskins wrote:
> > I'm writing to ask for help on Oracle's XML capabilities. The output I'm
> > getting does not have CrLf sequences between values, so instead of the
> > nicely formatted lists shown in Oracle's documentation, I'm getting
> > output that while technically correct, is difficult to read and not
> > really what we have come to expect.
> >
> > For example, this bit of code is from the Oracle docs, which show output
> > that is nicely formatted with indents that reflect the data hierarchy.
> > However, what I get is one long, continuous line of output per record.
> >
> > SQL> CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
> > 2 ENAME VARCHAR2(10));
> > 3 /
> >
> > Type created.
> >
> > SQL>
> > SQL> CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
> > 2 /
> >
> > Type created.
> >
> > SQL>
> > SQL> CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2),
> > 2 DNAME VARCHAR2(14),
> > 3 EMP_LIST emplist_t);
> > 4 /
> >
> > Type created.
> >
> > SQL>
> > SQL> SELECT XMLElement("Department",
> > 2 dept_t(deptno,
> > 3 dname,
> > 4 CAST(MULTISET(select empno,
> > 5 ename
> > 6 from scott.emp e
> > 7 where e.deptno = d.deptno
> > 8 )
> > 9 AS emplist_t
> > 10 )
> > 11 )
> > 12 ) AS deptxml
> > 13 FROM scott.dept d
> > 14 WHERE d.deptno = 10;
> >
> > DEPTXML
>
> --------------------------------------------------------------------------

---

> > <Department><DEPT_T
> > DEPTNO="10"><DNAME>ACCOUNTING</DNAME><EMP_LIST><EMP_T EMPNO=
> >
> >
> >
> > If you have any thoughts on how to produce more readable output using
> > just SQL, I'd sure appreciate hearing them.
>
> My first thought is that it is irrelevant ... but try the demos here:
>
> http://www.psoug.org/reference/dbms_xmlgen.html
>
> and let us know if you are still having problems.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Sun Jun 20 2004 - 17:49:31 CDT