Forum Stats

  • 3,837,498 Users
  • 2,262,265 Discussions
  • 7,900,307 Comments

Discussions

Return Key Into Item Code

Alter Boy
Alter Boy Member Posts: 65
edited Nov 1, 2018 12:34PM in APEX Discussions

Hi,

The APEX built-in 'Return Key Into Item' function is great, but I'd like to use it manually. Is there a specific function it uses to do this?

To elaborate, I want to make a manual row processor from pl/sql code. I have a trigger that creates a primary key if my value is null, which is why I use the 'Return Key Into Item' function, however I would like to make this a manual thing in my own code. I would like to create a record, have that primary key generated, be able to capture the value of that primary key in a variable, and then continue my pl/sql code with the ability to refer to that primary key.

As a basic example:

Insert Into Employees (Name, Job) values ('Matthew', 'Astronaut'); -- This table also has Employee_PK, but left it out because my trigger will supply that value

-- Code to take the value of that Employee_PK and store it in variable primaryKeyValue

dbms_output.put_line(primaryKeyValue); -- I should get the value of the primary key in that newly created record

Any help is appreciated, thank you.

Matthew

Tagged:

Best Answer

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Nov 1, 2018 11:56AM Answer ✓

    This is a DB thing, not an APEX thing.

    You need to use the RETURNING clause of an INSERT statement.

    MAKE SURE YOU PUT YOUR CUSTOM CODE IN A PACKAGE MANAGED BY A CODE REPOSITORY!!

    <<this>>declare  employee_id employees.employee_id%TYPE;begin  Insert Into Employees e (Name, Job)    values ('Matthew', 'Astronaut')  returning e.employee_id into this.employee_id;end;

    MK

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Nov 1, 2018 11:56AM Answer ✓

    This is a DB thing, not an APEX thing.

    You need to use the RETURNING clause of an INSERT statement.

    MAKE SURE YOU PUT YOUR CUSTOM CODE IN A PACKAGE MANAGED BY A CODE REPOSITORY!!

    <<this>>declare  employee_id employees.employee_id%TYPE;begin  Insert Into Employees e (Name, Job)    values ('Matthew', 'Astronaut')  returning e.employee_id into this.employee_id;end;

    MK

  • Alter Boy
    Alter Boy Member Posts: 65
    edited Nov 1, 2018 12:34PM

    Thank you. Yes I know it's a DB thing but I was just referring to the 'Return Key Into Item' in APEX. Thanks again.