Insert/Update records in Oracle SQL database?

Question

I recently started using Oracle. Before that I worked with SQL Server 2008/2012. I noticed some differences between Oracle and SQL Server. I created tables in SQL Server with some basic columns like First, Last name, email, etc. Here is example of my table structure:

Name          Data Type  Size   Not Null
RECORDID      NUMBER            true    //This is primary key (auto increment)  
FIRST         VARCHAR2   50     true        
LAST          VARCHAR2   50     true        
EMAIL         VARCHAR2   320    true        
PHONE         CHAR       10     true        
FILEPATH      VARCHAR2   1000   false       
TYPE          CHAR       1      true        
SUBJECT       VARCHAR2   100    true        
DESCRIPTION   VARCHAR    4000   true    // This should be varchar(max)  
ACTIONDATE    DATE              true    

I have used Identity Column option in Oracle SQL Developer to set RecordID to Column Sequence. This is different than SQL Server and I'm looking for the same behavior. That column should auto increment for each new row added to the table.

Is that correct way to set Identity Column in Oracle? Here is example of my Insert Statement:

<cfquery name="insertRec" datasource="test">
    INSERT INTO Table1 (
        RecordID, First, Last, Email, Phone, 
        FilePath, Type, Subject, Description, ActionDate 
    )VALUES(
        RecID_SEQ1.NEXTVAL,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="trim(#form.first#)" maxlength="50">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="trim(#form.last#)" maxlength="50">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="trim(#form.email#)" maxlength="50">,
        <cfqueryparam cfsqltype="cf_sql_char" value="trim(#form.phone#)" maxlength="10">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="trim(#form.file#)" maxlength="1000">,
        <cfqueryparam cfsqltype="cf_sql_char" value="trim(#form.type#)" maxlength="1">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="trim(#form.subject#)" maxlength="100">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="trim(#form.appdescr#)" maxlength="4000">,
        CURRENT_TIMESTAMP
    )
</cfquery>

In transaction above how to set auto increment ID? Also I would like to return scope identity same as in SQL Server. Is there a way to achieve that in Oracle? If anyone have any suggestions on how this can be achieved or how to improve my code above please let me know. I just started with Oracle and this is new for me.

CREATE TABLE "MYDB"."MYTABLE" 
   (    "RECORDID" NUMBER, 
    "FIRST" VARCHAR2(20 BYTE), 
    "LAST" VARCHAR2(20 BYTE), 
    "EMAIL" VARCHAR2(20 BYTE), 
    "PHONE" CHAR(10 BYTE), 
    "FILEPATH" VARCHAR2(20 BYTE), 
    "TYPE" CHAR(1 BYTE), 
    "SUBJECT" VARCHAR2(20 BYTE), 
    "DESCRIPTION" VARCHAR2(20 BYTE), 
    "ACTIONDATE" DATE, 
    "PRIORITY" CHAR(1 BYTE)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "MYDB" ;
--------------------------------------------------------
--  DDL for Index MYTABLE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "MYDB"."MYTABLE_PK" ON "MYDB"."MYTABLE" ("RECORDID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "MYDB" ;
--------------------------------------------------------
--  DDL for Trigger MYTABLE_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "MYDB"."MYTABLE_TRG" 
BEFORE INSERT ON MYTABLE
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    NULL;
  END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "MYDB"."MYTABLE_TRG" ENABLE;
--------------------------------------------------------
--  DDL for Trigger MYTABLE_TRG1
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "MYDB"."MYTABLE_TRG1" 
BEFORE INSERT ON MYTABLE
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.RECORDID IS NULL THEN
      SELECT ADPR_SEQ1.NEXTVAL INTO :NEW.RECORDID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/

ALTER TRIGGER "MYDB"."MYTABLE_TRG1" ENABLE;

Answer 1

To expand on what thatjeffsmith said, you do not have an identity column. In fact, you do not even have a primary key constraint, just a unique column. An identity column would look like:

create table mytable (
    recordid number generated always by default on null as identity ...

Then, you would not need to specify that column in the INSERT statement when creating a new row.

If you're going to use this sequence-with-trigger pattern to insert your column, you'd get the new id by doing something like this:

insert into mytable ( first, ... ) 
values ( 'john', ... )
returning recordid into :myrecordid

This will populate myrecordid with the recordid of the new row.

Answer 2

This is what you want - and it's available in Database version 12c and higher.

That generates code that looks like this -

CREATE TABLE TABLE4 
(
  COLUMN1 NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 1 MINVALUE 1 NOT NULL 
, COLUMN2 VARCHAR2(20) 
, CONSTRAINT TABLE4_PK PRIMARY KEY 
  (
    COLUMN1 
  )
  ENABLE 
);

Then to do an insert, and get back the generated ID -

declare
 my_new_record integer;
begin
 insert into table4(column2) values ('Hi') returning column1 into my_new_record;
 dbms_output.put_line('your new record ID is: ' || my_new_record);
end;
/

If you're not on 12c, you can still do all this - you just have a TRIGGER and SEQUENCE to create and maintain as well.