Hi i have the following function am geting this error when i pass valueCREATE OR REPLACE FUNCTION GEN_CURRENT_AccNo (vsInput VARCHAR2) RETURN number IS wrk_sin number; lsNSN VARCHAR2 (255); lnRow ITEM_BASIC%ROWTYPE; lnCode IITEM_BASIC.item_code%TYPE; lnAccnr ITEM_BASIC.item_code%TYPE; c_cnt_acc number(10);BEGIN SELECT * INTO lnRow FROM (SELECT * FROM ITEM_BASIC a where a.ITM_CODE = regexp_replace(vsInput,'[^[:digit:]]') UNION ALL SELECT * FROM ITEM_BASIC a WHERE ITM_CB||ITM_NR||ITM_NAME = vsInput) WHERE ROWNUM <= 1; IF lnRow.item_type = '9' then SELECT SIN INTO lnCode FROM ( SELECT * FROM ITEM_DATA START WITH ITM_CODE = lnRow.ITM_CODE CONNECT BY PRIOR TO_CODE = ITM_CODE) WHERE TO_CODE IS NULL; ELSE lnAccnr := lnRow.ITM_CODE; END IF; RETURN lnAccnr;EXCEPTION WHEN OTHERS THEN raise_application_error (-20001, SQLERRM);END;
in database they will be like this
ITM_CODE accountNr TO_CODE accountNrTo
20751411 3120LB1433679 20751411 3120180573151
20751411 3120180573151 159960150 3120144348210
CREATE TABLE ITEM_DATA (ITM_CODE NUMBER(11,0) primary key, accountNr varchar(20),--accountNr=ITM_CB||ITM_NR||ITM_NAME TO_CODE NUMBER(11,0), accountNrTo varchar(20), ITEM_VERSION VARCHAR2(20 BYTE), UNIQUE (ITM_CODE) CONSTRAINT TO_CODE_UQ UNIQUE (ITM_CODE, TO_CODE)); CREATE TABLE ITEM_BASIC (ITM_CODE NUMBER(11,0), ITM_NAME VARCHAR2(23 BYTE), ITM_CB VARCHAR2(50 BYTE), ITM_NR VARCHAR2(20 BYTE), item_type varchar(2), CONSTRAINT IM_CODE_PK PRIMARY KEY (ITM_CODE); CONSTRAINT IT_CODE_FK FOREIGN KEY (ITM_CODE) REFERENCES ITEM_DATA1 (ITM_CODE) ENABLE;