Problem:You want to perform case-insensitive search in Oracle.SolutionOne way to deal with case issues is to use the built in UPPER and LOWER functions. These functions let you force case conversion on a string for a single operationExampleDECLARE    full_name1  VARCHAR2(30) := 'roger federer';    full_name2   VARCHAR2(30) := 'ROGER ... Read More

Problem:You want to define a function in Oracle using WITH clause.SolutionStarting with Oracle Database 12.1, you can define functions as well as procedures within the same SQL statement in which the SELECT statement appears. This allows the context switch between the PL/SQL and SQL engines by allowing both steps to ... Read More

Problem:You want to capture Oracle errors in PL/SQL.SolutionAll Oracle errors can be trapped with the help of the OTHERS exception handler. Let us take an example. We will be using students table to demonstrate the usage.Let us first look at the table structure.ExampleDESC students;OutputName          Null   ... Read More

Problem:You want to generate JSON data and perform Schema Validation in Oracle.SolutionFunctions like JSON_OBJECT, JSON_ARRAYAGG, JSON_QUERY can be used to generate a complex json data using mutiple columns and tables.JSON_OBJECT:- Its an SQL/JSON function. JSON_OBJECT takes as its input one or more property key-value pairs. It returns a JSON object ... Read More

Problem:You want to use datetime functions to extract or process datetimes.SolutionADD_MONTHS()ADD_MONTHS(x, y) returns the result of adding y months to x. If y is negative, then y months are subtracted from xExample-- add 12 months SELECT ADD_MONTHS('01-JAN-2020', 12) FROM dual;Output-- 01/JAN/21Example-- subtract 12 months SELECT ADD_MONTHS('01-JAN-2020', -12) FROM dual;Output-- 01/JAN/21We ... Read More

Problem:You want to insert dates into a table and retrieve them.SolutionOracle, by default the uses the format DD-MON-YYYY to represent a date, whereDD represents a two-digit day.MON represents first three letters of the month, e.g., FEB.YYYY is a four-digit year, e.g., 1968Let us take a look at an example of ... Read More

Problem:You want to UNPIVOT results in Oracle.SolutionThe UNPIVOT clause is new for Oracle Database 11g and enables you to flip the columns into rows in the output from a query, and, at the same time, allow you to run an aggregation function on the data.Consider a table called customer which ... Read More

Problem:You want to PIVOT results in Oracle.SolutionThe PIVOT clause is new for Oracle Database 11g and enables you to flip the rows into columns in the output from a query, and, at the same time, allow you to run an aggregation function on the data.PIVOT is especially useful to look ... Read More

Problem:You want to monitor the usage of the temporary tablespace in Oracle.Solution:We can use the following query to find out the used and free space in a temporary tablespace.We will begin with identifying temporary table space names.ExampleSELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';OutputTEMPNext up we will use below SQL ... Read More

Problem:You want to find out the recent important waits in your database, as well as the users, SQL statements, and objects that are responsible for most of those waits.SolutionOracle provides us with V$ACTIVE_SESSION_HISTORY to get information about the most common wait events, and the SQL statements, database objects, and users ... Read More