Is There an Oracle Equivalent to SQL Server's Output Inserted.*

Oracle equivalent of PostgreSQL INSERT...RETURNING *;

It's not currently possible, especially in an old version of Oracle such as 8i. See this answer to a similar question.

Oracle SQL inserting multiple rows and returning something

From the documentation (at least through the 21c version), one of the restrictions to the returning clause:

You cannot specify the
returning_clause for a multitable
insert.

Copying table values with dependent child records

The usual way to insert a row and get the generated sequence value in a single statement is to use the RETURNING INTO clause. Unfortunately, this clause is not supported with the multi-row INSERT SELECT statement (as of 11.2.0.2.0).

So you will have to either:

• use a cursor and insert in the parent table row-by-row (then use one multi-row insert in the child table for each row in the parent table).

  For instance, in your case:

  DECLARE
     l_key aparent.aparent_code%TYPE;
  BEGIN
     FOR cc IN (SELECT a.aparent_code, a.value1, a.value2 
                  FROM aparent a 
                 WHERE a.avalue1 = 10) LOOP
        INSERT INTO aparent 
           VALUES (sq_aparent.nextval, cc.value1, cc.value2) 
           RETURNING aparent_code INTO l_key; -- get the new parent key
        INSERT INTO achild 
           (SELECT sq_achild.nextval, l_key, value3 -- use the new parent key
              FROM achild 
             WHERE aparent_code = cc.aparent_code);
     END LOOP;
  END;
  

• use multi-row inserts with a workaround as you did.

I think your approach may be the most efficient generally, especially if many parent rows are being copied. The overhead of inserting into the temporary table and joining to it should be minimal compared to the large overhead incurred by many single-row statements.

View Top N-Results in Oracle

Is this what you want?

select *
from (select product_name, (qty_on_order - qty_on_hand) as Sales 
      from products join
           product_inventory
           on product_inventory.product_id = products.product_id
      order by Sales Desc
     ) p
where rownum <= 3;

---

Related Topics