PL/SQL ORA-01422 Error for SELECT INTO, Oracle Anonymous Block (NOVA Environment)

Question

Getting the following Error: ORA-01422: exact fetch returns more than requested number of rows ORA-6512

New to PL/SQL and although I KNOW this query should be conducted via standard SQL, I am trying to figure out how to accomplish this using PL/SQL.

I need the query to return the total number of SALES for customers with zip code '20636'

Here are the tables/input:

CREATE TABLE CUSTOMERS
(customerID     INT     PRIMARY KEY,
customerZip     VARCHAR(15) NOT NULL); 

CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
customerID      INT,
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID));

INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (1, '20636');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (2, '20619');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (3, '20650');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (4, '20670');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (5, '20636');

INSERT INTO SALES (saleID, customerID) VALUES (1, 1);
INSERT INTO SALES (saleID, customerID) VALUES (2, 2);
INSERT INTO SALES (saleID, customerID) VALUES (3, 3);
INSERT INTO SALES (saleID, customerID) VALUES (4, 4);
INSERT INTO SALES (saleID, customerID) VALUES (5, 5);

Here's the code that I have written for the PL/SQL anonymous block:

DECLARE
customerZip INTEGER;
totalSales INTEGER;
BEGIN
SELECT customerID INTO customerZip from CUSTOMERS where customerZip = '20636';
SELECT COUNT(*) INTO totalSales from SALES where customerID = customerZip;
DBMS_OUTPUT.put_line('We sold ' || totalSales || ' Cars to customers in Zip Code ' || customerZip ||'.');
END;
/

If I run this without having two customerZIP attributes set at '20636', it works fine. As soon as I enter more than one customer record with the zip code '20636', I get the error.

Can you please explain what I'm doing wrong here and how I can fix it? Thank you!

SQL Fiddle link if it helps: http://sqlfiddle.com/#!4/10fc1

This: SELECT customerID INTO customerZip ... is returning more than 1 customerID . To fix, you could bulk collect into some collection, or use an analytic like row_number to pick just 1 customerID. — tbone, Feb 07 '18 at 18:55
Exactly, because two of the customer ID's (1 and 5) based on the data I provided have the zip code 20636. How can I revise the code so it will select multiple customerID's? In other words, since multiple customers from my table live in 20636, it will need to account for each of them. I'm guessing a loop of sorts? — StevenC, Feb 07 '18 at 18:57
You could turn that select into a cursor and loop through them (or use a collection like nested tables). Or, in this case, simply do a GROUP BY query (select zip, sum(whatever), count(*) from my_table where ... group by zip) — tbone, Feb 07 '18 at 19:00

score 2 · Accepted Answer · answered Feb 07 '18 at 19:36

You may use such a statement :

SQL> set serveroutput on;
SQL> DECLARE
  v_customerZip CUSTOMERS.customerZip%type:='20636';
  v_customerID  CUSTOMERS.customerID%type;
  totalSales    INTEGER:=0;
  Sales         INTEGER;
BEGIN
 for c in ( SELECT customerID from CUSTOMERS where customerZip = v_customerZip )
 loop
  v_customerID := c.customerID;
  SELECT COUNT(1) INTO Sales from SALES where customerID = v_customerID;
  totalSales := totalSales + Sales;
 end loop; 
  dbms_output.put_line('We sold ' || totalSales || ' Cars to customers in Zip Code ' || v_customerZip ||'.');
END;
/
We sold 2 Cars to customers in Zip Code 20636.

and change variable v_customerZip's value whatever you wish from declaration part.

Or you may create a procedure to accomplish this task :

SQL> CREATE OR REPLACE PROCEDURE GET_SALES( v_customerZip CUSTOMERS.customerZip%type ) IS
  v_customerID  CUSTOMERS.customerID%type;
  totalSales    INTEGER:=0;
  Sales         INTEGER;
BEGIN
 for c in ( SELECT customerID from CUSTOMERS where customerZip = v_customerZip )
 loop
  v_customerID := c.customerID;
  SELECT COUNT(1) INTO Sales from SALES where customerID = v_customerID;
  totalSales := totalSales + Sales;
 end loop; 
  dbms_output.put_line('We sold ' || totalSales || ' Cars to customers in Zip Code ' || v_customerZip ||'.');
END;
/
SQL> exec get_sales('&cZ');
Enter value for cz: 20636
We sold 2 Cars to customers in Zip Code 20636.

Did the trick. Thanks! Great info...hopefully this will start coming to me... SQL is easy compared to PL/SQL. — StevenC, Feb 08 '18 at 00:58

PL/SQL ORA-01422 Error for SELECT INTO, Oracle Anonymous Block (NOVA Environment)

1 Answers1