ORACLE - Online Tech Support

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 Use Oracle UNION Operator

online tech support Comments Off

The Oracle operators Oracle Union and Union ALL are to unite two SELECT statements with same amount columns. Also all columns should be in the same data type in both queries or the operator Oracle Union will raise an error and the select wouldn’t return any row.

The syntax of oracle union is:

SELECT <values>
  FROM <table_1>
UNION
SELECT <values>
  FROM <table_2>;

Operator Union in Oracle will remove duplicated lines from the queries. The following example shows how both dual tables have the same values and the last table has different ones. The Oracle Union will return to lines instead of three.

SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION
SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION
SELECT 2 AS first, 3 AS second, 4 AS third
  FROM DUAL;

The operator Union ALL in Oracle has the same purpose as Oracle Union with only one difference the Union ALL does not remove duplicated rows. The Oracle Union ALL syntax is following:

SELECT <values>
  FROM <table_1>
UNION ALL
SELECT <values>
  FROM <table_2>;

The second example is using the same Oracle Selects as above only in this time we are using the Union ALL operator instead of Oracle Union:

SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION ALL
SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION ALL
SELECT 2 AS first, 3 AS second, 4 AS third
  FROM DUAL;

The previous query returned all three rows and ignored that two of them are duplicated.

You can use Oracle Union and Union ALL in the same Select statement, but you should keep in mind the first comparing the rows and removing duplicated lines and the second one does not.

To demonstrate the differences between UNION ALL and UNION we will use the following Select statement. And to understand better the Oracle Union example outputs take a look at the following SQL query.

SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11;

The SQL query above is returning 10 rows with value number “1” and on the next example we will unite two exact queries as the last one using Oracle Union.

SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11
  UNION
 SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11;

The Oracle Union operator returned only one line out of 20 because the Union operator does group the values and returns only rows with unique values. The next example is using the same Select statements but they are united with the Union ALL operator.

SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11
  UNION ALL
 SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11;

As you see from the output all 20 rows are there and none of them is missing. Oracle Union ALL does not remove duplicated lines and it does keep both SQL query outputs as is.

The second important thing to know is that the Oracle Union is quite slow with many rows. Use it only where Oracle Union ALL cannot be use, but prefer to use the Oracle Union ALL operator and do Group By in inner queries where it is needed.

How To Write Oracle JOIN – Part 3

online tech support Comments Off

The Right Outer Join looks almost the same as the Oracle Left Outer Join (Part 2) only that the outer condition sign is set on the left table side. On this condition the master table is the right joined table and the slave table is on the left side. That means the select statement will return all lines from the right joined table “MY_B” and only the matching lines from the left joined table “MY_A“. The following example will demonstrate the Oracle right outer join:

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a, my_b
WHERE a_id(+) = b_id;

The ANSI standard the Oracle Right Outer Join will change also keywords the LEFT with RIGHT as the following syntax is showing:

SELECT <columns> 
  FROM <table1> 
    RIGHT OUTER JOIN <table2> ON <conditions>;

The next Oracle right outer join will return all lines from the right side joined table “MY_B” and only matching lines from the left joined table “MY_A“.

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a RIGHT OUTER JOIN my_b
 ON a_id = b_id;

The next outer joins type is Oracle Full Outer Join. It means we will show all lines in both tables and the join will match them where it’s possible by added condition. Oracle has full outer join condition only with the ANSI standard style. The syntax is following:

SELECT <columns> 
  FROM <table1> 
   FULL OUTER JOIN <table2> ON <conditions>;

This Oracle Full Outer Join example has match for IDs 7,8,9 and 10. The other lines have no match so you can find next to them a NULL column.

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a FULL OUTER JOIN my_b
 ON a_id = b_id;

The another join type is the Oracle Nested Loop Join. This Oracle join will return all matching values that did exists in the slave query. There is an exception about the slave query that there SHOULD NOT be any NULL values or the join returns no results. The Oracle Nested Loop Join syntax is:

SELECT <columns> 
  FROM <table1> 
 WHERE <columns> IN (SELECT <columns> FROM <table2>);

The following Nested Loop Join will have the master table “MY_A” and the slave table “MY_B“. All ID values existing in table “MY_B” will try to be matching the “IN” keyword.

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a
WHERE a_id IN (SELECT b_id FROM my_b);

Table “MY_A” has returned only 7,8,9,10 because they are only matching values in table “MY_B” that is sitting in the sub-query after the “IN” keyword.

See Also:

Previous Part

Home

Next Part

How To Use Oracle EXTRACT From Date

online tech support Comments Off

This page is based on examples to be easier to follow. The Oracle Extract is ANSI standard function and it returns a specified value from a date value. The Oracle Extract syntax is:

EXTRACT ( <returning value type> FROM <date type>)

Only the following “returning value type” and “date type” combinations are valid:

when “returning value type” is YEAR or MONTH then “date type” can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH
when “returning value type” is DAY then “date type” can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND
when “returning value type” is HOUR, MINUTE or SECOND then “date type” can be TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND
when “returning value type” is TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION or TIMEZONE_OFFSET then “date type” can be TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE

The first example with Oracle Extract will return a number of the current month. The example date is November 27th of 2012 and as November is 11th month in the year the Month column shows number “11“.

SELECT EXTRACT (MONTH FROM SYSDATE) AS Month, SYSDATE
  FROM DUAL;

The second example takes out a year number from the date. The sample output is in the first column “2012” and the second column shows the current date to make this example more understandable.

SELECT EXTRACT (YEAR FROM SYSDATE) AS YEAR, SYSDATE
  FROM DUAL;

The third SQL query extracts the number of hours from the Oracle timestamp. We are using a timestamp and not date because Hours, Minutes and Seconds can be extract only from a timestamp type (see the valid combinations list above). That moment we had 12pm and the Hours column shows the same number.

SELECT EXTRACT (HOUR FROM SYSTIMESTAMP) AS HOURS, SYSTIMESTAMP
  FROM DUAL;

This example below will show you a valid combination that logically is not valid. In short, you cannot extract a time zone from Oracle timestamp. The reason is quite simple one time offset have been assigned more than one time zone and Oracle database doesn’t which time-zone region name to return. The only output you would see in this case is “UNKNOWN“.

SELECT EXTRACT (TIMEZONE_REGION FROM systimestamp)
   FROM DUAL;