Re: XSU for PL/SQL or Java Question

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 23 Jul 2005 02:11:09 -0700
Message-ID: <1122109869.168710.67180@f14g2000cwb.googlegroups.com>

Have a look at schema validated XMLTYPE.

Re your specific query, see SQLPlus trace below.

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429

SQL> CREATE TABLE my_table(col1 NUMBER,col2 DATE, col3 CLOB);

Table created.

SQL> INSERT INTO my_table
  2 VALUES (1,SYSDATE,'test');

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL>
SQL> DECLARE

  2 ctx NUMBER;
  3 myclob CLOB;
  4 BEGIN
  5 ctx := DBMS_XMLQUERY.NEWCONTEXT('SELECT * FROM MY_TABLE');   6 myclob := DBMS_XMLQUERY.GETXML(ctx);   7 DBMS_OUTPUT.PUT_LINE(myclob);
  8 END;
  9 /
<?xml version = '1.0'?>
<ROWSET>

   <ROW num="1">

      <COL1>1</COL1>

<COL2>7/23/2005 10:1:23</COL2>

      <COL3>test</COL3>
   </ROW>
</ROWSET>

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT *

  2 FROM V$VERSION; BANNER

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

g3000 wrote:

> if I have this
>
> my_table
> col1     number
> col2     date
> col3     clob
>
> ctx := dbms_xmlquery.set_context("Select * from my_table");
>
> myclob := dbms_xmlquery.getXml( ctx);
>
> what would col3 data look like in the returned XML document?
>
> The answer is <col3> (CLOB) </col3>
>
> What I want to know is how can I get the actual ASCII characters for
> col3 instead of the word CLOB in parenthesis?

Received on Sat Jul 23 2005 - 04:11:09 CDT