ORACLE DATABASE - Online Tech Support
  Home
 

This tutorial page is based on examples to be easier to follow. The Oracle TO_CHAR function allows converting others’ data types value to character type and function To_Char always returns VARCHAR2 type. You can convert to VARCHAR2 for example DATE, NUMBER, NCHAR, NVARCHAR2, CLOB, or NCLOB data types. The TO_CHAR syntax is following:

TO_CHAR(<datatype>[,<data_mask>])

Take a look at the examples below to see how works Oracle TO_CHAR. For example the first query is number “01234” without To_Char function and the nature of this type is getting trimmed by Oracle database so the SQL output returns “1234“.

SELECT 01234 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

The reason why Oracle database removed the first 0 (zero) is to keep the database tidy and that way the number looks less confusing for users. Having a zero in front of 1234 doesn’t make much sense either so it gets cleaned away.

Now to keep the number as with the original look, you should use Oracle To_Char function and store the value into Oracle database table as VARCHAR2 type because numbers like phone numbers or credit card numbers starting with 0 (zero) wouldn’t be valid after removing the first zero. We can’t treat all numbers as “numbers” since there are numbers that should keep their look to remain correct and valid.

The second example demonstrates how will stay a number treated as text and to keep its look. This is a demonstrational query so we wrote the number as text and To_Char function is not needed in this case but you can use this example with other datatype variables in place of ‘01234‘. Take a look at the following query and note that Oracle database left the first zero as is and your phone numbers wouldn’t be ruined using this VARCHAR2 type.

SELECT TO_CHAR('01234') 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also Oracle To_Char can be used to give numbers a certain look so they look better and more clear on a report or a bill. To format a number you would need to use a data mask and for numbers there are limited amount of key-letters. To see number formatting element take a look at the table below or this link will send you directly to Oracle webpage for possible mask elements. The mask key-letters should be used only with Oracle To_Char as on the following example. On this example we are filling in missing zeros after the coma point to have always two places after the decimal point.

SELECT TO_CHAR(111.2,'990D00') AS formatted_number
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Changing value from number to character is not as popular as turning it from a date type to text and the following part of this tutorial will continue with date values. To change a date look you would need to use a mask again but this time with different mask elements. For example the following Oracle To_Char example has a mask as year(YYYY)-month(MM)-day(DD) hours in 24 digits(HH24):minutes(MI):seconds(SS).

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS date_to_char
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also here you can find a list of possible Oracle masks elements for a date type. Use those mask elements only with To_Char function.


See Also:
Oracle Select Oracle SUBSTR Oracle INSTR Oracle Replace Oracle Length Home

 

Number Format Elements Example Description
, (comma) 9,999 Returns a comma in the specified position. You can specify multiple commas in a number format model.Restrictions:

  • A comma element cannot begin a number format model.
  • A comma cannot appear to the right of a decimal character or period in a number format model.
. (period) 99.99 Returns a decimal point, which is a period (.) in the specified position.Restriction: You can specify only one period in a number format model.
$ $9999 Returns value with a leading dollar sign.
0 09999990 Returns leading zeros.Returns trailing zeros.
9 9999 Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B B9999 Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
C C999 Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).
D 99D99 Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).Restriction: You can specify only one decimal character in a number format model.
EEEE 9.9EEEE Returns a value using in scientific notation.
G 9G999 Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
L L999 Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter).
MI 9999MI Returns negative value with a trailing minus sign (-).Returns positive value with a trailing blank.Restriction: The MI format element can appear only in the last position of a number format model.
PR 9999PR Returns negative value in <angle brackets>.Returns positive value with a leading and trailing blank.Restriction: The PR format element can appear only in the last position of a number format model.
RNrn RNrn Returns a value as Roman numerals in uppercase.Returns a value as Roman numerals in lowercase.Value can be an integer between 1 and 3999.
S S99999999S Returns negative value with a leading minus sign (-).Returns positive value with a leading plus sign (+).Returns negative value with a trailing minus sign (-).Returns positive value with a trailing plus sign (+).Restriction: The S format element can appear only in the first or last position of a number format model.
TM TM The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive.The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation.Restrictions:

  • You cannot precede this element with any other element.
  • You can follow this element only with one 9 or one E (or e), but not with any combination of these. The following statement returns an error:SELECT TO_CHAR(1234, 'TM9e') FROM DUAL;
