Category Archives: PL/SQL

Learn PLSQL Tutorial

Sys_Connect_By_Path

Sys_Connect_By_Path SYS_CONNECT_BY_PATH returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition. SYS_CONNECT_BY_PATH is valid only in hierarchical queries. Syntax: SYS_CONNECT_BY_PATH( column, char ) Example: SELECT SYS_CONNECT_BY_PATH(ENAME, ‘/’) “Path” FROM EMP START WITH ENAME = ‘BLAKE’ CONNECT BY PRIOR EMPNO = MGR; Outpout:… Read More »

Rank

Rank RANK calculates the rank of a value in a group of values. The return type is NUMBER. Syntax: RANK( expression ) WITHIN GROUP ( ORDER BY expression ) RANK( ) OVER ([query_partition_clause] order_by_clause) Example 1: SELECT RANK(1000, 300) WITHIN GROUP (ORDER BY SAL, COMM) from EMP; Example 2: SELECT e.EMPNO, e.ENAME, e.DEPTNO, e.SAL, RANK() OVER (PARTITION BY… Read More »

Dense_Rank

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, COMM) from EMP;… Read More »

Listagg

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; Outpout: CLARK; KING; MILLER

ORA-01950 no privileges on tablespace USERS

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’); Outpout: SQL Error: ORA-01950: no… Read More »

ORA-01017: invalid username/password logon denied

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; Outpout: User TEST3 created.… Read More »

ORA-65096 invalid common user or role name

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 or specify a… Read More »

PLS-00487 Invalid reference to variable

PLS-00487 Invalid reference to variable Oracle SQL Error: PLS-00487 Invalid reference to variable Cause: A variable was referenced in a way that is inconsistent with its datatype. Solution: Check the spelling of the variable name. Make sure the variable was declared properly and that the declaration and reference are consistent regarding datatype. Example: DECLARE CURSOR c1 IS SELECT… Read More »

PLS-00310 with %ROWTYPE attribute, must name a table, cursor or cursor-variable

PLS-00310 with %ROWTYPE attribute, must name a table, cursor or cursor-variable Oracle SQL Error: PLS-00310 with %ROWTYPE attribute, ‘string’ must name a table, cursor or cursor-variable Cause: The %ROWTYPE attribute must be applied to an identifier declared as a cursor, cursor variable, or database table. This error occurs when %ROWTYPE follows some identifier that has not been so… Read More »