• Skip to primary navigation
  • Skip to main content

Tech Honey

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

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql

function

ROWIDTOCHAR Function in Oracle SQL – PLSQL

November 22, 2012 by techhoneyadmin

The ROWIDTOCHAR function in Oracle SQL / PLSQL is used to cionvert the ROWID to VARCHAR2 datatype, the result is always 18 characters long.

Syntax for using the ROWIDTOCHAR function Oracle SQL / PLSQL is;
SELECT ROWIDTOCHAR (ROWID)
FROM table_name;

Example:
Using ROWIDTOCHAR 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 ROWIDTOCHAR(ROWID)
FROM employee;

We may get the following result:

ROWIDTOCHAR(ROWID)
AAAPD2AABAAAS2aAAA
AAAPD2AABAAAS2aAAB
AAAPD2AABAAAS2aAAC
AAAPD2AABAAAS2aAAD
AAAPD2AABAAAS2aAAE
AAAPD2AABAAAS2aAAF
AAAPD2AABAAAS2aAAG
AAAPD2AABAAAS2aAAH

Here we can see that we have converted all the “rowids” into varchar2 data type and all the records are 18 characters long.


Filed Under: function Tagged With: how to use ROWIDTOCHAR function in oracle database query, how to use ROWIDTOCHAR function in oracle plsql, how to use ROWIDTOCHAR function in oracle sql, ROWIDTOCHAR function in oracle plsql, ROWIDTOCHAR function in oracle sql, ROWIDTOCHARPLSQL, syntax and example of ROWIDTOCHAR function in oracle database query, syntax and example of ROWIDTOCHAR function in oracle plsql, syntax and example of ROWIDTOCHAR function in oracle sql, using ROWIDTOCHAR function in oracle database query, using ROWIDTOCHAR function in oracle plsql, using ROWIDTOCHAR function in oracle sql

NULLS LAST Function in Oracle SQL – PLSQL

November 21, 2012 by techhoneyadmin

The NULLS LAST Function in Oracle SQL / PLSQL is used to place the NULL records at the end in the fetched records.

Syntax for using the NULLS LAST function in Oracle SQL / PLSQL is ;
SELECT column(s)
ROW_NUMBER() OVER (ORDER BY column NULLS LAST)
FROM table_name;

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
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales

Now, suppose we want to see the employee_name and commission and also want that the employee records having no commission should be listed LAST then we can achieve the same using NULLS LAST function as:

SELECT employee_name
       ,commission
       ,ROW_NUMBER () OVER (ORDER BY commission NULLS LAST) SNO
FROM employee;

We will get the following result:

Employee_Name Commission SNO
Emp G 10 1
Emp A 10 2
Emp E 10 3
Emp F 10 4
Emp B 20 5
Emp C 20 6
Emp H   7
Emp D   8

Here we can see that we have successfully fetched employees having no commission LAST in the list.


Filed Under: function Tagged With: how to use NULLS LAST function in oracle database query, how to use NULLS LAST function in oracle plsql, how to use NULLS LAST function in oracle sql, NULLS LAST function in oracle plsql, NULLS LAST function in oracle sql, NULLSLASTPLSQL, syntax and example of NULLS LAST function in oracle database query, syntax and example of NULLS LAST function in oracle plsql, syntax and example of NULLS LAST function in oracle sql, using NULLS LAST function in oracle database query, using NULLS LAST function in oracle plsql, using NULLS LAST function in oracle sql

NULLS FIRST Function in Oracle SQL – PLSQL

November 21, 2012 by techhoneyadmin

The NULLS FIRST Function in Oracle SQL / PLSQL is used to place the NULL records at the beginning in the fetched records.

Syntax for using the NULLS FIRST function in Oracle SQL / PLSQL is ;
SELECT column(s)
ROW_NUMBER() OVER (ORDER BY column NULLS FIRST)
FROM table_name;

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
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales

Now, suppose we want to see the employee_name and commission and also want that the employees having no commission should be listed first then we can achieve the same using NULLS FIRST function as:

SELECT employee_name
       ,commission
       ,ROW_NUMBER () OVER (ORDER BY commission NULLS FIRST) SNO
FROM employee;

We will get the following result:

Employee_Name Commission SNO
Emp D   1
Emp H   2
Emp G 10 3
Emp E 10 4
Emp F 10 5
Emp A 10 6
Emp C 20 7
Emp B 20 8