U U9999 Returns in the specified position the Euro (or other) dual currency symbol, determined by the current value of the NLS_DUAL_CURRENCY parameter.
V 999V99 Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9’s after the V.
X XXXXxxxx Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer.Restrictions:

  • This element accepts only positive values or 0. Negative values return an error.
  • You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has one leading blank.
Datetime Format Element TO_* Datetime functions? Description
-
/
,
.
;
:
"text"
Yes Punctuation and quoted text is reproduced in the result.
AD
A.D.
Yes AD indicator with or without periods.
AM
A.M.
Yes Meridian indicator with or without periods.
BC
B.C.
Yes BC indicator with or without periods.
CC
SCC
Century.

  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

D
Yes Day of week (1-7). This element depends on the NLS territory of the session.
DAY
Yes Name of day.
DD
Yes Day of month (1-31).
DDD
Yes Day of year (1-366).
DL
Yes Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy‘.Restriction: You can specify this format only with the TS element, separated by white space.
DS
Yes Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.Restriction: You can specify this format only with the TS element, separated by white space.
DY
Yes Abbreviated name of day.
E
Yes Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE
Yes Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]
Yes Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type’s default precision. Valid in timestamp and interval formats, but not in DATE formats.Examples: 'HH:MI:SS.FF'SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM
Yes Returns a value with no leading or trailing blanks.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX
Yes Requires exact matching between the character data and the format model.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH
HH12
Yes Hour of day (1-12).
HH24
Yes Hour of day (0-23).
IW
Week of year (1-52 or 1-53) based on the ISO standard.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
4-digit year based on the ISO standard.
J
Yes Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI
Yes Minute (0-59).
MM
Yes Month (01-12; January = 01).
MON
Yes Abbreviated name of month.
MONTH
Yes Name of month.
PM
P.M.
Yes Meridian indicator with or without periods.
Q
Quarter of year (1, 2, 3, 4; January – March = 1).
RM
Yes Roman numeral month (I-XII; January = I).
RR
Yes Lets you store 20th century dates in the 21st century using only two digits.See Also: “The RR Datetime Format Element”
RRRR
Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS
Yes Second (0-59).
SSSSS
Yes Seconds past midnight (0-86399).
TS
Yes Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD 
Yes Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH
Yes Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZM
Yes Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZR
Yes Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.Example: US/Pacific
WW
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X
Yes Local radix character.Example: 'HH:MI:SSXFF'.
Y,YYY
Yes Year with comma in this position.
YEAR
SYEAR
Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY
SYYYY
Yes 4-digit year; S prefixes BC dates with a minus sign.
YYY
YY
Y
Yes Last 3, 2, or 1 digit(s) of year.
 

This tutorial is based on examples to be easier to follow. The Oracle NVL allows replacing an empty (null) value with the second argument. Function Oracle NVL syntax is:

NVL (<value_1>, <value_2>)

Oracle NVL works in way that if the first column value is null then replace it with a second value as on the following example we are going to replace the null value with letter “Y“. In that example we will use an inner query to set the Oracle Dual column to NULL and that does NOT mean that you would need to use Oracle NVL function always with an inner query. This example below is written that way so it would to make the Oracle SQL example easier to follow and you wouldn’t have to create new Oracle tables to try this example. The Oracle Dual table is in present of all Oracle database versions and you can just do copy-paste and run it in your Oracle database.

SELECT NVL(my_value,'Y') 
  FROM (SELECT null AS my_value 
          FROM dual );

online tech support computer help computer technician computer problems computer oracle database oracle nvl oracle nvl in oracle sql nvl oracle sql nvl in oracle sql database programming database oracle retirement planning retirement oracle COALESCE oracle COALESCE in oracle NVL2 oracle NVL2 in oracle

The second example will show you what does return Oracle NVL if the first argument is not NULL. You will find out it will ignore the second argument and it returns only the first column value. Take a look at the example below the first argument is named “my_value” and it has assigned the “X” character. The same character will be returned by the Oracle NVL function.

