Skip to main content
PHP Blog

Back to all posts

How to Return Id After Inserting Into Oracle?

Published on
3 min read
How to Return Id After Inserting Into Oracle? image

Best Database Tools to Buy in October 2025

1 Database Systems: Design, Implementation, & Management

Database Systems: Design, Implementation, & Management

BUY & SAVE
$147.66 $259.95
Save 43%
Database Systems: Design, Implementation, & Management
2 Database Systems: Design, Implementation, & Management

Database Systems: Design, Implementation, & Management

BUY & SAVE
$86.49
Database Systems: Design, Implementation, & Management
3 Database Systems: Design, Implementation, & Management

Database Systems: Design, Implementation, & Management

BUY & SAVE
$86.49
Database Systems: Design, Implementation, & Management
4 Concepts of Database Management (MindTap Course List)

Concepts of Database Management (MindTap Course List)

BUY & SAVE
$54.86 $193.95
Save 72%
Concepts of Database Management (MindTap Course List)
5 Statistics: A Tool for Social Research and Data Analysis (MindTap Course List)

Statistics: A Tool for Social Research and Data Analysis (MindTap Course List)

BUY & SAVE
$118.60 $259.95
Save 54%
Statistics: A Tool for Social Research and Data Analysis (MindTap Course List)
6 Bioinformatics for Beginners: Genes, Genomes, Molecular Evolution, Databases and Analytical Tools

Bioinformatics for Beginners: Genes, Genomes, Molecular Evolution, Databases and Analytical Tools

BUY & SAVE
$44.96 $59.95
Save 25%
Bioinformatics for Beginners: Genes, Genomes, Molecular Evolution, Databases and Analytical Tools
7 The Manga Guide to Databases (The Manga Guides)

The Manga Guide to Databases (The Manga Guides)

BUY & SAVE
$19.95 $24.99
Save 20%
The Manga Guide to Databases (The Manga Guides)
8 Data Mining: Practical Machine Learning Tools and Techniques (Morgan Kaufmann Series in Data Management Systems)

Data Mining: Practical Machine Learning Tools and Techniques (Morgan Kaufmann Series in Data Management Systems)

  • EXPERIENCE THE LATEST INNOVATIONS WITH OUR NEW PRODUCT LINE TODAY!
  • UNMATCHED QUALITY AND PERFORMANCE - TRY OUR NEW FEATURES NOW!
  • BE THE FIRST TO ENJOY EXCLUSIVE BENEFITS FROM OUR NEW OFFERINGS!
BUY & SAVE
$54.94 $69.95
Save 21%
Data Mining: Practical Machine Learning Tools and Techniques (Morgan Kaufmann Series in Data Management Systems)
9 Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

BUY & SAVE
$74.00
Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design
+
ONE MORE?

To return the ID after inserting into Oracle, you can use the RETURNING clause in your SQL insert statement. This clause allows you to retrieve the value of the ID column immediately after inserting a new row into the database.

Here’s an example of how you can use the RETURNING clause:

INSERT INTO table_name (column1, column2) VALUES (value1, value2) RETURNING id_column INTO :out_id;

In this example, you would replace "table_name", "column1", "column2", "value1", "value2", and "id_column" with your actual table and column names. The ":out_id" placeholder is used to capture the returned ID value.

After executing the insert statement, you can fetch the returned ID value from the ":out_id" variable to use it in your application logic. This allows you to easily retrieve the generated ID after inserting a new record into the Oracle database.

How to retrieve the primary key value generated during an insert in Oracle SQL?

In Oracle SQL, you can retrieve the primary key value generated during an insert operation by using the RETURNING INTO clause in your INSERT statement. Here is an example:

DECLARE new_id NUMBER; BEGIN INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2') RETURNING primary_key_column INTO new_id;

DBMS_OUTPUT.PUT_LINE('Inserted row with primary key: ' || new_id); END;

In this example, your_table is the name of the table where you are inserting the data, primary_key_column is the name of the primary key column in that table, and new_id is a variable used to store the generated primary key value.

After executing the INSERT statement, the generated primary key value will be stored in the new_id variable, which you can then use for further processing or display.

What is the PL/SQL code for fetching the generated ID after inserting into an Oracle table?

There are a few ways to fetch the generated ID after inserting into an Oracle table. One common method is to use the RETURNING INTO clause along with the INSERT statement. Here is an example of how to do this in PL/SQL:

DECLARE v_id NUMBER; BEGIN INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2') RETURNING id_column INTO v_id;

DBMS_OUTPUT.PUT_LINE('Generated ID: ' || v_id); END; /

In this code snippet, your_table is the name of the table you are inserting into, column1 and column2 are the columns being inserted into, and id_column is the column that contains the generated ID (replace these with the actual column names in your table).

After the INSERT statement, the RETURNING id_column INTO v_id clause retrieves the generated ID from the newly inserted row and stores it in the v_id variable. You can then use this variable to access the generated ID as needed.

What is the query to retrieve the unique identifier of the newly inserted record in Oracle?

To retrieve the unique identifier of the newly inserted record in Oracle, you can use the RETURNING clause in your INSERT statement.

Here's an example query:

INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2') RETURNING your_unique_identifier_column INTO :out_variable;

In this query:

  • your_table_name is the name of the table where you are inserting the new record
  • column1, column2 are the columns you are inserting values into
  • 'value1', 'value2' are the values you are inserting into the columns
  • your_unique_identifier_column is the unique identifier column of the table
  • :out_variable is the variable where the unique identifier value will be stored after the INSERT operation is executed.

After executing this query, you can retrieve the value of :out_variable to get the unique identifier of the newly inserted record.