oracle-databaseplsqloracle-apex

Why I got error Ajax call returned server error ORA-01422: exact fetch returns more than requested number of rows for ajax_dispatch_request.?

I will do laboratory orders page in oracle APEX, I have 2 columns

customer and tests belongs to selected customer

first I will select the customer then I want to show list of tests for this customers

I did the following code for test list column :

SQL query for select list :

Select GET_CUSTOMERS_TEST(CUST_NO) TEST_NAME,CUST_NO
From CUSTOMERS_PRICE_LIST
WHERE CUST_NO = :P58_CUSTOMER;

Then I created the following function and called it from the Query GET_CUSTOMERS_TEST(CUST_NO)

create or replace function GET_CUSTOMERS_TEST(P_CUST_NO NUMBER)
RETURN VARCHAR2
IS 
    V_NAME VARCHAR2(255);
BEGIN
    IF P_CUST_NO IS NOT NULL THEN
        SELECT B.TEST_NAME_ENG INTO V_NAME
         FROM CUSTOMERS_PRICE_LIST A, LAB_TESTS B
         WHERE A.CUST_NO = P_CUST_NO
         AND A.TEST_NO = B.TEST_NO;
        RETURN V_NAME;
            
    ELSE
        return null;
    END IF;   
    EXCEPTION 
            WHEN NO_DATA_FOUND THEN
            NULL;     
END;
/

The error show when I select the customer then open the test list it show this error :

enter image description here

How can I solve this error please and thank you in advance .

Solution

  • Well... you should debug your apex page to find out what is going on exactly. The screenshots below are from a small test I wrote to simulate your error.

    Step 1: Enable Debug:

    enter image description here

    enter image description here

    Open that record and look for the error. That will show you what statement is erroring out.

    From your error it seems that the query

            SELECT B.TEST_NAME_ENG INTO V_NAME
         FROM CUSTOMERS_PRICE_LIST A, LAB_TESTS B
         WHERE A.CUST_NO = P_CUST_NO
         AND A.TEST_NO = B.TEST_NO;

    is returning more than one row. A SELECT INTO retrieves one or more columns from a single row docs. It will raise an exception with error 1422 if the select returns more than one row. If you are aware of that and you just want the first row then you can add a FETCH FIRST 1 ROWS ONLY

    ... 
         WHERE A.CUST_NO = P_CUST_NO
         AND A.TEST_NO = B.TEST_NO
         FETCH FIRST 1 ROWS ONLY;

    Else... probably a bug and the query needs to be fixed. Good luck !