SELECT NVL(my_value,'Y') 
  FROM (SELECT 'X' AS my_value 
          FROM dual );

online tech support computer help computer technician computer problems computer oracle database oracle nvl oracle nvl in oracle sql nvl oracle sql nvl in oracle sql database programming database oracle retirement planning retirement oracle COALESCE oracle COALESCE in oracle NVL2 oracle NVL2 in oracle

Sometimes you would need to use more than 2 arguments to compare with themselves and in that case we do recommend using function Oracle COALESCE. The Oracle COALESCE syntax is:

COALESCE ( <value_1>, <value_2>, <value_3>, <value_4>, ... )

The Oracle COALESCE function returns first not-empty (not null) value as on the following example:

SELECT COALESCE(my_value1, my_value2, my_value3,'Y') 
  FROM (SELECT NULL AS my_value1, NULL AS  my_value2, 3 AS my_value3 
          FROM dual );

online tech support computer help computer technician computer problems computer oracle database oracle nvl oracle nvl in oracle sql nvl oracle sql nvl in oracle sql database programming database oracle retirement planning retirement oracle COALESCE oracle COALESCE in oracle NVL2 oracle NVL2 in oracle

The example above has set only on the third column not null value and its name is “my_value3″. Since two first columns are left empty the Oracle Coalesce function ignores them and it returns the value of column my_value3 which is number “3“.

Another NVL functions is Oracle NVL2 and it lets you to return two following values depending on the first argument. If the first argument is NULL then Oracle NVL2 returns the 3rd value. Otherwise the function returns the 2nd value. The Oracle NVL2 syntax is:

NVL2 (<value_to_compare>, <returning_value_1>, <returning_value_2>)

Online tech support has prepared two following examples with Oracle NVL2. The 1st one has the 1st argument NULL and it will return the value from column my_value3.

SELECT NVL2(my_value1,my_value2,my_value3)
  FROM (SELECT NULL AS my_value1,1 AS my_value2, 2 AS my_value3
          FROM dual) ;

online tech support computer help computer technician computer problems computer oracle database oracle nvl oracle nvl in oracle sql nvl oracle sql nvl in oracle sql database programming database oracle retirement planning retirement oracle COALESCE oracle COALESCE in oracle NVL2 oracle NVL2 in oracle

The second example with Oracle NVL2 has the first argument set to not null as number “0” (zero). This time the Oracle NVL2 works “reverse” way and returns the value from column my_value2. The my_value2 column has number “1” value.

SELECT NVL2(my_value1,my_value2,my_value3)
  FROM (SELECT 0 AS my_value1,1 AS my_value2, 2 AS my_value3
          FROM dual) ;

online tech support computer help computer technician computer problems computer oracle database oracle nvl oracle nvl in oracle sql nvl oracle sql nvl in oracle sql database programming database oracle retirement planning retirement oracle COALESCE oracle COALESCE in oracle NVL2 oracle NVL2 in oracle

The difference between Oracle NVL and NVL2 is that NVL2 never returns the first declared value but only uses it to do comparison with NULL. Oracle NVL returns first or second value depending on which one is not null.


See Also:
Home Oracle Select Oracle NVL2 Oracle Decode

 

This tutorial page is based on examples to be easier to follow. You can find Oracle Date Format types on this link. The TO_DATE syntax is following:

TO_DATE('date_value',['date_mask'],['NLS_DATE_FORMAT']);

The first example to use date format in Oracle would be as the following example:

SELECT TO_DATE('20120921','YYYYMMDD')
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle date format date format oracle to_date oracle to_date in oracle sql date format oracle sql date format in oracle sql to_date oracle sql to_date in oracle sql retirement planning retirement

On the example above the Oracle date format has set a date mask after string ‘20120921’. The date mask is a text that is written or formatted in the same order as is the date value. For example the example has year as “YYYY” + month as “MM” + day number as “DD“. And the Oracle date value is September 21st 2012.

To find out the last date of some year you could do it as the following example. In that example we are looking for the last day number for year “2000“.

