Generate XML for table definiton [message #660319] |
Tue, 14 February 2017 15:12 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dba_vijay
Messages: 11 Registered: February 2009
|
Junior Member |
|
|
Hi Experts
I am trying to generate XMLs of table definitions.
I know how to use functions like XMLGEN for e.g. select dbms_xmlgen.getxml('select * from data_files') xml from dual;
But I want the describe table command output in an xml.
for example
Every tag below is a column in a table.
<?xml version="1.0" encoding="UTF-8"?>
<DOC_ID> 1</DOC_ID>
<COPY_ID> 1</COPY_ID>
<COPY_NUMBER> 1</COPY_NUMBER
<FORM_ABF> 1</FORM_ABF>
<FORM_TYPE> 1</FORM_TYPE>
<BILLING> 1</BILLING>
<CREATED_BY> 1</CREATED_BY>
<CREATED_DT> 1</CREATED_DT>
<UPDATED_BY> 1</UPDATED_BY>
<UPDATED_DT> 1</UPDATED_DT>
All inputs are appreciated.
|
|
|
|
|
Re: Generate XML for table definiton [message #660331 is a reply to message #660322] |
Wed, 15 February 2017 01:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:my intentions were to find if Oracle has any functions to do generate XML for SCHEMA definitions
It depends on what you mean by this.
Oracle has it (and use it in expdp/impdp), but is this what you want?
Example for table DEPT:
SQL> select dbms_metadata.get_xml('TABLE','DEPT') from dual;
DBMS_METADATA.GET_XML('TABLE','DEPT')
---------------------------------------------------------------------------------
<?xml version="1.0"?><ROWSET><ROW>
<TABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>3 </VERS_MINOR>
<OBJ_NUM>78607</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>78607</OBJ_NUM>
<DATAOBJ_NUM>78607</DATAOBJ_NUM>
<OWNER_NUM>73</OWNER_NUM>
<OWNER_NAME>MICHEL</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2011-01-27 20:59:38</CTIME>
<MTIME>2016-11-26 11:39:49</MTIME>
<STIME>2012-08-18 22:10:39</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
<SPARE3>73</SPARE3>
<OWNER_NAME2>MICHEL</OWNER_NAME2>
</SCHEMA_OBJ>
<STORAGE>
<FILE_NUM>4</FILE_NUM>
<BLOCK_NUM>10610</BLOCK_NUM>
<TYPE_NUM>5</TYPE_NUM>
<TS_NUM>4</TS_NUM>
<TRANSPORTABLE>0</TRANSPORTABLE>
<BLOCKS>8</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>8</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>73</USER_NUM>
<LISTS>1</LISTS>
<GROUPS>1</GROUPS>
<BITMAPRANGES>2147483645</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>78607</HWMINCR>
<FLAGS>4325633</FLAGS>
</STORAGE>
<TS_NAME>TS_D01</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<DATAOBJ_NUM>78607</DATAOBJ_NUM>
<COLS>3</COLS>
<PCT_FREE>10</PCT_FREE>
<PCT_USED>40</PCT_USED>
<INITRANS>1</INITRANS>
<MAXTRANS>255</MAXTRANS>
<FLAGS>1073742353</FLAGS>
<AUDIT_VAL>--------------------------------------</AUDIT_VAL>
<ROWCNT>4</ROWCNT>
<BLKCNT>4</BLKCNT>
<EMPCNT>0</EMPCNT>
<AVGSPC>0</AVGSPC>
<CHNCNT>0</CHNCNT>
<AVGRLN>20</AVGRLN>
<AVGSPC_FLB>0</AVGSPC_FLB>
<FLBCNT>0</FLBCNT>
<ANALYZETIME>2012/08/18 22:11:04</ANALYZETIME>
<SAMPLESIZE>4</SAMPLESIZE>
<INTCOLS>3</INTCOLS>
<KERNELCOLS>3</KERNELCOLS>
<PROPERTY>536870912</PROPERTY>
<PROPERTY2>0</PROPERTY2>
<XMLSCHEMACOLS>N</XMLSCHEMACOLS>
<TSTZ_COLS>N</TSTZ_COLS>
<TRIGFLAG>0</TRIGFLAG>
<SPARE1>736</SPARE1>
<SPARE6>2012/08/18 20:10:39</SPARE6>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>78607</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<PRECISION_NUM>2</PRECISION_NUM>
<SCALE>0</SCALE>
<NOT_NULL>1</NOT_NULL>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<BASE_INTCOL_NUM>1</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>78607</OBJ_NUM>
<COL_NUM>2</COL_NUM>
<INTCOL_NUM>2</INTCOL_NUM>
<SEGCOL_NUM>2</SEGCOL_NUM>
<PROPERTY>8388608</PROPERTY>
<NAME>DNAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>14</LENGTH>
<NOT_NULL>1</NOT_NULL>
<CHARSETID>178</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<CON>
<OWNER_NUM>73</OWNER_NUM>
<NAME>SYS_C0023957</NAME>
<CON_NUM>23957</CON_NUM>
<OBJ_NUM>78607</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>2012/08/18 22:10:39</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>14</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>78607</OBJ_NUM>
<COL_NUM>3</COL_NUM>
<INTCOL_NUM>3</INTCOL_NUM>
<SEGCOL_NUM>3</SEGCOL_NUM>
<PROPERTY>8388608</PROPERTY>
<NAME>LOC</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>13</LENGTH>
<NOT_NULL>0</NOT_NULL>
<CHARSETID>178</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<BASE_INTCOL_NUM>3</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>13</SPARE3>
</COL_LIST_ITEM>
</COL_LIST>
<CON0_LIST/>
<CON1_LIST>
<CON1_LIST_ITEM>
<OWNER_NUM>73</OWNER_NUM>
<NAME>DEPT_PK</NAME>
<CON_NUM>23953</CON_NUM>
<OBJ_NUM>78607</OBJ_NUM>
<PROPERTY>536870912</PROPERTY>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>2</CONTYPE>
<ENABLED>92640</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>2012/08/18 22:08:56</MTIME>
<FLAGS>4</FLAGS>
<OID_OR_SETID>0</OID_OR_SETID>
<COL_LIST>
<COL_LIST_ITEM>
<CON_NUM>23953</CON_NUM>
<OBJ_NUM>78607</OBJ_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<POS_NUM>1</POS_NUM>
<SPARE1>0</SPARE1>
<OID_OR_SETID>0</OID_OR_SETID>
<COL>
<OBJ_NUM>78607</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
</COL_LIST_ITEM>
</COL_LIST>
<IND>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>5 </VERS_MINOR>
<OBJ_NUM>92640</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>92640</OBJ_NUM>
<DATAOBJ_NUM>92640</DATAOBJ_NUM>
<OWNER_NUM>73</OWNER_NUM>
<OWNER_NAME>MICHEL</OWNER_NAME>
<NAME>DEPT_PK</NAME>
<NAMESPACE>4</NAMESPACE>
<TYPE_NUM>1</TYPE_NUM>
<TYPE_NAME>INDEX</TYPE_NAME>
<CTIME>2012-08-18 22:08:56</CTIME>
<MTIME>2012-08-18 22:08:56</MTIME>
<STIME>2012-08-18 22:08:56</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>0</SPARE1>
<SPARE2>65535</SPARE2>
<SPARE3>73</SPARE3>
<OWNER_NAME2>MICHEL</OWNER_NAME2>
</SCHEMA_OBJ>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>92640</OBJ_NUM>
<BO_NUM>78607</BO_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<COL>
<OBJ_NUM>78607</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
<POS_NUM>1</POS_NUM>
<SEGCOL_NUM>0</SEGCOL_NUM>
<SEGCOLLEN>0</SEGCOLLEN>
<OFFSET>0</OFFSET>
<FLAGS>0</FLAGS>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
<OID_OR_SETID>0</OID_OR_SETID>
</COL_LIST_ITEM>
</COL_LIST>
<TS_NAME>TS_D01</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<STORAGE>
<FILE_NUM>4</FILE_NUM>
<BLOCK_NUM>10978</BLOCK_NUM>
<TYPE_NUM>6</TYPE_NUM>
<TS_NUM>4</TS_NUM>
<TRANSPORTABLE>0</TRANSPORTABLE>
<BLOCKS>8</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>8</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>73</USER_NUM>
<LISTS>1</LISTS>
<GROUPS>1</GROUPS>
<BITMAPRANGES>2147483645</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>92640</HWMINCR>
<FLAGS>4325633</FLAGS>
</STORAGE>
<DATAOBJ_NUM>92640</DATAOBJ_NUM>
<BASE_OBJ_NUM>78607</BASE_OBJ_NUM>
<BASE_OBJ>
<OBJ_NUM>78607</OBJ_NUM>
<DATAOBJ_NUM>78607</DATAOBJ_NUM>
<OWNER_NUM>73</OWNER_NUM>
<OWNER_NAME>MICHEL</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2011-01-27 20:59:38</CTIME>
<MTIME>2016-11-26 11:39:49</MTIME>
<STIME>2012-08-18 22:10:39</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
<SPARE3>73</SPARE3>
<OWNER_NAME2>MICHEL</OWNER_NAME2>
</BASE_OBJ>
<ANC_OBJ>
<OBJ_NUM>78607</OBJ_NUM>
<DATAOBJ_NUM>78607</DATAOBJ_NUM>
<OWNER_NUM>73</OWNER_NUM>
<OWNER_NAME>MICHEL</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2011-01-27 20:59:38</CTIME>
<MTIME>2016-11-26 11:39:49</MTIME>
<STIME>2012-08-18 22:10:39</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
<SPARE3>73</SPARE3>
<OWNER_NAME2>MICHEL</OWNER_NAME2>
</ANC_OBJ>
<INDMETHOD_NUM>0</INDMETHOD_NUM>
<COLS>1</COLS>
<PCT_FREE>10</PCT_FREE>
<INITRANS>2</INITRANS>
<MAXTRANS>255</MAXTRANS>
<TYPE_NUM>1</TYPE_NUM>
<FLAGS>2050</FLAGS>
<PROPERTY>4097</PROPERTY>
<BLEVEL>0</BLEVEL>
<LEAFCNT>1</LEAFCNT>
<DISTKEY>4</DISTKEY>
<LBLKKEY>1</LBLKKEY>
<DBLKKEY>1</DBLKKEY>
<CLUFAC>1</CLUFAC>
<ANALYZETIME>2012/08/18 22:11:04</ANALYZETIME>
<SAMPLESIZE>4</SAMPLESIZE>
<ROWCNT>4</ROWCNT>
<INTCOLS>1</INTCOLS>
<NUMCOLSDEP>1</NUMCOLSDEP>
<SPARE6>2012/08/18 20:08:56</SPARE6>
<FOR_PKOID>0</FOR_PKOID>
<FOR_REFPAR>0</FOR_REFPAR>
<OID_OR_SETID>0</OID_OR_SETID>
</IND>
</CON1_LIST_ITEM>
</CON1_LIST>
<CON2_LIST/>
<REFPAR_LEVEL>0</REFPAR_LEVEL>
</TABLE_T>
</ROW></ROWSET>
You can choose more or less information you want from the object setting DBMS_METADATA options.
[Updated on: Wed, 15 February 2017 01:07] Report message to a moderator
|
|
|