Home » Developer & Programmer » JDeveloper, Java & XML » How to get the metadata (in xml format) of all the fileds in SQl query ? (Oracle 11g)
How to get the metadata (in xml format) of all the fileds in SQl query ? [message #544502] Wed, 22 February 2012 02:21 Go to next message
sajadparacha
Messages: 4
Registered: February 2012
Location: Khobar
Junior Member

Good day ,

I am using the dbms_xmlgen.getXMLfunction to get the result of any query in xml format.

With this XML I also want the metadata information about all the fields used in the query (passed to getXML function). Is it possible and how can I achieve this.

I tried to Google it but couldn't find any solution , it's easy to do it in java where I can get the resultset meta data from the resultset but I have to do it in Oracle function since I want the result in xml format and want to use the oracle XML API.

You may think why I need metadata , the reason is the application will later use this information to sort the data contained in these fields according to their data type provided to.

Regards

Sajjad Ahmed Paracha
Re: How to get the metadata (in xml format) of all the fileds in SQl query ? [message #544576 is a reply to message #544502] Wed, 22 February 2012 10:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You can use dbms_metadata.get_xml, as in the example below.

SCOTT@orcl_11gR2> SELECT DBMS_METADATA.GET_XML ('TABLE', 'DEPT')
  2  FROM   DUAL
  3  /

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>254698</OBJ_NUM>
 <SCHEMA_OBJ>
  <OBJ_NUM>254698</OBJ_NUM>
  <DATAOBJ_NUM>254698</DATAOBJ_NUM>
  <OWNER_NUM>84</OWNER_NUM>
  <OWNER_NAME>SCOTT</OWNER_NAME>
  <NAME>DEPT</NAME>
  <NAMESPACE>1</NAMESPACE>
  <TYPE_NUM>2</TYPE_NUM>
  <TYPE_NAME>TABLE</TYPE_NAME>
  <CTIME>2011-12-25 10:38:06</CTIME>
  <MTIME>2012-01-09 03:11:20</MTIME>
  <STIME>2011-12-25 10:38:06</STIME>
  <STATUS>1</STATUS>
  <FLAGS>0</FLAGS>
  <SPARE1>6</SPARE1>
  <SPARE2>1</SPARE2>
  <SPARE3>84</SPARE3>
  <OWNER_NAME2>SCOTT</OWNER_NAME2>
 </SCHEMA_OBJ>
 <STORAGE>
  <FILE_NUM>4</FILE_NUM>
  <BLOCK_NUM>1434</BLOCK_NUM>
  <TYPE_NUM>5</TYPE_NUM>
  <TS_NUM>4</TS_NUM>
  <BLOCKS>8</BLOCKS>
  <EXTENTS>1</EXTENTS>
  <INIEXTS>8</INIEXTS>
  <MINEXTS>1</MINEXTS>
  <MAXEXTS>2147483645</MAXEXTS>
  <EXTSIZE>128</EXTSIZE>
  <EXTPCT>0</EXTPCT>
  <USER_NUM>84</USER_NUM>
  <LISTS>1</LISTS>
  <GROUPS>1</GROUPS>
  <BITMAPRANGES>2147483645</BITMAPRANGES>
  <CACHEHINT>0</CACHEHINT>
  <SCANHINT>0</SCANHINT>
  <HWMINCR>254698</HWMINCR>
  <FLAGS>4325633</FLAGS>
 </STORAGE>
 <TS_NAME>USERS</TS_NAME>
 <BLOCKSIZE>8192</BLOCKSIZE>
 <DATAOBJ_NUM>254698</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>5</BLKCNT>
 <EMPCNT>0</EMPCNT>
 <AVGSPC>0</AVGSPC>
 <CHNCNT>0</CHNCNT>
 <AVGRLN>20</AVGRLN>
 <AVGSPC_FLB>0</AVGSPC_FLB>
 <FLBCNT>0</FLBCNT>
 <ANALYZETIME>25-DEC-11</ANALYZETIME>
 <SAMPLESIZE>4</SAMPLESIZE>
 <INTCOLS>3</INTCOLS>
 <KERNELCOLS>3</KERNELCOLS>
 <PROPERTY>536870912</PROPERTY>
 <PROPERTY2>0</PROPERTY2>
 <XMLSCHEMACOLS>N</XMLSCHEMACOLS>
 <TRIGFLAG>0</TRIGFLAG>
 <SPARE1>736</SPARE1>
 <SPARE6>25-DEC-11</SPARE6>
 <COL_LIST>
  <COL_LIST_ITEM>
   <OBJ_NUM>254698</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>0</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>254698</OBJ_NUM>
   <COL_NUM>2</COL_NUM>
   <INTCOL_NUM>2</INTCOL_NUM>
   <SEGCOL_NUM>2</SEGCOL_NUM>
   <PROPERTY>0</PROPERTY>
   <NAME>DNAME</NAME>
   <TYPE_NUM>1</TYPE_NUM>
   <LENGTH>14</LENGTH>
   <NOT_NULL>0</NOT_NULL>
   <CHARSETID>873</CHARSETID>
   <CHARSETFORM>1</CHARSETFORM>
   <BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
   <BASE_COL_TYPE>0</BASE_COL_TYPE>
   <SPARE1>0</SPARE1>
   <SPARE2>0</SPARE2>
   <SPARE3>14</SPARE3>
  </COL_LIST_ITEM>
  <COL_LIST_ITEM>
   <OBJ_NUM>254698</OBJ_NUM>
   <COL_NUM>3</COL_NUM>
   <INTCOL_NUM>3</INTCOL_NUM>
   <SEGCOL_NUM>3</SEGCOL_NUM>
   <PROPERTY>0</PROPERTY>
   <NAME>LOC</NAME>
   <TYPE_NUM>1</TYPE_NUM>
   <LENGTH>13</LENGTH>
   <NOT_NULL>0</NOT_NULL>
   <CHARSETID>873</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/>
 <CON2_LIST/>
 <REFPAR_LEVEL>0</REFPAR_LEVEL>
</TABLE_T>

 </ROW></ROWSET>


1 row selected.


Re: How to get the metadata (in xml format) of all the fileds in SQl query ? [message #544985 is a reply to message #544576] Sat, 25 February 2012 23:39 Go to previous messageGo to next message
sajadparacha
Messages: 4
Registered: February 2012
Location: Khobar
Junior Member

Hello ,

Thanks for your reply , the problem here is that i want to get the output of any query in xml format doing this is simple using oracl's xml API the problem is I also need to have the complete metadata information about the all the fields in query I used to get the XML data (That query could be a simple select or complex query with multiple joins).

The application which will actually be using this xml data will also need the metadata information about all the fields so that it can sort them according to their data types.

The solution you suggested is to get the metadata for a table only or please guide me if there is another way to use it to get what I want.
Re: How to get the metadata (in xml format) of all the fileds in SQl query ? [message #544988 is a reply to message #544985] Sun, 26 February 2012 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you post an example of you want.

Regards
Michel
Re: How to get the metadata (in xml format) of all the fileds in SQl query ? [message #544990 is a reply to message #544985] Sun, 26 February 2012 01:35 Go to previous messageGo to next message
sajadparacha
Messages: 4
Registered: February 2012
Location: Khobar
Junior Member

So if the following call get me the data mentioned below

select dbms_xmlgen.getXMLType('SELECT * FROM tbl_clas where ID=1 ') from dual;


<ROWSET>
<ROW>
<NAME>cl1</NAME>
<ID>1</ID>
</ROW>
</ROWSET>

I want to have the metadat information for the fields in this query as well , something like below

<ROWSET>
<METADATA>
<COLUMN name="NAME">
<DATATYPE>varchar2</DATATYPE>
<MAX_LENGTH>256</MAX_LENGTH>
</COLUMN>
<COLUMN name="ID">
<DATATYPE>NUMBER</DATATYPE>
<MAX_LENGTH>22</MAX_LENGTH>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COLUMN>

</METADATA>
<ROW>
<NAME>cl1</NAME>
<ID>1</ID>
</ROW>
</ROWSET>
Re: How to get the metadata (in xml format) of all the fileds in SQl query ? [message #544998 is a reply to message #544990] Sun, 26 February 2012 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is it allowed for you to create a procedure (and one that returns such result)?
I ask this because many say that can't create any procedure or object and have to use the predefined ones.

Regards
Michel
Re: How to get the metadata (in xml format) of all the fileds in SQl query ? [message #545013 is a reply to message #544998] Sun, 26 February 2012 06:26 Go to previous messageGo to next message
sajadparacha
Messages: 4
Registered: February 2012
Location: Khobar
Junior Member

Yes it is allowed here Smile.
Re: How to get the metadata (in xml format) of all the fileds in SQl query ? [message #545020 is a reply to message #545013] Sun, 26 February 2012 12:55 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The following uses dbms_sql to parse the query and get the column descriptions, then combines that with the xml, within a user-defined function.

-- test table and test data:
SCOTT@orcl_11gR2> CREATE TABLE tbl_clas
  2    (name   VARCHAR2 (256),
  3  	id     NUMBER	( 22,4),
  4  	other  DATE)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO tbl_clas VALUES ('cl1', 1, SYSDATE)
  3  INTO tbl_clas VALUES ('cl2', 2, SYSDATE)
  4  SELECT * FROM DUAL
  5  /

2 rows created.


-- function:
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION xml_with_metadata
  2    (p_query IN VARCHAR2)
  3    RETURN	   XMLTYPE
  4  AS
  5    v_cur	   INTEGER;
  6    v_exec	   INTEGER;
  7    v_col_cnt   INTEGER;
  8    v_rec_tab   DBMS_SQL.DESC_TAB;
  9    v_result    CLOB;
 10  BEGIN
 11    v_cur := DBMS_SQL.OPEN_CURSOR;
 12    DBMS_SQL.PARSE (v_cur, p_query, DBMS_SQL.NATIVE);
 13    v_exec := DBMS_SQL.EXECUTE (v_cur);
 14    DBMS_SQL.DESCRIBE_COLUMNS (v_cur, v_col_cnt, v_rec_tab);
 15    v_result := v_result || '<ROWSET><METADATA>';
 16    FOR i IN 1 .. v_col_cnt LOOP
 17  	 v_result := v_result
 18  	 || '<COLUMN name="' || v_rec_tab(i).col_name || '"><DATATYPE>'
 19  	 || CASE v_rec_tab(i).col_type
 20  	      -- list below may not be complete and may contain errors:
 21  	      WHEN   1 THEN 'VARCHAR2'
 22  	      WHEN   2 THEN 'NUMBER'
 23  	      WHEN   8 THEN 'LONG'
 24  	      WHEN  12 THEN 'DATE'
 25  	      WHEN  21 THEN 'BINARY_FLOAT'
 26  	      WHEN  22 THEN 'BINARY_DOUBLE'
 27  	      WHEN  23 THEN 'RAW'
 28  	      WHEN  24 THEN 'LONG RAW'
 29  	      WHEN  69 THEN 'ROWID'
 30  	      WHEN  96 THEN 'CHAR'
 31  	      WHEN 112 THEN 'CLOB'
 32  	      WHEN 113 THEN 'BLOB'
 33  	      WHEN 114 THEN 'BFILE'
 34  	      WHEN 180 THEN 'TIMESTAMP'
 35  	      WHEN 181 THEN 'TIMESTAMP WITH TIME ZONE'
 36  	      WHEN 182 THEN 'INTERVAL YEAR TO MONTH'
 37  	      WHEN 183 THEN 'INTERVAL DAY TO SECOND'
 38  	      WHEN 208 THEN 'UROWID'
 39  	      WHEN 231 THEN 'TIMESTAMP WITH LOCAL TIME ZONE'
 40  	    END
 41  	 || '</DATATYPE><MAX_LENGTH>' || v_rec_tab(i).col_max_len || '</MAX_LENGTH>'
 42  	 || CASE WHEN v_rec_tab(i).col_precision > 0 THEN
 43  	      '<PRECISION>' || v_rec_tab(i).col_precision
 44  	      || '</PRECISION><SCALE>'
 45  	      || v_rec_tab(i).col_scale || '</SCALE>'
 46  	    END
 47  	 || '</COLUMN>';
 48    END LOOP;
 49    DBMS_SQL.CLOSE_CURSOR(v_cur);
 50    SELECT v_result || '</METADATA>' ||
 51  	      LTRIM (DBMS_XMLGEN.GETXMLTYPE (p_query), '<ROWSET>')
 52    INTO   v_result
 53    FROM   DUAL;
 54    RETURN XMLTYPE (v_result);
 55  END xml_with_metadata;
 56  /

Function created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.


-- usage of function:
SCOTT@orcl_11gR2> SELECT xml_with_metadata
  2  	      ('SELECT name, id FROM tbl_clas WHERE id = 1')
  3  FROM   DUAL
  4  /

XML_WITH_METADATA('SELECTNAME,IDFROMTBL_CLASWHEREID=1')
--------------------------------------------------------------------------------
<ROWSET>
  <METADATA>
    <COLUMN name="NAME">
      <DATATYPE>VARCHAR2</DATATYPE>
      <MAX_LENGTH>256</MAX_LENGTH>
    </COLUMN>
    <COLUMN name="ID">
      <DATATYPE>NUMBER</DATATYPE>
      <MAX_LENGTH>22</MAX_LENGTH>
      <PRECISION>22</PRECISION>
      <SCALE>4</SCALE>
    </COLUMN>
  </METADATA>
  <ROW>
    <NAME>cl1</NAME>
    <ID>1</ID>
  </ROW>
</ROWSET>


1 row selected.

[Updated on: Sun, 26 February 2012 13:14]

Report message to a moderator

Previous Topic: Error while parsing XML file with special characters
Next Topic: BigDecimal issue
Goto Forum:
  


Current Time: Sat Nov 27 11:10:10 CST 2021