SELECT TO_DATE('20010101','YYYYMMDD') -1
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle date format date format oracle to_date oracle to_date in oracle sql date format oracle sql date format in oracle sql to_date oracle sql to_date in oracle sql retirement planning retirement

The Oracle SQL has date the January 1st 2001 and we are subtracting one day off from it. In addition, you can use the same way to find out the last day of the February month. The reason to do it is because the February month has a different amount of days in every forth year. The example below with Oracle date format will return the last day of February 1998 and it is “28-FEB-98“.

SELECT TO_DATE('19980301','YYYYMMDD') -1
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle date format date format oracle to_date oracle to_date in oracle sql date format oracle sql date format in oracle sql to_date oracle sql to_date in oracle sql retirement planning retirement

To achieve the last day of February 1998 we took the next month’s first day (March 1st 1998) and subtracted 1 day.

For additional oracle date format types please see the first link above and find out more date format oracle types.


See Also:
Oracle Date Difference Oracle TO_DATE Oracle Interval

 

This page is based on examples to be easier to follow. The Oracle DECODE allows you to compare the input value with the following ones and every comparable value should have a returning value. Among of all values even empty (null) values are included to the list. Function Decode in oracle can be used in themselves as many times you want, but we would not to recommend to do so and instead of you could use Oracle CASE function. The main reason for that is that your code will be more readable, using Oracle Decode function in another Decode will be difficult to follow and Oracle Case function is more clear in this case.

The syntax is following:

SELECT DECODE(<input_value>,
              <value_to_compare_1>,<result_1>,
              <value_to_compare_1>,<result_1>,..,<default_result>)
 FROM <table_name> ;

The first example is showing the DUAL table content and this one-row-table’s value is “X“. See at the output below.

SELECT * FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

P.S. The DUAL table is a one-row table that is present in all Oracle databases.

Using the table above Online Tech Support has prepared the next examples below with Oracle DECODE or CASE.

SELECT DECODE(DUMMY,NULL,'Empty','X','Dual Default','Unknown')
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The query returns the “Dual Default” string because the Dummy column has value “X” and the first “NULL” condition did not match. The second value “X” did match and the Select query returned the 2nd result “Dual Default“. Oracle DECODE function takes first matching value and ignores rest, so default result “Unknown” didn’t appear either.

The 3rd example has two “X” value with different outputs. The DECODE function returns the first available value now.

SELECT DECODE(DUMMY,NULL,'Empty',
                'X','Dual Default',
                'X','Dual Default Again','Unknown')
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

As you see the same result as in the 2nd example. The reason is that Oracle DECODE function takes first matching value and ignores rest, even if the second value matching too.

When any of the values don’t match and the default value is declared then the DECODE returns the default value as on the following example.

SELECT DECODE(DUMMY,NULL,'Empty',
                'Y','Dual Default',
                'Z','Dual Default Again','Unknown')
 FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

As we did mention above we do not recommend to use a DECODE in another Oracle DECODE function and this is mainly about not being clear enough and it is difficult to read. As the following two examples will demonstrate the first with DECODE and the same result achieved using the Oracle CASE function.

SELECT DECODE(DUMMY,NULL,'Empty',
              DECODE(DUMMY,'X','Dual Default',
                       'X','Dual Default Again','Unknown')
              )
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

SELECT CASE DUMMY
         when NULL then 'Empty'
         when 'Y' then 'Dual Default'
         when 'X' then'Dual Default Again'
         else 'Unknown' END
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

We are thinking it’s much easier to read the SQL query when combined conditions are written within Oracle Case then with using many Oracle Decode functions.


See Also the following:
Home Oracle CASE

 

Functions Oracle INSTR ,INSTRB , INSTRC , INSTR2 , INSTR4 are for searching your search text or a character in a string and it returns the position number in the string. When searching text didn’t find anything the functions are returning zero (0). The Oracle INSTR syntax is following:

INSTR('<your_string>','<searchible_string>'[,<starting_point>,<occurrence_number>]);

