ORA-01422: exact fetch returns more than requested number of rows

There are only 2 select statements in your function and the only one that could produce that error is this one.

     SELECT SIN
        INTO lnCode
        FROM (    SELECT *
                    FROM ITEM_DATA
              START WITH ITM_CODE = lnRow.ITM_CODE
              CONNECT BY NOCYCLE PRIOR TO_CODE = ITM_CODE)            
       WHERE TO_CODE IS NULL;

Select all Open in new window

That query returns more than one row.

Your sample data doesn't match with the table structure you supplied.  You are selecting all columns from a table when you really don't need to (you are only using 2).  If ITEM_TYPE = 9 you are returning no data.

I don't need the table structure to tell you which query causes the issue.

You supply create statements for 2 tables in your original post which contain a total of 10 columns.  Your sample data has 4 columns of data, so even if someone wanted to try to build a test case, it would be impossible.

The first post in this thread.

Table creates you provided.

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),
      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;      

Notice, 10 columns in bold.  This is the sample data provided:

ITM_CODE   accountNr       TO_CODE     accountNrTo
20751411   3120LB1433679   20751411    3120180573151
20751411   3120180573151   159960150   3120144348210

Notice, only 4 columns.

Given that information, how can someone construct meaningful sample data?

Regardless of the confusing samples, you should have the answer to your question as to why you are getting the error.

Let's assume for just a minute that the sample data you provided is 4 of the 5 columns of the ITEM_DATA table.  One would then attempt to create a sample set using the following:

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), 
	CONSTRAINT TO_CODE_UQ UNIQUE (ITM_CODE, TO_CODE));
insert into item_data values (20751411,   '3120LB1433679',   20751411,    3120180573151, null);
insert into item_data values (20751411,   '3120180573151',   159960150,   3120144348210, null);
commit;

Select all Open in new window

But, if you try to do that, you will get a constraint violation on the primary key.  If you remove the 2 constraints and then attempt to create the sample set, you would use this:

CREATE TABLE ITEM_DATA
   (ITM_CODE NUMBER(11,0), 
    accountNr varchar(20),--accountNr=ITM_CB||ITM_NR||ITM_NAME
	TO_CODE NUMBER(11,0), 
	accountNrTo varchar(20),
	ITEM_VERSION VARCHAR2(20 BYTE));
insert into item_data values (20751411,   '3120LB1433679',   20751411,    3120180573151, null);
insert into item_data values (20751411,   '3120180573151',   159960150,   3120144348210, null);
commit;

Select all Open in new window

Now, I can get the data into a table, but when I run the offending query:

     SELECT SIN
        INTO lnCode
        FROM (    SELECT *
                    FROM ITEM_DATA
              START WITH ITM_CODE = 20751411
              CONNECT BY NOCYCLE PRIOR TO_CODE = ITM_CODE)            
       WHERE TO_CODE IS NULL;

Select all Open in new window

I get no rows because there isn't a record where TO_CODE is NULL.

So, your sample data isn't valid and doesn't support your problem.  So, how can anyone help you.  This is your data in your system.  You should know the proper constructs, we're guessing.  We can point you in the right direction and with the information provided, I've gone as far as I can.

Again, your test data is invalid.  There is a primary key con ITEM_DATA.ITM_CODE.  Both of your records have the same value, so there is no possible way that this is valid.

Again, only you can answer the question as to how to distinguish between the 2 records and pick the correct one.  We cannot do that.