
Let's Develop in Oracle
1,164 FOLLOWERS
This blog provides useful information and Tips on Oracle database, Oracle SQL, Oracle PL/SQL, DBMS job scheduler, oracle performance tuning, Oracle SQL Query Optimization, Oracle Interview Questions, Various Oracle Error Codes, Oracle Regular Expressions and String Aggregation for Oracle DBA and Database Developers.
Let's Develop in Oracle
3y ago
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 create a running total for the complete dataset, PARTITION BY is not required, we just need to add ORDER BY in OVER analytic clause
SQL> select d.deptno, d.dname, empno, ename, hiredate, sal, 2 sum(sal) over(order by hiredate) running_total 3 from emp e, dept d 4 where e.deptno = d.deptno; DE ..read more
Let's Develop in Oracle
3y ago
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 clause, which determines the range of rows used to perform the calculations.
In last post, we calculated TOTAL SAL in EMP table against each row, using
select d.deptno, d.dname, empno, ename, sum(sal) over() totalfrom emp e, dept dwhere e.deptno = d.deptno;
Now, I want to modify TOTAL SAL to DEPARTMENT ..read more
Let's Develop in Oracle
3y ago
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 WINDOW ( partition by), which made Oracle to process all rows in table.
select d.deptno, d.dname, empno, ename, sum(sal) over() totalfrom emp e, dept dwhere e.deptno = d.deptno; DEPTNO DNAME EMPNO ENAME TOTAL---------- -------------- ---------- ---------- ---------- 10 ACCOUNTING ..read more
Let's Develop in Oracle
3y ago
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 19c
- Oracle Database 18c
- Oracle Database 12c Release 2 (12.2)
Top New features in Oracle Database 21c:
- Blockchain Tables
- Immutable Tables
- Oracle Machine Learning
- Native JSON Datatype
- SQL Macros
- In database JavaScript execution
- Compare explain plans / cursors
We all should be aware that Inno ..read more
Let's Develop in Oracle
4y ago
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;to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM') *ERROR at line 2:ORA-01821: date format not recognized
but it was failing with ORA-01821: date format not recognized.
There are 2 issues in above SQL.
1. The format in the SQL is itself incorrect. The correct format is 'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'.
SQ ..read more
Let's Develop in Oracle
4y ago
Star Patterns by SQL in Oracle -
nimish@garg> select lpad('*',level,'*') from dual connect by level <= 5;LPAD('*',LEVEL,'*')---------------------------------------------------------------------------------***************nimish@garg> select lpad('*',5-level+1,'*') from dual connect by level <= 5;LPAD('*',5-LEVEL+1,'*')---------------------------------------------------------------------------------***************nimish@garg> select lpad(' ',5-level,' ') || lpad('*',level,'*') from dual connect by level <= 5;LPAD('',5-LEVEL,'')||LPAD('*',LEVEL ..read more
Let's Develop in Oracle
4y ago
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 OVERFLOW TRUNCATE clause.
With Oracle 19c Database LISTAGG function can also remove the duplicate values by using DISTINCT keyword. This feature was requested by many developers from the inception of LISTAGG function, and now we do not need to write complex SQL to remove the duplicates from the list.
Following ..read more
Let's Develop in Oracle
4y ago
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 in archivelog mode, then ARCH process copies the log file to ARCHIVE_LOG_DEST directory at the time of LOG SWITCH.
Following is the SQL which provides basic information about Redo Log files on my Oracle 18c XE database.
select log.thread#, log.group#, file.member, log.archived, log.status ..read more
Let's Develop in Oracle
4y ago
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.
Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" evaluates cloud DBMS products for their suitability to support following four operational use cases -
1) traditional transactions
2) augmented transaction processing
3) operational intelligence
4) stream/even ..read more
Let's Develop in Oracle
4y ago
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 releases:
- Oracle Database 19c
- Oracle Database 18c
- Oracle Database 12c Release 2 (12.2)
Oracle Database Innovation Releases include many enhancements and new capabilities which will be included in the next Long Term Release. Oracle Database Innovation Releases does not have extended support. As Oracle Database 21c is ..read more