The first example is using string “AAAAAAAALMBBBBBBBBBAAAAAA” and we are looking for text “LM“. The “LM” string starts on the 9th position in the text and the same value is returned by the example below.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','LM')
  FROM DUAL;

online tech support online computer help computer technician computer problems instr oracle instr in oracle instr oracle sql oracle sql instr in oracle sql online universities

There is possibility to look for your string starting from n-position and the next example will try to look for the same “LM” string from the 10th position. The output return 0 because we do have only one “LM” string starting from the 9th position and the function could not find more matching strings.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','LM',10)
  FROM DUAL;

online tech support online computer help computer technician computer problems instr oracle instr in oracle instr oracle sql oracle sql instr in oracle sql online universities

The third example is a bit different from the examples above because we are trying to find only one letter “B“. The INSTR function returns 11 because this is the first match and the following “B“-s will be ignored.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','B')
  FROM DUAL;

online tech support online computer help computer technician computer problems instr oracle instr in oracle instr oracle sql oracle sql instr in oracle sql online universities

You can look for the “B” character in a reverse way or from left to right. To define the search from right to left you need to set starting position a negative number and on the example below you can find starting position as -1. The Oracle INSTR function returned the first “B” character from right to left on position 19th or we can call it as the last “B” in the string.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','B',-1)
  FROM DUAL;

online tech support online computer help computer technician computer problems instr oracle instr in oracle instr oracle sql oracle sql instr in oracle sql online universities

The other Oracle INSTR functions are working with the same principles only the output is different.


See Also:
Oracle Select Oracle Substr Oracle Length Oracle TO_CHAR Home

 

Oracle ANALYTIC functions can be use to do grouping on every row and without removing the duplications from the query output. We do recommend to use Oracle analytic functions on web pages or on reports and doing it your page does have to do download all lines to the page to do sum. You can just add additional column with Oracle analytic functions and use values from there.

The first examples will be used as a source query and to amend the data we are using the MOD function.

 SELECT mod (rownum,3) AS amount
   FROM dual
CONNECT BY rownum < 21;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online

The following query shows how to use one of the Oracle analytic functions, named COUNT and as you see all lines are counted the result is displayed without grouping data.

SELECT amount, COUNT(*) over () AS total_count
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
       CONNECT BY rownum < 21 ) ;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online

Oracle analytic functions can make the query simpler since we don’t need to worry about Oracle group by clause. The online tech support example below shows how to receive minimum, maximum and sum using Oracle analytic functions.

SELECT amount,
       MIN(amount) over () AS min_amount,
       MAX(amount) over () AS max_amount,
       SUM(amount) over () AS sum_amount
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
       CONNECT BY rownum < 21 ) ;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online

So far the examples have applied Oracle analytic functions over all lines, but there are more possibilities to work with groups without grouping the data. On the following online tech support query we taking a sum of values for every value type e.g. Zeros, ones and twos are making three different groups and we are applying the Oracle SUM function on every group separately.

SELECT amount,
       SUM(amount) over (partition by amount) AS sum_grouped_amounts
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
CONNECT BY rownum < 21 ) ;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online


See Also:
Home Oracle Select

 

This Online Tech Support page is based on examples to be easier to follow. The Oracle Sequence allowing to get unique integers through an independent object. The Oracle Sequence keeps track of the last number and generates a new one when to call out the NEXTVAL function. The Sequences are returning unique integers per each Oracle Sequence. They are mainly used to fill unique ID columns in Oracle tables or they can be used in applications where unique numbers are needed. To fill ID columns in tables the sequence will be much faster and safer than doing queries over the table rows and looking for the greatest ID value. We do recommend not to use ever the last query option.

The most basic syntax is :

CREATE SEQUENCE <sequence_name>
 START WITH     <number_position>
 INCREMENT BY   <number_amount>;

The first script will create a new sequence named MY_SEQUENCE into your database:

CREATE SEQUENCE my_sequence
 START WITH     1
 INCREMENT BY   1;
/

