|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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  |
 |
Barbara Boehmer
Messages: 9103
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.