Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

function

CHARTOROWID Function in Oracle SQL – PLSQL

by

The CHARTOROWID function in Oracle SQL / PLSQL is used to convert the CHAR, VARCHAR2, NCHAR or NVARCHAR2 data-type into ROWID type.

Syntax for using the CHARTOROWID function Oracle SQL / PLSQL is:

SELECT CHAROROWID (value)
FROM table_name;

    • value is the value to be converted to ROWID
    • The format of ROWID is BBBBBBB.RRRR.FFFFF

Here,
BBBBBBB is the block in the database file.
RRRR is the row in the block.
FFFFF is the database file.

Example:

Using CHARTOROWID function

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

If we write our query as:

SELECT *
FROM employee
WHERE ROWID = CHARTOROWID ('AAAPD2AABAAAS2aAAA');

Will return a unique row from employee table.

VAR_SAMP Function in Oracle SQL – PLSQL

by

The VAR_SAMP Function in Oracle SQL / PLSQL is used to calculate the sample variance of a set of numbers.

Syntax for the using the VAR_SAMP Function in Oracle SQL / PLSQL is:

SELECT VAR_SAMP(expression_1)
FROM table_name;

  • expression_1 is numeric expression
  • The VAR_SAMP function in Oracle SQL / PLSQL will eliminate all the NULL values before calculation.

Examples:

Using VAR_SAMP Function in Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales  
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support  
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Support  

If we write our VAR_SAMP Function query as:

SELECT VAR_SAMP(salary)
FROM employee;

We will get ‘76285714.2857143’ as output.

VAR_POP Function in Oracle SQL – PLSQL

by

The VAR_POP Function in Oracle SQL / PLSQL is used to calculate the population variance of a set of numbers.

Syntax for the using the VAR_POP Function in Oracle SQL / PLSQL is:

SELECT VAR_POP(expression_1)
FROM table_name;

  • expression_1 is a numeric expression
  • The VAR_POP function in Oracle SQL / PLSQL will eliminate all NULL values before calculation.

Examples:

Using VAR_POP Function in Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales  
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support  
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Support  

If we write our VAR_POP Function query as:

SELECT VAR_POP(salary)
FROM employee;

We will get ‘66750000’ as output

REMAINDER Function in Oracle SQL – PLSQL

by

The REMAINDER Function in Oracle SQL / PLSQL is used to get the remainder when m is divided by n.

Syntax for the using the REMAINDER Function in Oracle SQL / PLSQL is:

SELECT REMAINDER(m,n)
FROM table_name;

  • The remainder is calculated using the formula:

    • m-(n*A), here A is an integer nearest to m / n.

  • The REMAINDER function in Oracle SQL / PLSQL uses the round function, where as the MOD function in Oracle SQL / PLSQL uses the floor function in it’s calculation.

Examples:

Using REMAINDER Function in Oracle SQL / PLSQL SELECT statement:

SELECT REMAINDER(10,3)
FROM dual;

Will return ‘1’

SELECT REMAINDER(11,3)
FROM dual;

Will return ‘-1’

SELECT REMAINDER(15,6)
FROM dual;

Will return ‘3’

SELECT REMAINDER(15.3,6.5)
FROM dual;

Will return ‘2.3’

COVAR_SAMP Function in Oracle SQL – PLSQL

by

The COVAR_SAMP Function in Oracle SQL / PLSQL is used to calculate the sample covariance of a set of numbers in pairs.

Syntax for the using the COVAR_SAMP Function in Oracle SQL / PLSQL is:

SELECT COVAR_SAMP(expression_1, expression_2)
FROM table_name;

  • expression_1 and expression_2 are numeric expressions
  • The COVAR_SAMP function in Oracle SQL / PLSQL will eliminate all the pairs where expression_1 and expression_2 are having NULL values.

Example:

Using COVAR_SAMP Function in Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales  
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support  
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Support  

If we write our COVAR_SAMP Function query as:

SELECT COVAR_SAMP(salary, commission)
FROM employee;

We will get ‘-7500’ as output