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;

"I have used Identity Column option in Oracle SQL Developer to set RecordID to Column Sequence" - are you in the data modeler? i can't tell if you actually have an IDENTITY column or a trigger with a sequence. Please share the full DDL for your table. — thatjeffsmith
– thatjeffsmith, Commented Nov 16, 2018 at 14:16
@thatjeffsmith I use Oracle SQL Developer and in there I was setting Column Identity. Not sure if that will answer your questions. Thank you. — espresso_coffee
– espresso_coffee, Commented Nov 16, 2018 at 14:19
if you used an identity column, you shouldn't need the ' RecID_SEQ1.NEXTVAL' in your INSERT...the database provides that value for you - but we need to see your table DDL. open the table in sql developer, go to the SQL page, get the code, and add it to your question, then i'll give you a good answer — thatjeffsmith
– thatjeffsmith, Commented Nov 16, 2018 at 14:22
@thatjeffsmith I added DDL please take a look and let me know if that helps. — espresso_coffee
– espresso_coffee, Commented Nov 16, 2018 at 14:30
ok, you don't have a identity column...and your trigger says if null, replace with the nextval from your sequence, so your code should just insert everything BUT RECORDID — thatjeffsmith
– thatjeffsmith, Commented Nov 16, 2018 at 14:45

eaolson · Accepted Answer · 2018-11-16 15:39:10Z

2

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.

answered Nov 16, 2018 at 15:39

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

espresso_coffee Over a year ago

I'm trying to set Auto Increment ID that will be Primary Key for that table. In SQL I can do that by simply setting auto increment to Yes and Right Click on the column and check PK. Is it possible to set that in Oracle? Is that recommended or there is better way to this?

thatjeffsmith · Accepted Answer · 2018-11-16 17:25:35Z

1

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.

edited Nov 16, 2018 at 17:25

answered Nov 16, 2018 at 15:57

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

7 Comments

espresso_coffee Over a year ago

My Oracle is 11g, and I do not have the option Generated As Identity. This just seems way to complicated in comparing to SQL. Is there a better approach/solution in ORACLE to create unique ID for each record? Thank you.

thatjeffsmith Over a year ago

already said so, a trigger and sequence combo. And 11g is OLD. Like, more than 5 years old. Once you get to 12, this all becomes just as easy as you say in 'SQL', if not more so.

thatjeffsmith Over a year ago

the root of your question is how to get to the generated ID field for your new record, both of our answers demonstrate to you how this works. mark one as correct. if you want to know how to do a trigger/sequence in oracle - change your question, or better yet see stackoverflow.com/questions/11296361/…

espresso_coffee Over a year ago

Well I tried to use coldfusion methods but they did not return the rowid. I'm on CF 10 and maybe that is the problem.

thatjeffsmith Over a year ago

you can't return ROWID - but why would you ever want or care about the ROWID? or do you mean RECORD_ID?

|

Collectives™ on Stack Overflow

Insert/Update records in Oracle SQL database?

2 Answers 2

1 Comment

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related