exact fetch returns more than requested rows in toad

Question

I am new to procedures and trying to create one like coded below:

CREATE OR REPLACE PROCEDURE SYSTEM.tiii IS




 v_full_name VARCHAR2(500);
  sal varchar(200);
  jobid varchar(100);
  cpct varchar(50);
  mgid varchar(25);
  did varchar(20);
  cid varchar(20);
  rid varchar(20);
  lid varchar(20);


BEGIN

select HR.EMPLOYEES.FIRST_NAME||' '||HR.EMPLOYEES.LAST_NAME AS 
 Full_Name,HR.EMPLOYEES.SALARY as sal,HR.EMPLOYEES.JOB_ID as ji,
 HR.EMPLOYEES.COMMISSION_PCT as cmpct,HR.EMPLOYEES.MANAGER_ID as 
 mgid,HR.EMPLOYEES.DEPARTMENT_ID as dep,HR.COUNTRIES_EXTERNAL.COUNTRY_ID as 
 country,HR.DW_REGION.R_ID as region,
 HR.LOCATIONS.LOCATION_ID as loc
 into v_full_name,sal,jobid,cpct,mgid,did,cid,rid,lid
 from HR.EMPLOYEES
 join HR.DEPARTMENTS ON 
 HR.EMPLOYEES.DEPARTMENT_ID=HR.DEPARTMENTS.DEPARTMENT_ID
 join HR.LOCATIONS    ON HR.DEPARTMENTS.LOCATION_ID=HR.LOCATIONS.LOCATION_ID
 join HR.COUNTRIES_EXTERNAL ON HR.LOCATIONS.COUNTRY_ID=HR.COUNTRIES_EXTERNAL.COUNTRY_ID
 join HR.DW_REGION    ON HR.COUNTRIES_EXTERNAL.REGION_ID=HR.DW_REGION.R_ID
 where HR.COUNTRIES_EXTERNAL.COUNTRY_ID='US'
 AND trunc(HR.EMPLOYEES.HIRE_DATE) BETWEEN TO_DATE('16/08/2002','DD/MM/YYYY') AND 
 TO_DATE('07/12/2007','DD/MM/YYYY');

END tiii;
/

But keep getting this error:

ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYSTEM.TIII", line 35 ORA-06512: at line 2

Please guide where is the mistake in the query?

Never, ever create your own procedures, tables or other database objects in the SYSTEM (or SYS) schema. Just don't. Create a regular user and create your procedures and tables there. Do NOT use the SYSTEM (or even worse the SYS user) for your own stuff. — a_horse_with_no_name

Daniel Frech Daniel Frech · Accepted Answer · 2018-10-10T10:33:43

As already mentioned in the comments, do not add packages to the SYSTEM or SYS schema!

Besides that, you can only fetch into variables if you receive one row only from your query. So you can either assure, that your query never returns more than one row, or you need to change on how you fetch your data. An example would be to use a record and then to bulk collect into a table of this record:

CREATE OR REPLACE PROCEDURE <<YOUR_SCHEMA>>.tiii IS
    TYPE MyResultsRec IS RECORD(
        v_full_name VARCHAR2(500),
        sal varchar(200),
        jobid varchar(100),
        cpct varchar(50),
        mgid varchar(25),
        did varchar(20),
        cid varchar(20),
        rid varchar(20),
        lid varchar(20)
    );
    TYPE MyResultsTab IS TABLE OF MyResultsRec;
    t_my_results   MyResultsTab; 
BEGIN
    SELECT HR.EMPLOYEES.FIRST_NAME||' '||HR.EMPLOYEES.LAST_NAME AS Full_Name,
           HR.EMPLOYEES.SALARY as sal,
           HR.EMPLOYEES.JOB_ID as ji,
           HR.EMPLOYEES.COMMISSION_PCT as cmpct,
           HR.EMPLOYEES.MANAGER_ID as mgid,
           HR.EMPLOYEES.DEPARTMENT_ID as dep,
           HR.COUNTRIES_EXTERNAL.COUNTRY_ID as country,
           HR.DW_REGION.R_ID as region,
           HR.LOCATIONS.LOCATION_ID as loc
      BULK COLLECT INTO t_my_results 
      FROM HR.EMPLOYEES
     INNER JOIN HR.DEPARTMENTS ON HR.EMPLOYEES.DEPARTMENT_ID=HR.DEPARTMENTS.DEPARTMENT_ID
     INNER JOIN HR.LOCATIONS ON HR.DEPARTMENTS.LOCATION_ID=HR.LOCATIONS.LOCATION_ID
     INNER JOIN HR.COUNTRIES_EXTERNAL ON HR.LOCATIONS.COUNTRY_ID=HR.COUNTRIES_EXTERNAL.COUNTRY_ID
     INNER JOIN HR.DW_REGION    ON HR.COUNTRIES_EXTERNAL.REGION_ID=HR.DW_REGION.R_ID
     WHERE HR.COUNTRIES_EXTERNAL.COUNTRY_ID='US'
       AND trunc(HR.EMPLOYEES.HIRE_DATE) BETWEEN TO_DATE('16/08/2002','DD/MM/YYYY') 
       AND TO_DATE('07/12/2007','DD/MM/YYYY');
END tiii;
/

Another would be to create a cursor and then loop over the results:

CREATE OR REPLACE PROCEDURE <<YOUR_SCHEMA>>.tiii IS
BEGIN
    FOR r_results IN (
        SELECT HR.EMPLOYEES.FIRST_NAME||' '||HR.EMPLOYEES.LAST_NAME AS Full_Name,
               HR.EMPLOYEES.SALARY as sal,
               HR.EMPLOYEES.JOB_ID as ji,
               HR.EMPLOYEES.COMMISSION_PCT as cmpct,
               HR.EMPLOYEES.MANAGER_ID as mgid,
               HR.EMPLOYEES.DEPARTMENT_ID as dep,
               HR.COUNTRIES_EXTERNAL.COUNTRY_ID as country,
               HR.DW_REGION.R_ID as region,
               HR.LOCATIONS.LOCATION_ID as loc
          FROM HR.EMPLOYEES
         INNER JOIN HR.DEPARTMENTS ON HR.EMPLOYEES.DEPARTMENT_ID=HR.DEPARTMENTS.DEPARTMENT_ID
         INNER JOIN HR.LOCATIONS ON HR.DEPARTMENTS.LOCATION_ID=HR.LOCATIONS.LOCATION_ID
         INNER JOIN HR.COUNTRIES_EXTERNAL ON HR.LOCATIONS.COUNTRY_ID=HR.COUNTRIES_EXTERNAL.COUNTRY_ID
         INNER JOIN HR.DW_REGION    ON HR.COUNTRIES_EXTERNAL.REGION_ID=HR.DW_REGION.R_ID
         WHERE HR.COUNTRIES_EXTERNAL.COUNTRY_ID='US'
           AND trunc(HR.EMPLOYEES.HIRE_DATE) BETWEEN TO_DATE('16/08/2002','DD/MM/YYYY') 
           AND TO_DATE('07/12/2007','DD/MM/YYYY')
    LOOP
        -- do something here
        -- access your results like r_results.full_name
    END LOOP;
END tiii;
/

There are other options as well, such as using a cursor, but this should hopefully give you an idea on how to proceed.

exact fetch returns more than requested rows in toad

1 Answers