CODEWITHSUNDEEP
oracle-databaseplsqloracle10goracle-sqldeveloper
shamim
shamim

Reputation: 6770

How to return newly inserted record column value

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.

Upvotes: 4

Views: 17493

Answers (3)

ibre5041
ibre5041

Reputation: 5288

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;

Upvotes: 7

Maddy
Maddy

Reputation: 3816

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;

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231661

In order to return something, your procedure would need to declare an OUT parameter. Given that you're explicitly referencing a sequence in your INSERT, you can use the currval of the sequence to return the inserted value. Something like

CREATE OR REPLACE PROCEDURE TST_SMS_IN
(
  P_MOBILE_NO IN varchar2, -- Adding IN just for clarity
  P_SMS_BODY  IN VARCHAR2,
  p_IN_ID    OUT NUMBER    -- Adding the OUT parameter
)
IS
  <<snip>>
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);

     SELECT sms_in_seq.currval
       INTO p_in_id
       FROM dual;

     COMMIT;
END TST_SMS_IN;

As an aside, a commit in a simple stored procedure like this strikes me as highly dubious. That means that you can never use this procedure inside a larger transaction. It seems highly unlikely that you would never want to compose a transaction that includes writing to this table.

You've tagged this as 10g which is why I'm doing the SELECT ... INTO to populate p_in_id rather than just doing a direct assignment. I believe that support for just saying p_in_id := sms_in_seq.currval was added in 11.1 but I'm not 100% certain about that.

Upvotes: 1

Related Questions