Plsql Insert into with Subquery and Returning Clause

PLSQL Insert into with subquery and returning clause

You cannot use the RETURNING BULK COLLECT from an INSERT.
This methodology can work with updates and deletes howeveer:

create table test2(aa number)
/
insert into test2(aa)
      select level
        from dual
        connect by level<100
/        

set serveroutput on
declare 
     TYPE t_Numbers IS TABLE OF test2.aa%TYPE
        INDEX BY BINARY_INTEGER;
      v_Numbers t_Numbers;
      v_count number;
begin


update test2
  set aa = aa+1
returning aa bulk collect into v_Numbers;

    for v_count in 1..v_Numbers.count loop
        dbms_output.put_line('v_Numbers := ' || v_Numbers(v_count));
    end loop;

end;

You can get it to work with a few extra steps (doing a FORALL INSERT utilizing TREAT)
as described in this article:

returning with insert..select

T

to utilize the example they create and apply it to test2 test table

 CREATE or replace TYPE ot AS OBJECT
    ( aa number);
/


CREATE TYPE ntt AS TABLE OF ot;
/

set serveroutput on
 DECLARE

       nt_passed_in ntt;
       nt_to_return ntt;

       FUNCTION pretend_parameter RETURN ntt IS
          nt ntt;
       BEGIN
          SELECT ot(level) BULK COLLECT INTO nt
         FROM   dual
         CONNECT BY level <= 5;
         RETURN nt;
      END pretend_parameter;

   BEGIN

      nt_passed_in := pretend_parameter();

      FORALL i IN 1 .. nt_passed_in.COUNT
         INSERT INTO test2(aa)
         VALUES
         ( TREAT(nt_passed_in(i) AS ot).aa
         )
         RETURNING ot(aa)
         BULK COLLECT INTO nt_to_return;

      FOR i IN 1 .. nt_to_return.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE(
            'Sequence value = [' || TO_CHAR(nt_to_return(i).aa) || ']'
            );
      END LOOP;

   END;
   /

Write an insert statement with select clause returning id in oracle

I use Cursor to resolve the issue.

SELECT inside in INSERT INTO in Oracle

The error is coming from the ORDER BY IdVenta DESC, which isn't expected at that point in the statement. If you use a subquery in the values clause then it has to return a single value, and ordering a single value doesn't make much sense. (It's a more general rule than that, though in some places it's allowed but ignored).

Since you seem to have the ordering for the ROWNUM, you need another level of subquery anyway:

INSERT INTO DETALLEVENTA(Idventa, IdProducto, Cantidad, Precio_Uni, Descuento)
VALUES((
  SELECT IdVenta 
  FROM (
    SELECT IdVenta 
    FROM Venta
    ORDER BY IdVenta DESC
  )
  WHERE ROWNUM = 1
  ), pIdProducto, pCantidad, pPrecio_Uni, pDescuento);

Ignoring that it's in a values clause for now, if you just did:

SELECT IdVenta 
FROM Venta
WHERE ROWNUM = 1 
ORDER BY IdVenta DESC

then the rownum filter is applied first, and you then order that single row, which is pointless. The row you actually get is indeterminate - it depends on how the optimiser executes the query, and can vary even between calls of the same query.

What you actually want is:

SELECT IdVenta 
FROM (
  SELECT IdVenta 
  FROM Venta
  ORDER BY IdVenta DESC
)
WHERE ROWNUM = 1

where the overall result set is ordered first, and then in the outer query the rownum filter is applied. Doing it like that means you will get the IdVenta you expect, the one that orders last.

So, you need to embed that same subquery into the values clause, as its own subquery.

There are probably better ways of doing this; you seem to be running this inside a PL/SQL block so you coudl do that query once and store in a variable, for instance. From 12c there are other ways to get the 'top' row as well. Or you can skip the subquery, and the values clause, by changing to an insert .. select as @Barbaros showed.

How to add local variable to PL/SQL INSERT INTO using subquery

It can be added to the SELECT statement (as a constant in the SELECT):

DECLARE

   l_filename   VARCHAR2 (1024);
   v_aff_event_fk NUMBER;

BEGIN
   l_filename := 'some value';
   v_aff_event_fk := 9;

     INSERT INTO aff_attach
        (file_id, file_name, mime_type, attachment, file_size, AFF_EVENT_FKEY)
        SELECT ID, l_filename, mime_type, blob_content,DOC_SIZE, v_aff_event_fk
          FROM apex_application_files
          WHERE filename = l_filename AND created_by = :app_user;
END:

Insert sql statement with a subquery and sequence

You can just put the sequence into your select, easy peasy. (edit, whups, sorry, missed that you were grouping)

INSERT INTO data_plan_demand(data_demand_id, data_plan_name,product_demand,data_plan_inf)
select seq_data_demand_id2.nextval, ss.name, ss.product_demands, ss.dataplan_information
from(SELECT d.name, COUNT(u.data_id) AS product_demands, 
      d.information AS dataplan_information
      FROM users u, data_plans d
      WHERE u.data_id = d.data_plan_id
      GROUP BY d.name,d.information) ss;

INSERT returning IDENTITY generating ORA 00933

I completely agree with Luka Eder's answer, but if you must use a single query with a return clause, it would look like this:

variable identity number;
BEGIN
INSERT INTO blatchildren
  (blatranscriptid, childactivityid, enrollmentDate, enrollmentStatus)
  VALUES ((SELECT 2 from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)),
          (SELECT 'cours000000000004981' from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)), 
          (SELECT to_date('1/1/2015 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)), 
          (SELECT 'E'  from dual where 'cours000000000004981' not in (select childactivityid from blatchildren)))
   returning blatranscriptid
    into :identity;
END;
/

The logic here, is, as Luka said, you must use a VALUES clause to get RETURNING to work. Notice the number of parenthesee I've put in there. Doing a select inside a values clause is possible, I've personally never written something this way, I believe I would just query the database twice.

p.s. this doesn't solve much, you'd still have an insert attempt.
But maybe it saves adding another constraint, probably you have a not null constraint on an id column.

---

Related Topics