Online Tech Support

This Online Tech Support page is based on examples. Operator Oracle Insert allowing to add more lines into Oracle database tables or into other Oracle objects. The most basic Oracle Insert statement syntax is:

INSERT INTO <table_name> (<columns>)
     VALUES (<values>);

For example we have table CUSTOMERS that has columns FORENAME and SURNAME. Our Online Tech Support computer technician will add into that table customer “John Lennon”. To do it the insert Oracle statement would be following:

INSERT INTO customers (forename, surname)
     VALUES ('John', 'Lennon');

The example above inserted 1 line, but the line will be lost when you log out from the Oracle database. To save the permanent to the database you need to apply COMMIT after your statement as on the following example.

INSERT INTO customers (forename, surname)
     VALUES ('John', 'Lennon');

COMMIT;

Another way to insert your lines is to take them from another Oracle table using a Select statement. To write Oracle insert using the select statement use the syntax below:

INSERT INTO <table_name> (<columns>)
      (SELECT <columns> FROM <table names> WHERE <conditions>);

Now Online Tech Support computer technician has the second example with Insert Oracle that takes lines from another Oracle table named CUSTOMERS_BACKUP and inserting them directly into the CUSTOMERS table.

INSERT INTO customers (forename, surname)
      (SELECT ocu.forename, ocu.surname FROM customers_backup ocu);

Once again to store it use the commit command.

You can get back inserted values from the target table by keyword RETURNING and this is quite handy for new ID values or other values generated during the inserting process. The next Online Tech Support example shows a forename value returned by the Oracle insert statement and we are using a bind value to output it.

VARIABLE v_forename CHAR(200);

BEGIN
  INSERT INTO users_backup (forename,surname)
       VALUES ('John','Lennon' ) 
    RETURNING forename INTO :v_forename ;
END;
/

print v_forename;

As you see in the statement above we did insert name “John Lennon” and since we asked to return only forename the :v_forename bind variable shows value John.


See Also:
Home Oracle Select