Too many values inside a Where-CodePudding

I want to get all products inside an order. I give to my procedure the ID of the order and then I want to list all of them. Here is the database scheme of the tables I use in this procedure:

And here is the procedure:

CREATE OR REPLACE PROCEDURE exercitiu6(v_ID_Comanda Comanda.ID_Comanda%TYPE) AS
    TYPE produse IS TABLE OF Produs%ROWTYPE INDEX BY PLS_INTEGER;
    p produse;
    TYPE imbricat IS TABLE OF ProduseComanda.ID_Produs%TYPE;
    imbricat_produse imbricat:= imbricat();
    prod Produs%ROWTYPE;
    i number:=0;
    j number:=0;
 BEGIN 
    SELECT ID_Produs BULK COLLECT INTO imbricat_produse FROM ProduseComanda
    WHERE ID_Comanda = v_ID_Comanda;
    FOR i IN imbricat_produse.FIRST..imbricat_produse.LAST LOOP
        SELECT ID_Produs, nume, pret INTO prod FROM Produs
        WHERE ID_Produs = imbricat_produse(i);
        p(j):= prod;
        j:= j   1; 
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Comanda cu ID-ul ' || v_ID_Comanda || ' contine urmatoarele produse: ');
    FOR j IN p.FIRST..p.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(p(j).nume);
    END LOOP;
END;

I get the Error Sql Statement ignored; too many values on this line:

WHERE ID_Comanda = v_ID_Comanda;

How do I solve this error?

CodePudding user response：

Use a JOIN and a single cursor:

CREATE OR REPLACE PROCEDURE exercitiu6(
  v_ID_Comanda ProduseComanda.ID_Comanda%TYPE
)
AS
BEGIN 
   DBMS_OUTPUT.PUT_LINE('Comanda cu ID-ul ' || v_ID_Comanda || ' contine urmatoarele produse: ');

   FOR i IN (
     SELECT p.nume
     FROM   ProduseComanda c
            INNER JOIN Produs p
            ON p.ID_Produs = c.ID_Produs
     WHERE  c.ID_Comanda = v_ID_Comanda
   )
   LOOP
     DBMS_OUTPUT.PUT_LINE(i.nume);
   END LOOP;
END;
/

Then, for the sample data:

CREATE TABLE produsecomanda (ID_Produs, ID_Comanda) AS
  SELECT 1, 1 FROM DUAL UNION ALL
  SELECT 2, 1 FROM DUAL UNION ALL
  SELECT 3, 1 FROM DUAL;

CREATE TABLE produs (ID_Produs, nume) AS
  SELECT 1, 'Alice' FROM DUAL UNION ALL
  SELECT 2, 'Beryl' FROM DUAL UNION ALL
  SELECT 3, 'Carol' FROM DUAL;

Then:

BEGIN
  DBMS_OUTPUT.ENABLE();
  exercitiu6(1);
END;
/

Outputs:

Comanda cu ID-ul 1 contine urmatoarele produse: 
Alice
Beryl
Carol

If you want to fix your code then the error is not with the WHERE clause but with the mismatch between the number of columns in the SELECT clause and the INTO clause. To fix it you need to use SELECT * INTO ... rather than naming all the columns when you are working with %ROWTYPE variables:

CREATE OR REPLACE PROCEDURE exercitiu6(
  v_ID_Comanda ProduseComanda.ID_Comanda%TYPE
)
AS
    TYPE produse IS TABLE OF Produs%ROWTYPE INDEX BY PLS_INTEGER;
    p produse;
    TYPE imbricat IS TABLE OF ProduseComanda.ID_Produs%TYPE;
    imbricat_produse imbricat;
    i number:=0;
    j number:=0;
 BEGIN 
    SELECT ID_Produs
    BULK COLLECT INTO imbricat_produse
    FROM   ProduseComanda
    WHERE  ID_Comanda = v_ID_Comanda;

    FOR i IN imbricat_produse.FIRST..imbricat_produse.LAST LOOP
        j:= j   1;
        SELECT *
        INTO   p(j)
        FROM   Produs
        WHERE  ID_Produs = imbricat_produse(i);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Comanda cu ID-ul ' || v_ID_Comanda || ' contine urmatoarele produse: ');
    FOR j IN p.FIRST..p.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(p(j).nume);
    END LOOP;
END;
/

fiddle