Problem Statement:You want to find and remove duplicates from a table in Oracle.Solution: We can use Oracle’s internal ROWID value for uniquely identifying rows in a table. The sample syntax to acheive this would like below.delete from table where rowid in   (... query here ...)To demonstrate the usage, we ... Read More

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 ... Read More

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 ... Read More

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 ... Read More

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 ... Read More

Problem Statement: How to identify leaf rows in a Hierarchy table i.e. rows that are not parents of any other rows.Solution: Oracle provides CONNECT_BY_ISLEAF clause to identify rows that are not parents of any other rows. To begin with let us see how does a connect_by_isleaf works.SQL:/*   Function - ... Read More

Problem Statement: You need to traverse the hierarchy data from top to bottom but doesn’t want a particular branch in the output.Solution: We will look at couple of examples for this problem statement.Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child ... Read More

Problem Statement: You need to traverse the hierarchy data from top to bottom and sort the rows at the same level in a hierarchy.Solution: The usual ORDER BY clause will not sort the rows at the same hierarchy level. We need to use SIBLINGS with in the ORDER BY Clause.Additionally, ... Read More

Problem Statement: You need to traverse the hierarchy data from top to bottom marking the level of each row in the hierarchy.Solution:Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between ... Read More

ProblemYou need to split a string into fields, but the delimiters aren’t consistent throughout the string.SolutionThere are multiple ways you can split a string or strings of multiple delimiters in python. The most and easy approach is to use the split() method, however, it is meant to handle simple cases.re.split() ... Read More