CODEWITHSUNDEEP
sqldatabaseoracle-database
Fede E.
Fede E.

Reputation: 1918

Getting ID of inserte row in Oracle, using sequence and trigger to insert the id

I'm trying to get the vale of an "autoincrement" column in Oracle db. To create that column I use a sequence and a trigger.

Creation script:

create table SD_LOG (
    ID_SD_LOG       number(19) primary key,
    LOG_DATE        timestamp(3) default systimestamp NOT NULL,
    MODULE          nvarchar2(15) NOT NULL,
    INSTANCE        number(5) NOT NULL,
    REMOTE_ADDR     nvarchar2(39) NOT NULL,
    USERNAME        nvarchar2(30) NOT NULL,
    USER_AGENT      nvarchar2(1024) NOT NULL,
    HTTP_METHOD     nvarchar2(8) NOT NULL,
    HTTP_REQ_URL    nvarchar2(1024) NOT NULL,
    HTTP_STATUS     number(5),
    ERROR           nvarchar2(100),
    DETAILS         clob
);

/

-- Generate ID using sequence and trigger
create sequence SD_LOG_seq start with 1 increment by 1;

create or replace trigger SD_LOG_seq_tr
 before insert on SD_LOG for each row
 when (new.ID_SD_LOG is null)
begin
 select SD_LOG_seq.nextval into :new.ID_SD_LOG from dual;
end;
/

Every time I make an insert I need to get the ID_SD_LOG created value for later row update.

I followed some other questions but for some reason the statement I am trying is failing with:

SQL Error: ORA-00905: missing keyword

This is the statement I'm trying to run (insert alone, without the 'RETURNING...' works just fine):

INSERT INTO SD_LOG 
(MODULE, INSTANCE, REMOTE_ADDR, USERNAME, USER_AGENT, HTTP_METHOD, HTTP_REQ_URL) 
VALUES
('modulename', '1', '192.168.0.1', 'User Name', 'blah blah blah blah', 'POST', '/page?query=1234567890')
RETURNING ID_SD_LOG INTO gen_id;

Any idea why am I getting that error? Or any other way to do this without using a procedure?

Upvotes: 3

Views: 208

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

The RETURNING clause is a PL/SQL language feature. You cannot use it from ordinary SQL contexts, such as SQL Developer scripts, SQL*Plus, or JDBC statements. That's very unfortunate of course. There's an idea on this Oracle ideas platform that can be upvoted, to fix this.

You can, however, wrap your statement in an anonymous block, to get back to the PL/SQL context:

SET SERVEROUTPUT ON
DECLARE
  gen_id SD_LOG.ID_SD_LOG%TYPE;
BEGIN
  INSERT INTO SD_LOG 
  (MODULE, INSTANCE, REMOTE_ADDR, USERNAME, USER_AGENT, HTTP_METHOD, HTTP_REQ_URL) 
  VALUES
  ('modulename', '1', '192.168.0.1', 'User Name', 'blah blah blah blah', 'POST', '/page?query=1234567890')
  RETURNING ID_SD_LOG INTO gen_id;

  dbms_output.put_line(gen_id);
END;
/

Upvotes: 4

Related Questions