Re: Bug in dbms_metadata.get

From: <g.entelmann_at_t-online.de>
Date: 10 May 2004 00:17:59 -0700
Message-ID: <390eb482.0405092317.74d2fd82@posting.google.com>

"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message news:<jZKmc.25526$TT.24971_at_news-server.bigpond.net.au>...
> <g.entelmann_at_t-online.de> wrote in message
> news:390eb482.0405060138.1c733f77_at_posting.google.com...
> > Hello,
> >
> > I have a case in Oracle9i where dbms_metadata.get_xml does not work
> > corectly.
> >
> > When I try to extract the metadata for one of my views I get
> > (by executing "select dbms_metadata.get_xml('VIEW', 'WAAGEN')from
> > dual"):
> >
> > <?xml version="1.0"?>
> > <ROWSET>
> > <ROW>
> > <VIEW_T>
> > <VERS_MAJOR>1</VERS_MAJOR>
> > <VERS_MINOR>0 </VERS_MINOR>
> > <OBJ_NUM>31271</OBJ_NUM>
> > <SCHEMA_OBJ>
> > <OBJ_NUM>31271</OBJ_NUM>
> > <OWNER_NUM>65</OWNER_NUM>
> > <OWNER_NAME>VSAM_YEOMAN</OWNER_NAME>
> > <NAME>WAAGEN</NAME>
> > <NAMESPACE>1</NAMESPACE>
> > <TYPE_NUM>4</TYPE_NUM>
> > <TYPE_NAME>VIEW</TYPE_NAME>
> > <CTIME>19.04.2004 10:29:09</CTIME>
> > <MTIME>29.04.2004 10:25:19</MTIME>
> > <STIME>29.04.2004 10:25:19</STIME>
> > <STATUS>1</STATUS>
> > <FLAGS>0</FLAGS>
> > <SPARE1>6</SPARE1>
> > <SPARE2>65535</SPARE2>
> > </SCHEMA_OBJ>
> > <AUDIT_VAL>--------------------------------</AUDIT_VAL>
> > <COLS>11</COLS>
> > <INTCOLS>11</INTCOLS>
> > <PROPERTY>0</PROPERTY>
> > <FLAGS>0</FLAGS>
> > <TEXTLENGTH>128</TEXTLENGTH>
> > <TEXT>SELECT NETNAME, DEVICE, ID, BEZEICHNUNG, NUMMER, ORT, NK,
> > FREIGABE, SPERRTEXT1, SPERRTEXT2, TYP
> > FROM PERIPHERIE
> > WHERE TYP < 256INTCOL_NUM>1</INTCOL_NUM>
> > <SEGCOL_NUM>1</SEGCOL_NUM>
> > <PROPERTY>0</PROPERTY>
> > <NAME>NETNAME</NAME>
> > <TYPE_NUM>1</TYPE_NUM>
> > </COL_LIST_ITEM>
> > .... (rest left away)
> >
> >
> > It is obvious, that the element <TEXT> is not closed ...
> >
> > Is this a known issue, or am i using an older buggy version of the
> > package ?
> >
> > Many thanks in advance
> > Gerrit
> >
> > PS: I was using SQL*Plus with long = 9999999 and linesize = 10000
>
> Gerrit,
>
> I tried a similar thing on 10.1.0.2 and 9.2.0.1 on WinXP Pro SP1 and I could
> not reproduce your error. My output in part is from iSQL*Plus:
> <TEXTLENGTH>284< /TEXTLENGTH><TEXT> SELECT object_ name, subobject_name,
> object _id, data_object_id, object_ type, created, last_ddl_time ,
> timestamp, status, temporary, generated, secondary FROM user_objects WHERE
> object_name = 'EMP'</TEXT>
>
> Douglas Hawthorne

Hello Douglas,

thank you very much for your answer. I am using 9.2.0.1 on WinXP Pro SP1 also
and still can reproduce my error.
You can use this statements:
CREATE TABLE "PERIPHERIE2"

(	"ID" NUMBER(10,0) NOT NULL PRIMARY KEY, 
	"THE_NETNAME" VARCHAR2(35), 
	"THE_DEVICE" VARCHAR2(16), 
	"BEZEICHNUNG" VARCHAR2(35), 
	"NUMMER" NUMBER(10,0), 
	"ORT" VARCHAR2(1), 
	"NK" NUMBER(10,0), 
	"FREIGABE" NUMBER(10,0), 
	"SPERRTEXT1" VARCHAR2(35), 
	"SPERRTEXT2" VARCHAR2(35), 
	"TYP" NUMBER(10,0), 
	"C_USER" VARCHAR2(10), 
	"M_USER" VARCHAR2(10), 
	"CREATD" DATE, 
	"MODIFD" DATE, 
	"ID_EXTERN" VARCHAR2(16)

);

CREATE VIEW "WAAGEN2" AS
SELECT THE_NETNAME, THE_DEVICE, ID, BEZEICHNUNG, NUMMER, ORT, NK, FREIGABE, SPERRTEXT1, SPERRTEXT2, TYP
FROM PERIPHERIE2
WHERE TYP < 256;

When executing "select dbms_metadata.get_xml('VIEW', 'WAAGEN2') from dual;"
i still get:
...

<TEXTLENGTH>137</TEXTLENGTH>
<TEXT>SELECT THE_NETNAME, THE_DEVICE, ID, BEZEICHNUNG, NUMMER, ORT, NK, FREIGABE, SPERRTEXT1, SPERRTEXT2, TYP FROM PERIPHERIE2

WHERE TYP &lt; 256     <INTCOL_NUM>1</INTCOL_NUM>
     <SEGCOL_NUM>1</SEGCOL_NUM>
     <PROPERTY>0</PROPERTY>

...

Can you verify this on your System ??

Very many thanks in advance
Gerrit Received on Mon May 10 2004 - 02:17:59 CDT

Re: Bug in dbms_metadata.get_xml ?