Oracle ora-32033,ORA-01436: CONNECT BY loop in user data

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;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值