online tech support online computer help computer technician computer problems computer oracle sequence oracle sequence in oracle sql sequence oracle sql sequence in oracle sql oracle database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The Oracle Sequence syntax above has keyword “START WITH”  and the value will be a starting point to increase the numbers. The number can be set to a higher or lower number depending on what should be the next number. The second keyword named “INCREMENT BY” will increment every next number by the amount set by the parameter. The “INCREMENT BY” value is by default “1“. When you’ll leave the “INCREMENT BY” value to “1” then the Sequence will return the following values: 2,3,4,5,.. and when it has to been set the to “2” then the Sequence will return 2,4,6,8,10,..

The first example shows how to get the next Sequence value and you can do it using a simple SQL query. The returned number “2” came from the MY_SEQUENCE object. If you’ll run the query one more time the next value will be “3” and so on.

SELECT my_sequence.nextval
 FROM dual;

online tech support online computer help computer technician computer problems computer oracle sequence oracle sequence in oracle sql sequence oracle sql sequence in oracle sql oracle database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To show how to use the Oracle Sequence in the Oracle insert statement we will need a table. The following script will create a new table named MY_NUMBER.

CREATE TABLE my_number
    ( ID NUMBER (17)) ;

online tech support online computer help computer technician computer problems computer oracle sequence oracle sequence in oracle sql sequence oracle sql sequence in oracle sql oracle database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The next example of the Oracle Insert statement will insert the next value of Sequence MY_SEQUENCE into just created table MY_NUMBER. This insert statement will enter only 1 line and with every next execution there will be one line more and with greater ID value.

INSERT INTO my_number
    ( ID )
    VALUES
    ( my_sequence.nextval) ;

online tech support online computer help computer technician computer problems computer oracle sequence oracle sequence in oracle sql sequence oracle sql sequence in oracle sql oracle database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Let see what is inside the MY_NUMBER table. The value number “3” means the MY_SEQUENCE objects has increased its value by one more number.

SELECT *
   FROM my_number;

online tech support online computer help computer technician computer problems computer oracle sequence oracle sequence in oracle sql sequence oracle sql sequence in oracle sql oracle database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To use Oracle Sequence in the insert statement and once the record has been inserted you may need the ID value to store it in some other tables. The most easiest way to get the just inserted ID value is by using keyword  RETURNING. We wrote this example in PL/SQL anonymous block but you can use the RETURNING keyword with the bind variables too. The Oracle Insert with the Returning keyword is following.

INSERT INTO <table_name>
    ( <table_columns> )
    VALUES
    ( <my_values>)
      RETURNING <column_names> into <variables>;

You can return any value that was just inserted with the statement but the next example returns ID value that has been generated by Oracle Sequence during the insert.

DECLARE
  v_id number;
BEGIN
 INSERT INTO my_number
    ( ID )
    VALUES
    ( my_sequence.nextval)
      RETURNING ID into v_id ;

 DBMS_OUTPUT.PUT_LINE('value v_id is '||v_id);

END;
/

online tech support online computer help computer technician computer problems computer oracle sequence oracle sequence in oracle sql sequence oracle sql sequence in oracle sql oracle database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The ID value is stored into V_ID variable that has been declared above and after the insert procedure DBMS_OUTPUT.PUT_LINE sends text “value v_id is x” to output. Take a look at the output above and take a note the MY_SEQUENCE value has increased by 1 number more and has become number “4“.

To remove Oracle Sequence use the DROP SEQUENCE command as the following syntax:

DROP SEQUENCE <sequence_name>;

The next script will drop just created MY_SEQUENCE sequence.

DROP SEQUENCE my_sequence;
/

online tech support online computer help computer technician computer problems computer oracle sequence oracle sequence in oracle sql sequence oracle sql sequence in oracle sql oracle database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Also the Oracle Sequence can be used in Oracle Triggers, procedures or functions to insert the unique ID value into the Oracle tables.



See Also:
Oracle Select Oracle Insert

 

This online tutorial page is based on examples to be easier to follow. Now about the Oracle Date type; there are many different ways to use the Oracle Date type for calculations and one of them is subtracting. The output below returns the value in number of days as it is in the following example:

SELECT (SYSDATE+1) - SYSDATE AS Days
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Doing subtraction with the dates the Oracle datediff will return a number in a day unit. To turn it in hours we need to multiply with 24 hours. As it is written in the next example.

