Let's Develop in Oracle
- Website
- nimishgarg.blogspot.de · all feeds from this site
- Feed URL
https://nimishgarg.blogspot.com/feeds/posts/default
Read it in your FeedListing reader
Or add to: Feedly Inoreader Raw feed
Latest posts
-
Analytical SQL - Running Total - Order by without Partition By
Welcome to the third post on the series Analytical SQL . In the first two posts we studied Analytical Function without Window Clause Analytical Function Partition By In this post we will create a report showing a running total of the complete EMP table in order of HIREDATE. To…
-
Analytical SQL - SUM with Partition By
Welcome to the second post on the series Analytical SQL . In the first post we studied Analytical Function without Window Clause . With this post, we will learn Partition By . Analytical functions compute an aggregated value based on a group of rows defined as per Partition By…
-
Analytical SQL - SUM without Window Clause
This is my first post in 2022, and with this I am starting a new series on Analytical SQL . In The first example, we are trying to list all records from EMP and DEPT tables with the SUM of SAL column with each row. As you can see here, we have used "sum(sal) over()" without…
-
Oracle Database 21c for on-premises is here - New Features
Oracle Database 21c is the latest innovation release of world's most popular database. Oracle Database 21c is now available on Oracle cloud and on-premises for linux including Exadata. You can directly upgrade to Oracle Database 21c from the following releases: - Oracle Database…
-
Oracle: String to Date, Timestamp with Timezone
I recently got a text from a friend to convert '2021-04-07T14:03:54.000000-0700' string to date. He Tried - SQL> select 2 to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM') 3 from dual;…
-
Star Pattern by SQL in Oracle Database
Star Patterns by SQL in Oracle - nimish@garg> select lpad('*',level,'*') from dual connect by level select lpad('*',5-level+1,'*') from dual connect by level select lpad(' ',5-level,' ') || lpad('*',level,'*') from dual connect by level <= 5;…
-
LISTAGG DISTINCT - Remove Duplicates - Oracle 19c
LISTAGG function was introduced in Oracle 11g. LISTAGG function aggregates the result set in multiple rows into one single column. In Oracle 12c R2 LISTAGG function was enhanced to manage situations where the length of the concatenated string is too long by providing us ON…
-
Redo Files, Redo Group and Redo Thread in Oracle Database
Redo log files used to maintain logs of all transactions performed against the Oracle database mainly for disaster recovery. An Oracle database must have at least two redo log files. These files are written in a circular fashion by the LGWR process. If Oracle database is running…
-
Oracle Autonomous Database scored highest in Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" report
Oracle is named as a Leader in 2020 Gartner Magic Quadrant for Cloud Database Management Systems . Oracle Autonomous Transaction Processing (ATP) ranked highest in 2020 Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" report.…
-
Oracle Database 21c is here - Innovation Release
Oracle Database 21c is the latest innovation release of world's most popular database. Oracle Database 21c is initially available on Oracle cloud with Autonomous Database Free Tier and Database Cloud Service. You can directly upgrade to Oracle Database 21c from the following…
-
Generate Nested JSON using SQL in Oracle Database
It has been a long time, since I wrote my last post on Parse JSON data in Oracle Database . I got lot of questions on my last post mainly how did I create the nested JSON data for the demo. So with this post, I am sharing the SQL which I used to create the nested JSON data for…
-
Parse JSON data in Oracle Database using JSON_TABLE in SQL
Recently, I got opportunity to work on JSON data in Oracle Database . We were receiving this JSON data from Cisco Webex APIs, and the JSON data was complex (nested with json array). With this blog, I am sharing simple example how to parse complex JSON data in Oracle Database…
-
Automatic Indexing in Oracle 19c Autonomous Database
With Oracle Database 19c , The Automatic Indexing feature automates the index management by allowing Oracle Database to automatically create, rebuild and drop indexes based on application workload . Oracle Database runs the automatic indexing process in the background, analyzes…
-
SQL to calculate PI using Nilakantha Series
On the Pi Day , I wrote a SQL to calculate PI(π) with accuracy upto 10 decimal digits using Nilakantha Series . When I shared it with my friends on Twitter and Whatsapp, many of them suggested me write a post on it. As per Wikipedia , The calculation of PI(π) was revolutionized…
-
SQL Puzzle - Calendar of Current Year
With this post, I want to share a SQL Puzzle to Generate the Complete Calendar of Current Year . As you can see in the desired output - Month names should not be repeated - For each month we should have a separate header. The solution to generate the calendar of current year is…
-
AVG Aggregate Function and NULL in Oracle Database
In my previous post on Oracle Aggregate Functions , I explained Oracle Database ignores NULL values with Aggregate Functions , which is perfect while calculating COUNT, MIN, MAX and SUM but sometimes it leads us to wrong values if we do not focus on Oracle Database behavior and…
-
Oracle Aggregate Functions - Count Sum Avg Min Max
In continuation of basics yet important topics of Oracle Database , with this post, I am trying to explain Oracle Aggregate Functions with Group By and Having clause . Oracle Aggregate functions operate on multiple rows and return a single value for each group. Oracle Aggregate…
-
Constraint to Validate Data and Optimize SQL - Manual Partition
I always suggest developers to add constraints on tables in Oracle Database, as constraints not only validate the data, but they also helps Oracle Database Optimizer to choose better execution plan and hence tune the SQL. Surprised? Let me share my experience with constraints in…
-
ORA-01843: not a valid month - NLS_DATE_FORMAT
"ORA-01843: not a valid month" is a very common exception which we all might have faced while working with Dates in Oracle Database. ORA-01843 occurs due to implicit date conversion which is Basics yet Important feature of Oracle Database. With this blog I am trying to explain…
-
Datatype is Important for Good Execution Plan and SQL Performance
In application programming, choosing the correct data-type is very important for the performance. It is also true with Oracle Database, a correct datatype for column in Oracle Database Table is very important for Good Execution Plan and SQL Performance . With this blog, I am…