exact fetch returns more than requested number of rows in a triger execution

Question

I am creating a trigger for auto_generating prod_id from prod_summery
the given table is

PROD_ID CHAR(8 BYTE)    No      1   
PRODUCT_TITLE   VARCHAR2(30 BYTE)   No      2   
PRODUCT_SUMMERY VARCHAR2(150 BYTE)  No      3   
INTEREST_TYPE   CHAR(1 BYTE)    No      4   
INTEREST_RATE   NUMBER(4,2) No      5   
SECURITY_REQD   CHAR(1 BYTE)    No      6   
MIN_LOAN_AMT    NUMBER(11,2)    No      7   
MAX_LOAN_AMT    NUMBER(11,2)    No      8   
TERM_MIN    INTERVAL YEAR(2) TO MONTH   No      9   
TERM_MAX    INTERVAL YEAR(2) TO MONTH   No      10  
REPAYMENT_FREQUENCY INTERVAL YEAR(2) TO MONTH   No      11  
REPAYMENT_AMT   NUMBER(11,2)    No      12  
EARLY_REPAY_ALLOWED CHAR(1 BYTE)    No      13  
MIN_AGE_LIMIT   NUMBER(2,0) No      14  
MAX_AGE_LIMIT   NUMBER(2,0) No      15  
RESIDENT    RESIDENT_VARRAY No      16  
PROD_START_DT   DATE    Yes     17  
PROD_END_DT DATE    Yes     18  
PROD_STATUS CHAR(1 BYTE)    No      19

Trigger

create or replace TRIGGER LOAN_PROD_ID_TR1 
    BEFORE INSERT ON LOAN_PROD_TAB
   for each row
    DECLARE
      v_length number(2):=LENGTH(:NEW.PRODUCT_SUMMERY)-
         LENGTH(REPLACE(:NEW.PRODUCT_SUMMERY,' ',' '))+1;
         V_W2 VARCHAR2(10);
        V_W3 VARCHAR2(10);
     V_W4 VARCHAR2(10);
      V_W5 VARCHAR2(10);
V_W6 VARCHAR2(10);
 V_CON VARCHAR2(10);
V_CON1 VARCHAR2(10);
V_CON2 VARCHAR2(10);
BEGIN
 IF v_length=1 THEN
 SELECT 'CBIS'||LPAD(:NEW.PRODUCT_SUMMERY,4) INTO :NEW.PROD_ID FROM 
 LOAN_PROD_TAB;
  ELSIF v_length=2 THEN
  V_W2:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)(\s)',1,2),1,2);
  V_W3:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)'),1,2);
    SELECT 'CBIS'||LPAD(V_W2,4,V_W3) INTO :NEW.PROD_ID FROM LOAN_PROD_TAB;
    ELSIF v_length=3 THEN
  V_W2:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)($)'),1,1);
   V_W3:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)(\s)',1,2),1,2);  
   V_W4:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)'),1,2);
    V_CON:=CONCAT(V_W3,V_W2);
   SELECT 'CBIS'||LPAD(V_W4,4,V_CON) INTO :NEW.PROD_ID FROM LOAN_PROD_TAB;
   ELSIF v_length=4 THEN
    V_W2:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)'),1,1);
     V_W3:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)(\s)',1,2),1,1);
   V_W4:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)(\s)',1,3),1,1);
   V_W5:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)($)'),1,1);
    V_CON:=CONCAT(V_W2,V_W3);
      V_CON1:=CONCAT(V_W4,V_W5);
    V_CON2:=CONCAT(V_CON,V_CON1);
    SELECT 'CBIS'||LPAD(V_CON2,4) INTO :NEW.PROD_ID FROM LOAN_PROD_TAB;
        ELSE
        V_W2:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)'),1,1);
         V_W3:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)(\s)',1,2),1,1);
         V_W4:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)(\s)',1,3),1,1);
      V_W5:=SUBSTR(REGEXP_SUBSTR(:NEW.PRODUCT_SUMMERY,'(\S*)(\s)',1,4),1,1);
      V_CON:=CONCAT(V_W2,V_W3);
    V_CON1:=CONCAT(V_W4,V_W5);
    V_CON2:=CONCAT(V_CON,V_CON1);
         SELECT 'CBIS'||LPAD(V_CON2,4) INTO :NEW.PROD_ID FROM LOAN_PROD_TAB;
         END  IF;
     END;

The code would be easier to follow and debug if you applied some formatting rather than just placing random amounts of whitespace in front of each line, and used lowercase. — William Robertson, Jan 20 '18 at 10:43
By the way, the standard string type in Oracle is `VARCHAR2`. You should only use `CHAR` if you have a specific business requirement to store trailing blank spaces for strings shorter than the maximum length, and I have never seen this requirement ever. — William Robertson, Jan 21 '18 at 15:13

score 4 · Answer 1 · answered Jan 19 '18 at 06:52

your error is caused by the select statements, e.g.

SELECT 'CBIS'||LPAD(:NEW.PRODUCT_SUMMERY,4) INTO :NEW.PROD_ID
FROM LOAN_PROD_TAB;

if there is more than one record in the table you will get an error as you are selecting everything from the table.

You shouldn't need to do a select here, you should be able to just do

:NEW.PROD := 'CBIS'||LPAD(:NEW.PRODUCT_SUMMERY,4);

score 1 · Answer 2 · answered Jan 19 '18 at 06:53

TOO-MANY-ROWS is returned by one of SELECT statements. Basically, no wonder - none of them contains the WHERE clause.

On the other hand, I think that you shouldn't use SELECT at all; trigger is on LOAN_PROD_TAB, and you're selecting from it - that leads to a mutating table error. Instead, you should rewrite them (example based on your first SELECT) into

   :NEW.prod_id := 'CBIS' || LPAD (:NEW.PRODUCT_SUMMERY, 4);

Oh, yes - one more thing: for your own sake, learn how to properly format code. Using such an unreadable mess is a nightmare.

Do that :) It'll make your (and our) life somewhat less complicated :) — Littlefoot, Jan 19 '18 at 12:08

exact fetch returns more than requested number of rows in a triger execution

2 Answers2