Is there an easy way to obtain the primary key of an insert SQL command?

[Former Member]

I have a transaction with some Actions. One of them is a SQL command inserting a new line in a Oracle database table, say Table_A. The next action is an Insert SQL command too, but to another table, and I need the primary key of the line just created in Table_A to put in a foreign key column of this other table (i am creating an line in Table_B that points to the line in table_A).

Is there an easy way to get this PK? I hope there is another way than making an intermedate Action with a Select command to identify the line in table_A that I have just created......

Thank you in advance,

Nuno Cunha

[Former Member]

Unfortunately I can not use stored procedures in this project. In this particular case it is not difficult make a select command after to find my new registry (but I am still interested know how to do this in an elegant way!).

Thank you all

[Former Member]

Hi all,

Unfortunately a stored procedure difficult things when there is a need to exchange server database from Oracle to another one.

I was hoping that the solution was inside the SAP MII, but not creating another Action to search the line inserted at the previous Action.

Thank you very much the replies.

Nuno Cunha

[Former Member]

You can do almost everything you can do with a Stored Procedure via a Fixed Query. The disadvantage is that the query needs to be reparsed each time, but that is negligible. You can also probably avoid cursors with this approach if all you need to return is a simple scalar value. The nice thing is that the entire fixed query (which can contain multiple SQL statements) runs on the same connection, whereas multiple SQLQuery actions in a BLS transaction cannot be guaranteed to run on the same connection.

[Former Member]

Hi,

I am a new user of xMII and Oracle, so I don' t know how to do this (my database is Oracle). How to put two commands (Insert and then Select) together using Fixed Query? In TSQL (MS SQL Server) I know I can use "go" between SQL commands. Is this the same in Oracle?

Using TSQL I now there is @@IDENTITY that keep the last PK created in that transaction. Is there something like this in Oracle?

ps: (okay okay, this forum is about xMII not Oracle.

[Former Member]

Nuno,

in Oracle, you will use PL/SQL as programming language in your stored procedures instead of TSQL für SQL Server.

Here are some starting points:

Comparison SQL Server - Oracle

PL/SQL Tutorial

You may find more hints in this forum searching for stored procedures.

Hope this helps.

Michael

[Former Member]

If you are on Oracle 10g or newer, you can use the "RETURNING" keyword to get the last inserted ID.

INSERT INTO yourtable(Thing1,Thing2) VALUES ('Cheese',234.56) RETURNING id INTO myNewID;

[Former Member]

Hi,

It seems this command works only in stored procedures, where we can define the "myNewID" variable. I was not able to use it in any command line sql software....

Nuno

[sidnooradarsh]

Hi,

This is how you can run in Oracle SQL command line

SQL> var empid number

SQL> insert into employees(employee_id,emp_name, emp_location,hire_date) values(1001,'Sam', 'IND', to_date('110697','mmddyy')) returning employee_id into :empid;

SQL>Print empid

Else declare variable empid in any PL/SQL block and use it

SET SERVEROUTPUT ON
DECLARE
  empid employees.employee_id%TYPE;
BEGIN
insert into employees(employee_id,emp_name, emp_location,hire_date) values(1001,'Sam', 'IND', to_date('110697','mmddyy'))   returning employee_id into empid;
  
  COMMIT;

  DBMS_OUTPUT.put_line('Latest employee Id=' || empid);
END;
/

Else as you mentioned go for stored procedure

Regards,

Adarsh

Edited by: Adarsh Sidnoor on Nov 30, 2009 11:45 PM

[sidnooradarsh]

Hi Nuno,

Rightly as Michael and Christian as said go for stored procedure.

You can write a Stored procedure which looks something like this.....

create or replace
procedure
Test_Proc(outcur OUT GLOBALPKG.CUR)
as
begin
insert into EMP_LIST(EMP_CODE,EMP_NAME) values('123','SAM');
open outcur for
select EMP_CODE from EMP_LIST e where (select max(rownum) from EMP_LIST )=
(select count(*) from EMP_LIST where e.rowid>=EMP_LIST.rowid);
end;

Where,

GLOBALPKG.CUR is a Ref cursor declared in a package

create or replace PACKAGE GLOBALPKG
AS
TYPE CUR is REF CURSOR;
END;

In query template use FixedQueryWithOutput mode

Hope this helps!!

Regards,

Adarsh

Edited by: Adarsh Sidnoor on Nov 6, 2009 3:07 PM

[Former Member]

Adarsh, unfortunately, approaches that query "max" values or last rows are not reliable and will not work in scenarios where there is any level of frequent inserts/data access. This type of approach is vulnerable to timing issues. As such, you should always use "atomic" techniques that get the last inserted ID. These tend to be database-specific, but are the only reliable way to obtain this information.

[Former Member]

Nuno,

I guess you are using some function to automatically populate your primary key of Table_A, otherwise you would already have the key value in your transaction.

Follow the way Christian proposed building a stored procedure. You may use the Oracle INSERT INTO ... RETURNING statement to get the primary key used in the insert and deliver it back to the calling MII query. You can then use the value in your transaction for your insert to Table_B.

Have a look at [ORACLE Insert|http://www.psoug.org/reference/insert.html] and search for "Returning" for more details.

Hope this helps.

Michael

[Former Member]

Stored procedure.