ORACLE DATABASE - Online Tech Support

How To Use Oracle TO_CHAR Function

online tech support Comments Off

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;

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;

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;

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;

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.

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 10ⁿ (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.

How To Use Oracle NVL Function

online tech support Comments Off

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 );

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 );

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 );

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) ;

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) ;

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

How To Use Oracle Date Format

online tech support Comments Off

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;

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;

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;

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.

How To Use Oracle DECODE Function

online tech support Comments Off

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;

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;

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;

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;

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;

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

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

How To Use Oracle INSTR Function

online tech support Comments Off

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;

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;

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;

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;

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

How To Use Oracle ANALYTIC Functions

online tech support Comments Off

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;

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 ) ;

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 ) ;

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 ) ;

See Also:
Home Oracle Select

How To Use Oracle Sequence

online tech support Comments Off

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;
/

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;

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)) ;

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) ;

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;

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;
/

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;
/

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

How To Find in Oracle Date Difference

online tech support Comments Off

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;

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;

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;

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;

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;

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;

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;

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.

How To Use Oracle INTERVAL Function

online tech support Comments Off

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;

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;

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;

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;

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;

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;

See Also:
Home Oracle Date Difference

How To Use Oracle TIMESTAMP

online tech support Comments Off

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;

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;

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;

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;

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;

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;

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;

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

Older Entries