Category Archives: PL/SQL

Learn PLSQL Tutorial

PL/SQL Interview Questions and Answers

PL SQL Interview Questions and Answers 1. What is an index? Index is an object in a database that provides direct access and quick search without scanning the entire table. The index is used for the most used columns for queries. 2. What are the types of indexes? B-tree index (default) Bitmap index Partition Function-based Domain index Compound… Read More »

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; Output:… 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; Output: 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’); Output: 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; Output: 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 »