SELECT ((SYSDATE+1) - SYSDATE) * 24 AS Hours
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To change the result in minutes you will need to multiply the result above with 60 minutes. The following SQL query will return the date diff Oracle in minutes by multiply the result with 60 minutes.

SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 AS Minutes
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The fourth Online Tech Support example with Oracle date diff will return the date result in seconds (multiply the result with 60 seconds).

SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 * 60 AS Seconds
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

This example is how to find the differences in months Online Tech Support will use function MONTHS_BETWEEN that gives us the final result.

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-60))) AS Months
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The next date calculation is to find amount of years. You would need to convert the number of days in an amount of month and after to convert it to years by dividing it with 12 months. See the example below:

SELECT TRUNC( MONTHS_BETWEEN(SYSDATE,(SYSDATE-600)) /12 ) AS Years
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The last things is to finding out the date difference in years, months and days together and that will make the query more complicated. See the query below:

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))/12) AS Years,
       MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))),12) AS Months,
       SYSDATE - ADD_MONTHS(
         (SYSDATE-600),TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600)))
       ) AS Days
 FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To use more date diff Oracle functions find out below the date format Oracle date masks and using oracle interval functions the date calculating will look more clear and easier to read.

See Also:
Home How To Use Oracle Date Format How To Use Oracle Interval

 

This tutorial is based on examples to be easier to follow. The Oracle INTERVAL is used with date, timestamp etc and it helps to write more simpler SQL statements. The following two examples are describing how to achieve the same result using Oracle date versus interval.

The first example is using Oracle SYSDATE and multiply it with 600 seconds which is 10 times 60 seconds. The output shows current time plus 10 minutes.

SELECT sysdate, sysdate * 10 * 60 AS sysdate_plus_10min
FROM dual;

The second example will do the same calculation as the last one only with using Oracle INTERVAL.

SELECT sysdate, sysdate + INTERVAL '10' MINUTE AS sysdate_plus_10min
FROM dual;

online tech support computer help computer technician computer problems computer oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle retirement planning sql database programming database oracle retirement planning retirement sql database oracle database sql

The last SQL query above is showing how the Interval function makes your code more clear and easier to read as the 10 MINUTE can be read directly from the query.

The next example is about different options that can be used with Oracle Interval.

SELECT sysdate,
       sysdate + INTERVAL '1' second AS interval_sec,
       sysdate + INTERVAL '2' minute AS interval_min,
       sysdate + INTERVAL '3' hour AS interval_hour,
       sysdate + INTERVAL '4' DAY AS interval_day,
       sysdate + INTERVAL '5' MONTH AS interval_month,
       sysdate + INTERVAL '6' YEAR AS interval_year
FROM dual;

online tech support computer help computer technician computer problems computer oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle retirement planning sql database programming database oracle retirement planning retirement sql database oracle database sql

Keep in mind that the Oracle Interval functions can also be used with minus (“-“) too to reduce some time or days.

Now the last functions above were easy to use if you need to add only one type of units like years, but when you would need to add 1 year and 2 months then the following example can help you with it. This example is based on a new function TO_YMINTERVAL.

SELECT sysdate,
       sysdate + TO_YMINTERVAL('1-2') AS year_and_month
FROM dual;

online tech support computer help computer technician computer problems computer oracle interval oracle interval in oracle to_yminterval oracle retirement planning sql database programming database oracle retirement planning retirement sql database oracle database sql

This Oracle TO_YMINTERVAL function is used to add years and months but it can also take off the years and month by adding a minus in front of the parameters as we are done on the following example.

SELECT sysdate,
       sysdate + TO_YMINTERVAL('-1-2') AS year_and_month
FROM dual;

online tech support computer help computer technician computer problems computer oracle interval oracle interval in oracle to_yminterval oracle retirement planning sql database programming database oracle retirement planning retirement sql database oracle database sql

The next examples below are about adding days, minutes and seconds and to do that we are using another Oracle interval function named Oracle TO_DSINTERVAL.

