• 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

ABS Function in Oracle SQL – PLSQL

November 5, 2012 by techhoneyadmin

The ABS function in Oracle SQL / PLSQL is used to get the absolute value of a number.

Syntax for the ABS function in Oracle SQL / PLSQL is:

SELECT ABS(number)
FROM table_name;

  • ‘number’ is the number that has to be converted to an absolute value

Example 1:

SELECT ABS(1.23)
FROM DUAL; 

Will return “1.23”


Example 2:

SELECT ABS(-1.23)
FROM DUAL; 

Will return “1.23”


Example 3:

SELECT ABS(-1.23 *1)
FROM DUAL; 

Will return “1.23”


Example 4:

SELECT ABS(1.23 *-1)
FROM DUAL; 

Will return “1.23”


Filed Under: function Tagged With: abs (absolute) function in oracle plsql, abs (absolute) function in oracle sql, ABSPLSQL, how to use abs (absolute) function in oracle database query, how to use abs (absolute) function in oracle plsql, how to use abs (absolute) function in oracle sql, syntax and example of abs (absolute) function in oracle database query, syntax and example of abs (absolute) function in oracle plsql, syntax and example of abs (absolute) function in oracle sql, using abs (absolute) function in oracle database query, using abs (absolute) function in oracle plsql, using abs (absolute) function in oracle sql

LOG Function in Oracle SQL – PLSQL

November 5, 2012 by techhoneyadmin

The LOG function in Oracle SQL / PLSQL is used to return the logarithmic value of a number to a base.

Syntax for the LOG function in Oracle SQL / PLSQL is:

SELECT LOG(n,m)
FROM table_name;

  • ‘n’ must be a positive number
  • ‘m’ must be a positive number except 0 and 1.

Example 1:

SELECT LOG(20,10)
FROM DUAL; 

Will return “0.768621786840241”


Example 2:

SELECT LOG(20,20)
FROM DUAL; 

Will return “1”


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

Oracle PL/SQL EXTRACT Function

November 5, 2012 by techhoneyadmin

Oracle EXTRACT FunctionOracle EXTRACT Function is used to extract a value from a date or interval value. SQL Extract Function can be used with String Dates and even with SQL SYSDATE.


Oracle EXTRACT Function Syntax

SELECT EXTRACT(
[YEAR or MONTH or DAY or HOUR or MINUTE or SECOND]
[TIMEZONE_HOUR or TIMEZONE_MINUTE]
[TIMEZONE_REGION or TIMEZONE_ABBR]
FROM [date_value or interval._value]
) FROM table_name;
  • Only Year, Month or Day can be extracted from a DATE value.
  • Only timezone_hour or timezone_minute can be extracted from a time stamp with time zone value.

Oracle EXTRACT Function – Extracting YEAR Example

We can use the SQL EXTRACT Function to extract year from a date passed as string.

For example, the EXTRACT Function query below extracts the YEAR from passed String.

SELECT EXTRACT(YEAR FROM DATE '2010-10-12') "PLSQL Extract"
FROM DUAL;

The above SQL EXTRACT query returns “2010”

Note: We have aliased EXTRACT(YEAR FROM DATE ‘2010-10-12’) as PLSQL Extract


Oracle EXTRACT Function – Extracting MONTH Example

We can use the SQL EXTRACT Function to extract MONTH from date passeda s String,

For example, the SQL EXTRACT Function query below extracts the MONTH from passed String.

SELECT EXTRACT(MONTH FROM DATE '2010-10-12') "PLSQL Extract"
FROM DUAL;

The above Oracle EXTRACT query returns “10” as the month.

Note: We have aliased EXTRACT(MONTH FROM DATE ‘2010-10-12’) as PLSQL Extract


Oracle EXTRACT Function – Extracting DAY Example

Apart from extracting YEAR and MONTH, SQL EXTRACT Function can be used to extract DAY.

For example, the SQL EXTRACT Function query below extracts DAY from the parameter passed.

SELECT EXTRACT(DAY FROM DATE '2010-10-12') "PLSQL Extract"
FROM DUAL;

The above Oracle EXTRACT query returns “12” as the DAY of the MONTH.

Note: We have aliased EXTRACT(DAY FROM DATE ‘2010-10-12’) as PLSQL Extract.


Oracle EXTRACT Function – Using SYSDATE Example

SQL SYSDATE can also be used with the SQL EXTRACT Function to get YEAR, MONTH or DAY.

For example, the Oracle EXTRACT query below returns the YEAR using SYSDATE as parameter.

SELECT EXTRACT(YEAR FROM SYSDATE)
FROM DUAL;

May return “2012” depending upon SYSDATE.


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

Oracle NUMTOYMINTERVAL Function

November 5, 2012 by techhoneyadmin

SQL NUMTOYMINTERVAL FunctionOracle NUMTOYMINTERVAL Function is used to convert any number to an Interval Year to Month Literal.


Oracle NUMTOYMINTERVAL Function Syntax

SELECT NUMTOYMINTERVAL(number,expression)
FROM table_name;
  • ‘number’ is the number to be converted to interval
  • ‘expression’ is the unit and can have the following values: ‘YEAR’ or ‘MONTH’

Oracle NUMTOYMINTERVAL – Converting to MONTH Example

Oracle NUMTOYMINTERVAL is used in SQL SELECT Statement to convert any number to an Interval Year to Month Literal.

For example, the below SQL Query converts 300000000 to MONTH.

SELECT NUMTOYMINTERVAL(300000000, 'MONTH')
FROM DUAL;

May return “+25000000-00”


Oracle NUMTOYMINTERVAL – Converting to YEAR Example

NUMTOYMINTERVAL can convert any number to an Interval Year type also.

For example, the below SQl Query converts 300000 to YEAR.

SELECT NUMTOYMINTERVAL(300000, 'YEAR')
FROM DUAL;

May return “+300000-00”


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

NUMTODSINTERVAL Function in Oracle SQL – PLSQL

November 5, 2012 by techhoneyadmin

The NUMTODSINTERVAL function in Oracle SQL / PLSQL is used to convert any number to an Interval Day to Second Literal.

Syntax for the NUMTODSINTERVAL function in Oracle SQL / PLSQL is:

SELECT NUMTODSINTERVAL(number,expression)
FROM table_name;

  • ‘number’ is the number to be converted to interval
  • ‘expression’ is the unit and can have the following values: ‘DAY’, ‘HOUR’, ‘MINUTE’ OR ‘SECOND’

Example 1:

SELECT NUMTODSINTERVAL(250, 'DAY')
FROM DUAL; 

May return “+250 00:00:00.000000”


Example 2:

SELECT NUMTODSINTERVAL(2500, 'HOUR')
FROM DUAL; 

May return “+104 04:00:00.000000”


Example 3:

SELECT NUMTODSINTERVAL(25000, 'MINUTE')
FROM DUAL; 

May return “+17 08:40:00.000000”


Example 4:

SELECT NUMTODSINTERVAL(250000, 'SECOND')
FROM DUAL; 

May return “+02 21:26:40.000000”


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

  • « Go to Previous Page
  • Page 1
  • Interim pages omitted …
  • Page 39
  • Page 40
  • Page 41
  • Page 42
  • Page 43
  • Interim pages omitted …
  • Page 76
  • Go to Next Page »

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