- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Kiran P has Published 123 Articles
Kiran P
334 Views
Problem Statement:You want to assign a number/rank representing their positions in the result.Solution:Oracle provides the RANK analytic function to generate a ranking number for rows in a result set. To demonstrate we will rank students by fees, from highest paid down. The following SELECT statement uses the rank function to ... Read More
Kiran P
2K+ Views
Problem Statement:You want to improve the performance of frequently used queries.Solution:We have to use Oracle’s result cache to store the query results of frequently used SQL, so they can be retrieved quickly for future use when the same query has been executed.The result cache is new additon to Oracle 11g, ... Read More
Kiran P
2K+ Views
Problem Statement:You are performing a INSERT statement, and it is performing slower than needed. You want to optimize the INSERT statement.Solution:By using the APPEND or APPEND_VALUES hint with INSERT statement, we can significantly speed up the process of performing an insert operation on the database. Here is an example of ... Read More
Kiran P
900 Views
Problem Statement:You want to enforce XML schema validity on XML data stored in your database.Solution:Oracle provides the DBMS_XMLSCHEMA.REGISTERSCHEMA function to define XML schemas within the Oracle database. Inorder to validate the xml data generated, we need to register the schema. While registering the schema, the format must match the xml ... Read More
Kiran P
1K+ Views
Problem Statement:You want to generate a complex XML document with nesting values at different levels.Solution: Oracle has quite a number of functions and procedures for generating XML from regular data. To demonstrate the usage, I will be using the studentdata. We will assume we would like to use the student_id ... Read More
Kiran P
5K+ Views
Problem Statement:You need to extract a portion/subset of elements values from an XML document.Solution:We can leverage Oracle’s EXTRACT function which supports XMLTYPE datatype. EXTARCT function provides the ability to preserve XML element name and attribute constructs by returning the results as an XMLTYPE value.Assume, we have below XML in a ... Read More
Kiran P
472 Views
Problem Statement:You need to extract individual elements values from an XML document.Solution:Oracle provides the XMLTABLE function to manipulate XML documents using XQuery and column mapping to Oracle datatypes. Using XMLTABLE, we can identify and use data elements in an XML document in a relational way.Let us assume, below XML document ... Read More
Kiran P
2K+ Views
Problem Statement:You need to store native XML data into a relational table in your database.Solution:Oracle have several ways of storing XML documents. One way of storing data where our XML doesn’t need to be altered, or where a portion of the XML can be extracted with XSLT, is to use ... Read More
Kiran P
1K+ Views
Problem Statement:You need to convert data stored in traditional form in your database to XML document.Solution: Oracle have many different functions for converting data to XML format. Some of the functions Oracle provides for converting relational data to XML are the SYS_XMLGEN, DBMS_XMLGEN, and XMLELEMENT functions.We will be using SYS_XMLGEN ... Read More
Kiran P
476 Views
Problem Statement:You want to write a common procedure to find and remove duplicates from any table and columns in Oracle.Solution:We can use Oracle’s internal ROWID value for uniquely identifying rows in a table along with the OLAP function row_number with partition clause. The sample syntax to acheive this would like ... Read More