Defining 12c IDENTITY Columns in Oracle SQL Developer Data Modeler

Defining Triggers and Sequences to populate identity columns in Oracle Database is no longer required. You have an Oracle Database 12c instance up and running, and you’re ready to hit the ground running.

Now How Do I Draw That Up in SQL Developer Data Modeler?

Draw your table. You’ll want a column. 

RELATIONAL MODEL, COLUMN PROPERTIES

Ok, the Modeler now knows that this column is identifying, and that’s it’s going to be self-incrementing. Next we need to fill in the details.

MIN, MAX, INCREMENT BY, CACHE?

Last thing.

The modeler knows what you want to do with the column, but it doesn’t know what RDBMS features it has at its disposal. We need to go into the Physical Model level, ensuring we create a 12c physical model.

AFTER YOU’VE CREATED THE 12C PHYSICAL MODEL, GO TO THE TABLE, COLUMN AND ACCESS ITS PROPERTIES

YOU WANT THE ONE THAT SAYS ‘IDENTITY’ 

Here you go >>

THAT LOOKS RIGHT TO ME…

Oracle 12c and Interactive Grid Oracle Apex

To make Interactive Grid work smoothly using auto generate sequence feature of Oracle 12c, remove the Primary Key column or the column on which you have auto generate sequence from SQL query in Interactive Grid.
That's it!

To know how to generate Auto Sequence, click on below link
http://mythass.blogspot.my/2017/11/auto-generate-sequence-in-oracle-12c.html

I hope the above is useful to you.

Auto Generate Sequence in Oracle 12c

Oracle 12c - No need to create sequences.

Use the below highlighted syntax to auto generate Sequences in Oracle 12c.

CREATE TABLE ABC (
    abc_id           NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name             VARCHAR2(50)
)
ALTER TABLE ABC ADD CONSTRAINT abc_id_pk PRIMARY KEY ( abc_id );

Note: In case you are passing the value for primary key manually, Oracle won't generate the sequence for that transaction and will start from the same number where it left last time irrespective of manual insertion.

How to split comma separated string in new row using CONNECT BY and REGEXP?

SELECT REGEXP_SUBSTR('APPLE,BOB,CARS', '[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('APPLE,BOB,CARS', '[^,]+', 1)

select * from
(select 'GIOVANNI COSTELLO' writer, 'RODRIGUEZ' author from dual) tt
where exists
(select * from (
select trim(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) as c1
from
(
select 'ARMIN RODRIGUEZ, GIOVANNI COSTELLO, RUBEN RODRIGUEZ ALARCON, RUEDIGER SKOCZOWSKY, XAVIER NAIDOO' as str
from dual) CONNECT BY LEVEL <= REGEXP_COUNT(str, '[^,]+', 1)
) where c1 = tt.writer);

select regexp_substr('abc:xyz:abc:pqr:xyz','[^:]+',1, level) str from dual

connect by level <= regexp_count('abc:xyz:abc:pqr:xyz','[^:]+', 1)

Get Data of Particular Month in Oracle

This is the easiest way of getting data of particular month

Select * from table where extract(MONTH from column_name ) = 4; -- April

Column_name should be of date type or timestamp.