Inserting Multiple Rows into Oracle

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

How do you insert multiple rows of data using SQL or PLSQL into a table in Oracle?

SQL Workshop, right? Include those commands into a begin-end block (and make them PL/SQL instead):

begin
  insert into historie ...;
  insert into historie ...;
end;
/

and then push the RUN button.

Inserting multiple rows with sequence in Oracle

The restrictions on multitable inserts include:

You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.

That isn't quite true - you can use a sequence, it just always gets the same value, so it can be useful to create parent and child records in one go by referring to the same sequence.

If you want to continue to use insert all you could work around that by using a non-deterministic function that gets the sequence value:

CREATE FUNCTION get_seq RETURN NUMBER IS
BEGIN
  RETURN postal_code_seq.nextval;
END;
/

INSERT ALL
  INTO POSTAL_CODE( postal_code,description)
    VALUES(get_seq,'Coimbatore')
  INTO POSTAL_CODE (postal_code,description)
    VALUES(get_seq,'Mumbai') SELECT * FROM DUAL;

2 rows inserted.

SELECT * FROM postal_code;

                            POSTAL_CODE DESCRIPTION        
--------------------------------------- --------------------
                                      1 Coimbatore          
                                      2 Mumbai

But that's a bit awkward. You're probably better off using individual insert statements - using a multitable insert into a single table isn't really gaining you much anyway - or a trigger to set the unique column from the sequence, or a CTE/inline view to generate the values to insert.

How to insert multiple object rows into a table in oracle sql?

how do i insert the second row of "kids" (Leanardo, Trudeau, 10).

You cannot. The kids type is defined as:

CREATE TYPE kids AS OBJECT(firstK VARCHAR(20), lastK VARCHAR(20), age INT);

and then you define the table as:

CREATE TABLE person (
  id VARCHAR(10),
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  adr address,
  pn phoneNum,
  ks kids
);

kids (despite the plural in its name) is a singular object so you can only put a single kids value into it.

If you want to put multiple kids then you either need to:

change the table to store a collection of kids;
have a separate table for children; or
reverse the relationship and store two (or more) foreign key relationships for parents.

For example, you could (since children are people too) store the relationship as:

CREATE TYPE address AS OBJECT(
  num    INT,
  street VARCHAR2(20), 
  city   VARCHAR2(20),
  zip INT
);
CREATE TYPE phoneNum AS VARRAY(2) OF VARCHAR2(13);

CREATE TABLE person (
  id            INTEGER
                CONSTRAINT person__id__pk PRIMARY KEY,
  firstName     VARCHAR2(20),
  lastName      VARCHAR2(20),
  date_of_birth DATE,
  adr           address,
  pn            phoneNum
);

CREATE TABLE children(
  id        INTEGER
            CONSTRAINT children__id__pk PRIMARY KEY,
  parent_id CONSTRAINT children__parent_id__fk REFERENCES person (id),
  child_id  CONSTRAINT children__child_id__fk  REFERENCES person (id),
  CONSTRAINT children__pid_cid__u UNIQUE (parent_id, child_id)
);

db<>fiddle here

Oracle SQL Inserting multiple rows into a table while referencing 2 FK

How about CTEs? Select ID_TEAM_FK first, use it to fetch ID_LOCATION_FK, insert the result.

INSERT INTO test (pk, id_team_fk, id_location_fk)
   WITH
      temp
      AS
      -- select random number from table
         (SELECT id_team_fk
            FROM (  SELECT id_team_FK
                      FROM team_table
                  ORDER BY DBMS_RANDOM.random)
           WHERE ROWNUM = 1),
      temp2
      AS
      -- now you know ID_TEAM_FK (you fetched it in the TEMP CTE), so - fetch ID_LOCATION_FK
         (SELECT id_team_fk, id_location_fk
            FROM (SELECT a.id_team_fk, b.id_location_FK
                    FROM team_table a CROSS JOIN temp b
                   WHERE a.id_team_FK = b.id_team_FK))
   SELECT 1, id_team_fk, id_location_fk
     FROM temp2;