Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Software & Coding Articles
Page 58 of 83
How to perform Schema Registration and XML Validation in Oracle ?
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 MoreHow to generate XML with nested values ?
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 MoreHow to extract Key XML Elements from an XML Document in Oracle?
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 MoreHow to extract XML data for relational use?
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 MoreHow to translate SQL data to XML in Oracle?
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 to convert rows to XML.The SYS_XMLGEN function returns a block of XML based on a row/s or row like expression. SYS_XMLGEN can take a literal value, a single column, or a user defined type as input parameter.We will be creating XML from students data for an student record.We will begin ...
Read MoreHow to write a common procedure to find and remove duplicates from any table and columns in Oracle?
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 below.delete from table where rowid in (... query here ...)To demonstrate the usage, we will begin by creating sample data.Example-- table with tennis player rankings DROP TABLE atp_stats; CREATE TABLE atp_stats ( player_rank NUMBER NOT NULL, player_name VARCHAR2(100) NOT NULL, time_range TIMESTAMP(6)); -- sample records ...
Read MoreHow to validate the syntax of a Oracle dynamic SQL before execution ?
Problem Statement:You want to validate the syntax of a SQL before execution.Solution: The DBMS_SQL default package allows SQL to be dynamically executed. Owned by SYS it has been defined with the AUTHID CURRENT_USER keyword so it runs with the privileges of the invoker. We can leverage DBMS_SQL.PARSE function to validate the syntax.We will first define a function to accept the SQL statement as the parameter and parse the SQL statement./* * --------------------------------------------------------------------------- * Function : check_syntax * Input : sql statement * Output : Number * --------------------------------------------------------------------------- */ FUNCTION check_syntax ( p_query IN CLOB ) ...
Read MoreHow to produce group subtotals for all combinations of the dimensions specified in Oracle?
Problem Statement:You want to find out subtotals for all combinations of the dimensions specified in Oracle.Solution:The CUBE function will generate subtotals for all combinations of the dimensions specified. If “n” is the number of columns listed in the CUBE, there will be 2n subtotal combinations.We will begin by create the necessary data for this requirement.Example-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles ( player VARCHAR2(100) NOT NULL, title_type VARCHAR2(100) NOT NULL, titles NUMBER NOT NULL);Example-- insert ...
Read MoreHow to produce group subtotals and a grand total in Oracle?
Problem Statement:You want to find out totals, subtotals and a grand total in Oracle.Solution:Oracle ROLLUP function performs grouping at multiple levels, using a right to left method of rolling up through intermediate levels to any grand total. To demonstrate the ROLLUP function we will create a table to hold tennis player along with the ATP tour titles and Grandslam titles acheived by the player.We will begin by create the necessary data for this requirement.Example-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles ( player VARCHAR2(100) NOT NULL, title_type ...
Read MoreHow to find and replace text in Oracle?
Problem Statement: You want to find and replace a string in Oracle.Solution:Function: TRANSLATESyntax: TRANSLATE(expr, from_string, to_string)TRANSLATE funtion in oracle allows you to make many single character, one on one substitutions in a single operation.However, the to_string and from_string values must not be empty, if an empty string is passed to TRANSLATE Function, Oracle Database interprets the empty string as null and returns null./* replace all occurances of b with j in below string */ SELECT 'back and bill ' AS original_string , TRANSLATE('back and bill', 'b', 'j') AS replaced_string FROM dual;Outputback and bill jack and ...
Read More