Home » SQL & PL/SQL » SQL & PL/SQL » how to write LAST_INSERT_ID() in oracle (oracle 11g)
how to write LAST_INSERT_ID() in oracle [message #596676] Wed, 25 September 2013 11:35 Go to next message
vkanuri
Messages: 20
Registered: August 2013
Location: United States
Junior Member
Hi ,I have a procedure in mysql that need to be converted to oracle.I tried to convert it ,everything looks good except the function LAST_INSERT_ID().Can someone please help how to write this in oracle.

mqsql procedure
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_ac_message`(
IN in_process_run_id INT,
IN in_processID INT, 
IN in_messagename VARCHAR(25),
IN in_message VARCHAR(200))
BEGIN

DECLARE vNOW DATETIME;
DECLARE V_MESSAGEID INT;
SET vNOW = NOW();

INSERT INTO PRDDCTN.PROCESS_MESSAGE (PROCESS_ID, MESSAGE_NAME, MESSAGE_DESCRIPTION, CREATE_USER_ID, CREATE_DATE)
VALUES (in_processID, in_messagename, in_message, USER(), vNOW);

SELECT LAST_INSERT_ID() INTO V_MESSAGEID;

INSERT INTO PRDDCTN.PROCESS_LOG (PROCESS_RUN_ID, PROCESS_MESSAGE_ID, CREATE_DATE)
VALUES (in_process_run_id, V_MESSAGEID, vNOW);

End ;;
DELIMITER ;


oracle procedure
create or replace 
PACKAGE body Mysql_To_Oracle
IS
Procedure Add_Ac_Message(In_Process_Run_Id In Number,
In_Processid In Number,
In_Messagename In Varchar2,
In_Message In Varchar2)

As
vNOW TIMESTAMP(0);
 V_MESSAGEID NUMBER;
BEGIN
vNOW := SYSTIMESTAMP;

INSERT INTO PRDDCTN.PROCESS_MESSAGE (PROCESS_ID, MESSAGE_NAME, MESSAGE_DESCRIPTION, CREATE_USER_ID, CREATE_DATE)
VALUES (in_processID, in_messagename, in_message, USER(), vNOW);

SELECT LAST_INSERT_ID() INTO V_MESSAGEID FROM dual;

INSERT INTO PRDDCTN.PROCESS_LOG (PROCESS_RUN_ID, PROCESS_MESSAGE_ID, CREATE_DATE)
VALUES (in_process_run_id, V_MESSAGEID, vNOW);
ENd Add_Ac_Message;
ENd Mysql_To_Oracle;

Error:
ORA-00904: "LAST_INSERT_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"


Thanks
Re: how to write LAST_INSERT_ID() in oracle [message #596677 is a reply to message #596676] Wed, 25 September 2013 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 23054
Registered: January 2009
Senior Member
no LAST_INSERT_ID() function exists for schema running the posted code.

Re: how to write LAST_INSERT_ID() in oracle [message #596678 is a reply to message #596677] Wed, 25 September 2013 11:47 Go to previous messageGo to next message
Bill B
Messages: 1130
Registered: December 2004
Senior Member
read up on the returning clause for the insert statement.
icon5.gif  Re: how to write LAST_INSERT_ID() in oracle [message #596679 is a reply to message #596676] Wed, 25 September 2013 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59788
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

MySQL LAST_INSERT_ID returns the last identifier created by an AUTO_INCREMENT column.
There is no AUTO_INCREMENT column in Oracle so the question is:
"How did you implement this feature?".

Re: how to write LAST_INSERT_ID() in oracle [message #596680 is a reply to message #596679] Wed, 25 September 2013 11:53 Go to previous messageGo to next message
Bill B
Messages: 1130
Registered: December 2004
Senior Member
Oracle 12 has an Identity column now (finally)
Re: how to write LAST_INSERT_ID() in oracle [message #596681 is a reply to message #596679] Wed, 25 September 2013 11:55 Go to previous messageGo to next message
vkanuri
Messages: 20
Registered: August 2013
Location: United States
Junior Member
is any sequence required in oracle to implement this feature.
Re: how to write LAST_INSERT_ID() in oracle [message #596682 is a reply to message #596681] Wed, 25 September 2013 11:57 Go to previous message
Bill B
Messages: 1130
Registered: December 2004
Senior Member
In 11g, you will need a sequence and a trigger on the table. In oracle 12, NO
Previous Topic: Lag Function problems pls help
Next Topic: Please correct the update statement
Goto Forum:
  


Current Time: Wed Nov 26 06:57:28 CST 2014

Total time taken to generate the page: 0.13863 seconds