INSERT INTO table RETURNING trigger-generated primary key from SQL Server linked server to Oracle (11g)

0
votes

Scenario: get trigger-generated primary key when calling INSERT INTO from SQL Server linked server to Oracle

Given

  1. Oracle 11g table with columns PRIMARY_KEY_ID, FIELD1, FIELD2, CREATE_DATE. Table has "BEFORE INSERT" trigger that selects NEXTVAL from a sequence into PRIMARY_KEY_ID field.

  2. SQL Server 2008 R2 with Linked Server to the Oracle database containing the table above.

When I insert a record into the Oracle table, then I want to retrieve the trigger-generated primary key.

How do I do this?

1
sql-serveroracle

1 Answers

0
votes
  1. Make sure these properties are set on the SQL Server linked server:
    • RPC=True
    • RPC Out=True

  2. Execute this code in SQL Server:

    DECLARE @Field1 NVARCHAR(42);
DECLARE @Field2 NVARCHAR(42);
DECLARE @PrimaryKeyValue INT;
EXECUTE (
    'begin INSERT INTO MYSCHEMA.MYTABLE (
        FIELD1
        ,FIELD2
        ,CREATE_DATE
        )
    VALUES (
        ?
        ,?
        ,sysdate
        ) RETURNING PRIMARY_KEY_ID INTO :PrimaryKeyValue; end;'
    ,@Field1
    ,@Field2
    ,@PrimaryKeyValue OUTPUT
    ) at oracle_linked_server;

Notes

  1. begin and end; are required in the statement.
  2. The @PrimaryKeyValue variable declared in SQL Server is the same as the :PrimaryKeyValue output parameter; Oracle uses a colon prefix for parameters.
  3. See Calling Oracle stored procedure with output parameter from SQL Server, which provided the inspiration for this answer.