Author: Dev

Dense_Rank DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. Syntax: DENSE_RANK( expression ) WITHIN GROUP ( ORDER BY expression ) DENSE_RANK( ) OVER ([query_partition_clause] order_by_clause) Example 1: select DENSE_RANK(1000, 300) WITHIN GROUP (ORDER BY SAL,…(Continue Reading)

Listagg LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. Syntax: LISTAGG (measure_expression [, ‘delimiter’]) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)] Example: SELECT LISTAGG(e.ENAME, ‘; ‘) WITHIN GROUP (ORDER BY e.ENAME) FROM EMP e WHERE e.DEPTNO=10; Output: CLARK; KING; MILLER

ORA-01950: no privileges on tablespace USERS Oracle SQL Error: ORA-01950: no privileges on tablespace ‘USERS’ Cause: User does not have privileges to allocate an extent in the specified tablespace. Solution: Grant the user the appropriate system privileges or grant the user space resource on the tablespace. Example: Insert into DEPARTMENTS (DEP_ID,DEP_NAME) values (‘1′,’aaa’); Output: SQL…(Continue Reading)

ORA-01017: invalid username/password; logon denied Oracle SQL Error: ORA-01017: invalid username/password; logon denied Cause: An attempt was made to logon on database schema. Solution: Check if the username and password are correct. Check if the user has the right to create session. Example: CREATE USER test3 IDENTIFIED BY test_user3; GRANT create session TO test3; Output:…(Continue Reading)

ORA-65096 invalid common user or role name Oracle SQL Error: ORA-65096: invalid common user or role name Cause: An attempt was made to create a common user or role with a name that was not valid for common users or roles. Solution: Ask the database administrator to perform the operation or grant the required privileges…(Continue Reading)