DENSE_RANK is an Analytical function as well as Aggregate function in Oracle. It returns the rank of the row within the group and it is dense. Syntax (Aggregate function): dense_rank(expression1,expression2,…) WITHIN GROUP (ORDER BY expression1,expression2…)...
Nth highest salary 1) Find highest salary/salaries using DENSE_RANK() function select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk = 1; EMPNO SALARY RK ———- ———- ———- 10 12000 1 2) Find 2nd highest salary/salaries...
Translate function Oracle Posted on September 30, 2012 by sqlandplsql TRANSLATE function replaces a string with another set of characters.TRANSLATE replace characters in sequential order. Syntax TRANSLATE(string, from_string, to_string) Example 1:- SQL>...
Replace function Oracle Posted on September 24, 2012 by sqlandplsql Replace function replaces the string with another string if it matches. It is a character function in Oracle. Replace function is case-sensitive. Syntax:- REPLACE(‘input string’,’find_string’,’replace_string’);...
REPLACE and TRANSLATE are character function in Oracle. Replace function Translate function Both function looks similar but there are some subtle difference. We can say Translate is an extended version of Replace.
CREATE [OR REPLACE] FUNCTION [SCHEMA..] function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section variable declarations; constant declarations; ] BEGIN [executable_section PL/SQL execute/subprogram body ] [EXCEPTION]...
PL/SQL functions block create using CREATE FUNCTION statement. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value. When you create a function or procedure, you have to...
What is a database link? Database link is a named path through which a remote database can be accessed.
What is ROWID? ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWI
What's Deadlock Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first...
