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;
your error is caused by the select statements, e.g.
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