SELECT sysdate,
       sysdate + TO_DSINTERVAL('1 02:03:04') AS day_to_sec
FROM dual;

online tech support computer help computer technician computer problems computer oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle retirement planning sql database programming database oracle retirement planning retirement sql database oracle database sql

And to remove the days, minutes or seconds take a look at the last example below.

SELECT sysdate,
       sysdate + TO_DSINTERVAL('-1 02:03:04') AS day_to_sec
FROM dual;

online tech support computer help computer technician computer problems computer oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle retirement planning sql database programming database oracle retirement planning retirement sql database oracle database sql


See Also:
Home Oracle Date Difference

 

This page is based on examples to be easier to follow. The Oracle Timestamp is more accurate than Oracle Date and it keeps a location stamp. They are the two main reasons why programmers are using the Oracle Timestamp. The location stamp is important because the same time in hours can mean different moments in different places on world. For example 1 pm in London means 8 am in New York, so using Oracle Date we do think about different moments. Timestamp in Oracle can solve the problem and we do have more exact time for transactions.

The first example is with Oracle CURRENT_TIMESTAMP and the difference between mostly used Oracle SYSTIMESTAMP is that the Oracle CURRENT_TIMESTAMP shows time location in words. The CURRENT_TIMESTAMP is used to show us – to people more readable way the Oracle timestamp value.

SELECT CURRENT_TIMESTAMP 
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The second Online Tech Support example is with Oracle SYSTIMESTAMP and as you the time location has changed to the GMT number +1 that is the same as Europe/London.

SELECT SYSTIMESTAMP
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To take out some value from Oracle Timestamp you can use Oracle Extract function. You can extract for example SECOND, HOUR, MINUTE, DAY, MONTH or YEAR value using the same keywords. The Oracle Extract syntax is following.

EXTRACT(<extracting_value_name> FROM <timestamp_type>)

And on the example below we have taken out a day number as this moment it was April 11th 2013.

SELECT EXTRACT(DAY FROM SYSTIMESTAMP) AS Timestamp_Day
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To turn an Oracle Timestamp a date you can use the Oracle Trunc function. The function not only will turn the Timestamp a date but also removes all stamp values. You can truncate date model names like minutes (“MI“), hours (“HH“), DAY, MONTH or YEAR.

TRUNC(<timestamp_value>,<date_model_name>)

Using Oracle Trunc without any additional setting will truncate the date from all time value. Take a look at the example below.

SELECT TRUNC(SYSTIMESTAMP) AS Truncated_Timestamp
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The second example with Oracle Trunc we have set the truncation limit to minutes (“MI“). The Oracle Trunc function will truncate from Timestamp all values that are smaller than minutes and stamps will be included into the removing list.

SELECT TRUNC(SYSTIMESTAMP,'MI') AS Truncated_Timestamp
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Another way to turn Oracle Timestamp to a date without truncating anything is to use Oracle Cast. You can cast different values to other types and the same you can to with Oracle Timestamp. The Oracle Cast syntax is:

CAST(<timestamp_value> AS <new_value_name>)

On the Oracle Cast example the Timestamp will be cast to Oracle Date. As you see on the following output all time values are remained in the date.

SELECT CAST(systimestamp AS DATE) AS THE_DATE
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To know more deep about the Oracle Timestamp attributes you can take a look at function Oracle Dump. The function returns description of timestamp where you can find in present the data type code, length in bytes, and internal representation of expr. Those attributes can be useful when you need to check if your timestamp value has been converted correctly.

DUMP(<timestamp_value>)

The example above demonstrating the output of Oracle Dump function with Oracle Timestamp.

SELECT DUMP(SYSTIMESTAMP)
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

It is important to know that using Oracle Timestamp in Oracle database table may cause problems for some applications. For example application Oracle Forms belongs to the same company as Oracle database but Oracle Forms would not compile when table has Timestamp type columns. In this case the Timestamp should be stored in VARCHAR2 and using Oracle Cast function can be returned to Oracle Timestamp type. Still if it’s possible keep the Oracle Timestamp values in their own type to avoid the extra work of converting types.



See Also:
Home Oracle Select