How to use LONG column in the WHERE clause
As developers, we often query the data dictionary for details about views, triggers, and other objects. Oracle stores large text data like object definitions in LONG columns, which […]
More DetailsCategory: SQL
How to use XMLTABLE
XMLTABLE is a powerful feature in Oracle databases that transforms XML data into relational format for easy querying. I would like to begin with comprehensive example. In the […]
More DetailsHow to delete duplicate rows
Basically, there are several ways to do this. In this post, I show two different ways. Let’s start with a basic example. Set Up Checking if the table […]
More DetailsHow to use dynamic SQL with DBMS_SQL
There may be situations where we need to run dynamic queries in our developments. We can utilize execute immediate statement or DBMS_SQL package in order to use dynamic […]
More DetailsORA-01704 string literal too long
ORA-01704 error occurs when a string literal more than 4000 characters in SQL. In the example below the string length is exactly 4000 characters. So, if one character […]
More DetailsNVL vs COALESCE
As it is known, NVL and COALESCE functions are used in order to eliminate NULL values. However, NVL function is much more popular in comparison with the COALESCE. […]
More DetailsHow to use MULTISET UNION DISTINCT on collections
Let’s start with a basic example. Suppose, you have two PL/SQL collections with the same data type that you want to merge and remove the repetitive data. You […]
More DetailsHow to use PL/SQL functions within SQL
As it is known, invoking PL/SQL code from SQL cause context switching. SQL is a non-procedural language and it is very different from PL/SQL language. In addition, PL/SQL […]
More DetailsThe power of MATCH_RECOGNIZE
MATCH_RECOGNIZE is one of the most powerful feature of SQL. There are several use cases, such as finding series of consecutive rows, pattern matching, etc. Once you understand […]
More DetailsLISTAGG Enhancements
If you consider to aggregate data from multiple rows into a single column, LISTAGG might be the first function that comes to your mind. It has been enhanced […]
More Details