Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
You want to use Oracle aggregate function XMLAGG for string aggregation.?Solution:ExampleSELECT class_id, rtrim(xmlagg(xmlelement(x, first_name || ' ' || last_name , ', ') ORDER BY first_name).extract('//text()').getstringval(), ', ') AS "names" FROM students GROUP BY class_id;OutputCL_MATH ANDERSON DANIEL, MARTINEZ CHRISTOPHER, TAYLOR PAUL, THOMAS MARK, WILSON THOMAS, CL_PHY RODRIGUEZ JOSEPH, INS_CHAIRMAN SMITH JAMES, INS_STAFF BROWN MICHAEL, DAVIS RICHARD, GARCIA CHARLES, JONES WILLIAM, MILLER DAVID, INS_VP JOHNSON JOHN, WILLIAMS ROBERT, In the above statement xmlelement will create an XML element called x (name it what ... Read More
Problem Statement:You want to perform string concatenation as a comma delimited text in oracle.Solution:Oracle has few methods to perform string aggregation. The most common usuage you popularly find on internet is to convert multiple rows to a single row with a delimiter.Starting Oracle version 11.2, a new built-in function is added called listagg which performs string aggregation. The listagg function uses two parameters the first is the string column or expression we want to aggregate and the second which is an optional parameter is the delimiter to put between the strings in the aggregated result. within group is mandatory and ... Read More
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 assign these values.ExampleSELECT student_id, first_name, last_name, fees, RANK() OVER (ORDER BY fees DESC) AS rnk FROM students;Outputstudent_idfirst_namelast_namefeesrnk100SMITHJAMES240001101JOHNSONJOHN170002102WILLIAMSROBERT170002108RODRIGUEZJOSEPH120084103BROWNMICHAEL90005109WILSONTHOMAS90005110MARTINEZCHRISTOPHER82007112TAYLORPAUL78008111ANDERSONDANIEL77009113THOMASMARK690010104JONESWILLIAM600011105MILLERDAVID480012106DAVISRICHARD480012107GARCIACHARLES420014RANK behaves similar to any other analytic function, operating in a second pass over the result set once non analytic processing is ... Read More
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, which will allow us store results from often-used queries in memory for quick and easy retrieval.ExampleSELECT /*+ result_cache */ e.class_id, min_fees, max_fees FROM students e ,jobs j WHERE e.class_id = j.class_id GROUP BY e.class_id, min_fees, max_fees;To demonstrate how it is used, we will check ... Read More
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 the performance savings using the APPEND hint.SQL without OptimizationINSERT INTO students SELECT * FROM students_bkp;Output-- Output 22141998 rows created. Elapsed: 00:03:11.21 ------------------------------------------------- | Id | Operation | Name | ------------------------------------------------- | 0 | INSERT STATEMENT ... Read More
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 format generated or register the schema based on how you want to generate the xml .The registration provides two key features. First, it allows Oracle to identify the external location or locations from which it can source the schema. Second and most important, REGISTERSCHEMA parses the schema for syntactical correctness ... Read More
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 of an student as an XML attribute to the root elementand add detail on fees, specifying the payment period as an attribute, and adding currency details.Oracle functions XMLROOT, XMLELEMENT, and XMLATTRIBUTE provide full control over the structure of your desired XML.XMLROOT provides the necessary XML header to turn our results ... Read More
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 table tmp_xml_gen from which we wanted to extract the customer names.Example 134 taylor.cauchon@internalmail Taylor Cauchon 921 COMPLETE ... Read More
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 is stored in a table tmp_xml_gen from which we wanted to extract the elements. We have customer details along with order related information.Example 134 taylor.cauchon@internalmail Taylor Cauchon 921 ... Read More
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 XMLTYPE data casting.We will use the XMLTYPE call to cast the text provided into the XMLTYPE datatype. In the background Oracle XMLTYPE supports CLOB datatype, because XML is stored internally as a CLOB. This means we can use the same approach to casting, passing the call to XMLTYPE a string ... Read More