How to return newly inserted record column value

Question

I am new to Oracle SQL Developer. I am trying to write a SP to insert value,After insert record sp return correspondent talbe’s column= IN_ID(it’s primary key) value.My SP using the following query

Table:

CREATE TABLE SMS_IN
(
  IN_ID      NUMBER(20)                         NOT NULL,
  MOBILE_NO  VARCHAR2(20 BYTE)                  NOT NULL,
  SMS_BODY   VARCHAR2(200 BYTE)                 NOT NULL,
  STATUS     NUMBER(1)                          NOT NULL,
  USERS      VARCHAR2(20 BYTE)                  NOT NULL,
  REMARKS    VARCHAR2(200 BYTE),
  IN_TIME    DATE                               NOT NULL
)

SP:

CREATE OR REPLACE PROCEDURE TST_SMS_IN
(
--P_MSISDN NUMBER,
P_MOBILE_NO varchar2, 
P_SMS_BODY VARCHAR2
)
IS

V_SYSTEM_DATE DATE;
V_YEAR NUMBER;
V_YEAR_SYSTEM NUMBER;
V_TABLE_NAME VARCHAR2(100) :='';
V_SQL VARCHAR2(1000) :='';
V_ERROR_MESSAGE VARCHAR2(1000) :='';

P_AVG NUMBER;
V_CREATED_BY NUMBER;

BEGIN
     INSERT INTO SMS_IN(IN_ID,MOBILE_NO,SMS_BODY,STATUS,USERS,REMARKS,IN_TIME) VALUES
     (SMS_IN_SEQ.NEXTVAL,P_MOBILE_NO,P_SMS_BODY,0,'gp','TEST',sysdate);
     COMMIT;

END TST_SMS_IN;

After insert help me to return newly inserted record column= IN_ID value.

If have any query please ask.Thanks in advanced.

Answer 1

Aside using sequence's .currval you can also use RETURNING clause part of insert statement.

INSERT INTO SMS_IN(IN_ID,MOBILE_NO,SMS_BODY,STATUS,USERS,REMARKS,IN_TIME) 
VALUES (SMS_IN_SEQ.NEXTVAL,P_MOBILE_NO,P_SMS_BODY,0,'gp','TEST',sysdate)
RETURNING IN_ID INTO p_in_id;

Answer 2

CREATE OR REPLACE PROCEDURE TST_SMS_IN
(
--P_MSISDN NUMBER,
P_MOBILE_NO varchar2, 
P_SMS_BODY VARCHAR2,
p_IN_ID    INTEGER
)
IS

V_SYSTEM_DATE DATE;
V_YEAR NUMBER;
V_YEAR_SYSTEM NUMBER;
V_TABLE_NAME VARCHAR2(100) :='';
V_SQL VARCHAR2(1000) :='';
V_ERROR_MESSAGE VARCHAR2(1000) :='';

P_AVG NUMBER;
V_CREATED_BY NUMBER;

BEGIN
     INSERT INTO SMS_IN(IN_ID,MOBILE_NO,SMS_BODY,STATUS,USERS,REMARKS,IN_TIME) VALUES
     (SMS_IN_SEQ.NEXTVAL,P_MOBILE_NO,P_SMS_BODY,0,'gp','TEST',sysdate);

     p_IN_ID := SMS_IN_SEQ.CURRVAL;

     COMMIT;

END TST_SMS_IN;