Exact fetch returns more than requested number of rows error while inserting value to the table-CodePudding

I'm working on PL/SQL that inserts the value into the table with increment by using a sequence and a loop. There are 830 rows need to insert the values. However, it keeps says error ORA-01422: exact fetch returns more than the requested number of rows at line 11. The type of variable that causes the error is the date. And all the date values in the row of the table should be the same as the system date. Which part should I fix to let it insert the system date in all rows?

CREATE SEQUENCE ORDERID_SEQ
START WITH 11080
INCREMENT BY 1
NOCACHE;

DECLARE
LOOPCOUNT NUMBER :=1;
CUSTOMER ORDERS.CUSTOMERID%type;
EMPLOYEE ORDERS.EMPLOYEEID%type;
TERRITORY ORDERS.TERRITORYID%type;
ORDERINGDATE ORDERS.ORDERDATE%type;

BEGIN
LOOP
ORDERINGDATE := SYSDATE;
SELECT ORDERID_SEQ.NEXTVAL INTO CUSTOMER FROM ORDERS;
INSERT INTO ORDERS (CUSTOMERID) VALUES (CUSTOMER);
SELECT ORDERID_SEQ.NEXTVAL INTO EMPLOYEE FROM ORDERS;
INSERT INTO ORDERS (CUSTOMERID) VALUES (EMPLOYEE);
SELECT ORDERID_SEQ.NEXTVAL INTO TERRITORY FROM ORDERS;
INSERT INTO ORDERS (CUSTOMERID) VALUES (TERRITORY);
SELECT ORDERINGDATE INTO ORDERINGDATE FROM ORDERS;
INSERT INTO ORDERS (ORDERDATE) VALUES (ORDERINGDATE);
LOOPCOUNT := LOOPCOUNT 1;
EXIT WHEN LOOPCOUNT >= 830;
END LOOP;
END;

CodePudding user response：

PL/SQL will not permit a SELECT statement that returns more than one row (if you need that, you have to use a cursor). Because you are selecting FROM ORDERS, each of those statements will return as many rows as there currently are in the ORDERS table. Instead try:

SELECT ORDERID_SEQ.NEXTVAL INTO CUSTOMER FROM DUAL;

and so on. And delete the final SELECT (of ORDERINGDATE). You don't need to select it from the database when you've already assigned it a value.

CodePudding user response：

Hm, code you wrote doesn't make sense at all.

If you want to insert rows into the ORDERS table, then you should insert the whole row at once. Using as many INSERT statements as there are columns would create a row which has just one column with value and the rest would be empty. Note that it would fail as soon as there's a NOT NULL column which is left empty.

Other than that, select with no where clause will return all rows from the ORDERS table (hence ORA-01422, too_many_rows you got).

Why are you selecting the sequence value into some columns (customer, employee, territory) and orderingdate separately? Are you really supposed to put a sequence value into those columns? Setting orderingdate to sysdate doesn't mean much as you're then selecting orderingdate (I presume that's a column name in orders table) into a variable with the same name.

Note that you shouldn't name variables the same as column names in the table. Use a prefix, e.g. v_orderingdate.

Saying that you have to insert 830 rows: why 830? You're selecting from ORDERS and inserting into ORDERS. Not that it isn't possible, but - what exactly are you doing? Instead of a loop, consider something like a single insert statement at SQL (i.e. not PL/SQL) level (I'm guessing column names):

INSERT INTO orders (customerid,
                    employee,
                    territory,
                    orderingdate)
   SELECT orderid_seq.NEXTVAL,
          employee,
          territory,
          SYSDATE
     FROM some_other_table;