multiple rows insert in plsql ora 01427

Question

https://i.stack.imgur.com/t2tMi.jpg

SELECT T.USER_ID
  FROM USER_OTHER_PRIVILEGES T
 WHERE T.UOPM_ID = 17
   AND T.PRIV_ID IN (719) ;  67 rows selected

INSERT INTO  USER_OTHER_PRIVILEGES (
  ID, PRIV_ID,UOPM_ID,USER_ID,ML_ID,PARENT_ID
) VALUES (
  PRIV_USER_OTH_ID_SEQ.NEXTVAL,
  1792,
  17,
  ( SELECT T.USER_ID
    FROM   USER_OTHER_PRIVILEGES T
           JOIN USERS U
           ON U.ID = T.USER_ID
    WHERE  T.UOPM_ID = 17
    AND    T.PRIV_ID IN (719) ),
  NULL,
  1
)

do you want to insert 67 rows corresponding to your first query into the user_other_privileges table along with sequence,1792,17 ? — cableload, Apr 24 '16 at 03:08
You post a `select` statement that you say returns 67 rows. Your `insert` statement contains a similar statement that joins to an additional table `users` but it is not obvious what that join would accomplish. Either it would do nothing because you aren't referencing `users` in a predicate or in the projection or it would change the number of rows returned. — Justin Cave, Apr 24 '16 at 04:53

score 1 · Answer 1 · edited May 23 '17 at 12:23

The error means, that this subquery:

SELECT T.USER_ID
    FROM   USER_OTHER_PRIVILEGES T
           JOIN USERS U
           ON U.ID = T.USER_ID
    WHERE  T.UOPM_ID = 17
    AND    T.PRIV_ID IN (719)

returns more that one row.
Please run this query and you will see that at least 2 user ids will appear.

The INSERT statements with the VALUE clause can insert only one row into the table, and it expects only one value for each column in the VALUES clause:

INSERT INTO table( col1, col2, col3 ) VALUES ( val1, val2, val3 )

You can't enter many values to one column in the row.

For example if this subquery returns three numbers: 1,2,3, you cannot insert one row in such a way:

+------+---------+---------+---------+-------+-----------+
| ID   | PRIV_ID | UOPM_ID | USER_ID | ML_ID | PARENT_ID | 
+------+---------+---------+---------+-------+-----------+
| 456  |    1792 |      17 |  1,2,3  |  NULL |         1 |
+------+---------+---------+---------+-------+-----------+

I guess that you don't want to insert only one row, but you are going to insert one separate row for each entry returned by the suquery, like this:

+------+---------+---------+---------+-------+-----------+
| ID   | PRIV_ID | UOPM_ID | USER_ID | ML_ID | PARENT_ID | 
+------+---------+---------+---------+-------+-----------+
| 456  |    1792 |      17 |      1  |  NULL |         1 |
+------+---------+---------+---------+-------+-----------+
| 457  |    1792 |      17 |      2  |  NULL |         1 |
+------+---------+---------+---------+-------+-----------+
| 458  |    1792 |      17 |      3  |  NULL |         1 |
+------+---------+---------+---------+-------+-----------+

In this case you can't use INSERT INTO ... VALUES ... syntax,
you need INSERT INTO .... subquery variant instead,
see this answer for more details: How can I insert values into a table, using a subquery with more than one result?

Your insert statement for such a case can be:

INSERT INTO  USER_OTHER_PRIVILEGES (
  ID, PRIV_ID,UOPM_ID,USER_ID,ML_ID,PARENT_ID
) 
SELECT
     PRIV_USER_OTH_ID_SEQ.NEXTVAL,
     1792,
     17,
     T.USER_ID,
     NULL,
     1
FROM USER_OTHER_PRIVILEGES T
JOIN USERS U
ON U.ID = T.USER_ID
WHERE  T.UOPM_ID = 17
  AND  T.PRIV_ID IN (719)

score 0 · Answer 2 · answered Apr 24 '16 at 09:27

Answer, thanks :)

DECLARE

v_USER_ID INTEGER;


BEGIN
  FOR XYZ IN 
              (
                SELECT T.USER_ID
                FROM   USER_OTHER_PRIVILEGES T
                JOIN USERS U
                ON U.ID = T.USER_ID
                WHERE  T.UOPM_ID = 17
                AND    T.PRIV_ID IN (719)
              )
  LOOP
  v_USER_ID:= XYZ.USER_ID;

                INSERT INTO  USER_OTHER_PRIVILEGES
                (ID, PRIV_ID,UOPM_ID,USER_ID,ML_ID,PARENT_ID)
                VALUES
                (PRIV_USER_OTH_ID_SEQ.NEXTVAL,1792, 17,v_USER_ID, NULL,1);

                COMMIT;

  END LOOP;

END;

multiple rows insert in plsql ora 01427

2 Answers2