Kiran P has Published 123 Articles

How to assign ranks to the query results in Oracle?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:51:05

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

How to cache query results in Oracle?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:49:31

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

How to optimize the INSERT statement using direct-path insert technique in Oracle?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:46:58

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

How to perform Schema Registration and XML Validation in Oracle ?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:45:21

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

How to generate XML with nested values ?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:43:13

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

How to extract Key XML Elements from an XML Document in Oracle?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:42:05

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

How to extract XML data for relational use?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:40:47

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

How to store XML data in a table in Oracle?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:39:36

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

How to translate SQL data to XML in Oracle?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:37:09

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

How to write a common procedure to find and remove duplicates from any table and columns in Oracle?

Kiran P

Kiran P

Updated on 04-Dec-2020 10:35:34

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

Previous 1 ... 5 6 7 8 9 ... 13 Next
Advertisements