Ask Anantha

Oracle suports XML handling through the built in datatype XMLTYPE. Here is an example of handling XML data to convert xml to object types and viceversa. Consider the following XML <customer> <id>100</id> <name>XEROX</name> <country>FRANKFURT</country> <status>ACTIVE</status> </customer> Create a sample object type customer which contains the same elements as the XML data using the following command: CREATE OR REPLACE TYPE CUSTOMER AS OBJECT ( ID VARCHAR2(10) , NAME VARCHAR2(50) , COUNTRY VARCHAR2(50), STATUS VARCHAR2(15)) / The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents. DECLARE v_in customer; v_xml xmltype; BEGIN v_xml :=XMLTYPE('<c...

What is XML? XML stands for eXtensible Mark up Language. It is classified as an extensible language because it allows its users to define their own tags. XML was developed to provide a universal format for describing structured documents and data. There are no fixed tags for XML. Any user can add his own set of tags. The tags though are similar to HTML, they do differ by the way it is presented. Unlike HTML, which tags elements in Web pages for presentation by a browser, e.g. Oracle , XML tags elements as data, e.g. Oracle . In this example HTML identifies as a command to display the data within as Bold. But in case of XML, the company for instance can be a column name in a database and Oracle is the column value. Why do we use XML? As XML is W3C(World Wide Web Consortium) standard, various software companies have openly accepted and implemented it in their operations. It is a fee-free open standard. It is platform-independent, language-independent, textual data. XML can be used with...

Use this query to get the record count of all tables in a schema. select table_name, to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name) ),'/ROWSET/ROW/C')) count from user_tables order by 1 The output is like table_name count ----------- -------- DEPT 4 EMP 14 Courtesy: http://laurentschneider.com/wordpress/2007/04/ how-do-i-store-the-counts-of-all-tables.html