Here we can see that we have successfully fetched employees having no commission first in the list.


Filed Under: function Tagged With: how to use NULLS FIRST function in oracle database query, how to use NULLS FIRST function in oracle plsql, how to use NULLS FIRST function in oracle sql, NULLS FIRST function in oracle plsql, NULLS FIRST function in oracle sql, NULLSFIRSTPLSQL, syntax and example of NULLS FIRST function in oracle database query, syntax and example of NULLS FIRST function in oracle plsql, syntax and example of NULLS FIRST function in oracle sql, using NULLS FIRST function in oracle database query, using NULLS FIRST function in oracle plsql, using NULLS FIRST function in oracle sql

LAST Function in Oracle SQL – PLSQL

November 21, 2012 by techhoneyadmin

The LAST Function in Oracle SQL / PLSQL is used to get the last value in an ordered set of records.

The LAST function can be used with the following functions.

  1. MIN()
  2. MAX()
  3. COUNT()
  4. SUM()
  5. AVG()
  6. STDDEV() and
  7. VARIANCE()

Syntax for using the LAST function in Oracle SQL / PLSQL is ;
SELECT
AGGREGATE_FUNCTION() KEEP (RANK_FUNCTION LAST ORDER BY AGGREGATE_FUNCTION(COLUMN))
FROM table_name
GROUP BY column(s);

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
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

Now, suppose we want to get the name of the department where the total salary being given is higher than total of salary in any other department, then we can achieve the same using LAST function as:

SELECT MAX(department) KEEP (DENSE_RANK LAST ORDER BY SUM(salary)) Max_Sal_Department
FROM employee
GROUP BY department;

We will get the following result:

MAX_SAL_DEPARTMENT
Sales

Here we can see that we have successfully fetched the name of the department in which the total salary is greater than total salary of any other department.


Filed Under: function Tagged With: how to use LAST function in oracle database query, how to use LAST function in oracle plsql, how to use LAST function in oracle sql, LAST function in oracle plsql, LAST function in oracle sql, LASTPLSQL, syntax and example of LAST function in oracle database query, syntax and example of LAST function in oracle plsql, syntax and example of LAST function in oracle sql, using LAST function in oracle database query, using LAST function in oracle plsql, using LAST function in oracle sql

FIRST Function in Oracle SQL – PLSQL

November 21, 2012 by techhoneyadmin

The FIRST Function in Oracle SQL / PLSQL is used to get the FIRST value in an ordered set of records.
The FIRST function can be used with the following functions.

  1. MIN()
  2. MAX()
  3. COUNT()
  4. SUM()
  5. AVG()
  6. STDDEV() and
  7. VARIANCE()

Syntax for using the FIRST function in Oracle SQL / PLSQL is ;
SELECT
AGGREGATE_FUNCTION() KEEP (RANK_FUNCTION FIRST ORDER BY AGGREGATE_FUNCTION(column))
FROM table
GROUP BY column(s);

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
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

Now, suppose we want to get the name of the department where the total salary being given is lesser than the total of salary in any other department, then we can achieve the same using FIRST function as:

SELECT MAX(department) KEEP (DENSE_RANK FIRST ORDER BY SUM(salary)) Min_Sal_Department
FROM employee
GROUP BY department;

We will get the following result:

MIN_SAL_DEPARTMENT
Support

Here we can see that we have successfully fetched the name of the department in which the total salary is lesser than total salary of any other department.


Filed Under: function Tagged With: FIRST function in oracle plsql, FIRST function in oracle sql, FIRSTPLSQL, how to use FIRST function in oracle database query, how to use FIRST function in oracle plsql, how to use FIRST function in oracle sql, syntax and example of FIRST function in oracle database query, syntax and example of FIRST function in oracle plsql, syntax and example of FIRST function in oracle sql, using FIRST function in oracle database query, using FIRST function in oracle plsql, using FIRST function in oracle sql

  • « Go to Previous Page
  • Page 1
  • Interim pages omitted …
  • Page 9
  • Page 10
  • Page 11
  • Page 12
  • Page 13
  • Interim pages omitted …
  • Page 27
  • Go to Next Page »

Copyright © 2025 · Parallax Pro on Genesis Framework · WordPress · Log in