TOAD Oracle Function error

jknj72
jknj72 used Ask the Experts™
on
I have a Select statement that im trying to return a value for a column and its giving me an error saying that
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ANET.FUNC_OMNI_CHECK_ESSENTIAL", line 32

Does it have anything to do with passing the column FILE_GUID which is in the same select statement.

Here is the Select statement Im trying to use the function in...
SELECT FILE_GUID, FILE_UPLOADED_BY, FILE_DESCRIPTION, FILE_BYTES, FILE_NAME, FILE_UPLOAD_LOCATION, FILE_FULL_NAME, FILE_UPLOAD_TYPE, FILE_UPLOAD_DATE,
FUNC_OMNI_DEPT_NAME_PAGEID(FILE_DEPT_OWNER) FILE_DEPT_OWNER, CONTENT_TYPE, FILE_CREATE_DATE,
FUNC_DOC_LINKS(FILE_GUID) as LinkInfo,
FUNC_OMNI_CHECK_ESSENTIAL(FILE_GUID) as FILE_ESSENTIAL
FROM VW_OMNI_ENT_FILE_LIST WHERE FILE_DEPT_OWNER ='400000' AND UPPER(FILE_UPLOAD_TYPE)='ADOBE' ORDER BY FILE_CREATE_DATE DESC

And here is the function code...
CREATE OR REPLACE FUNCTION FUNC_OMNI_CHECK_ESSENTIAL
(vFile_GUID IN VARCHAR2)
RETURN NUMBER

IS

BEGIN
   DECLARE
        lnkURL VARCHAR(255);
        lnkID NUMBER;
        cnt NUMBER;
       
        BEGIN      
            SELECT UPPER(FILE_UPLOAD_TYPE || '/' || FILE_FULL_NAME) into lnkURL from OMNI_ENT_FILES where UPPER(FILE_GUID) = UPPER(vFile_GUID);
            SELECT LINK_ID into lnkID FROM OMNI_ENT_LINK WHERE UPPER(LINK_URL) = UPPER(lnkURL);
           
        SELECT COUNT(*) into cnt from OMNI_COMMON_LOOKUP where LOOKUP_KEY LIKE '%ESSENTIALS_%' and LOOKUP_VALUE_NUMBER=lnkID;
            if cnt > 0 then
                RETURN 1;
            else
                RETURN 0;
            end if;
       
        END;

Thanks all....Id appreciate any help I can get...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
One of the selects in your function returns more than one row.

When you select into a variable, the select can only return one row or you get the error.

You either have a data issue where more than one row is satisfied by your select or you need to change the function so the select returns a specific row.
Also the code which you had posted of the function FUNC_OMNI_CHECK_ESSENTIAL is incomplete.

As Steve suggested error is because of retrieval of more than a single row from a select statement into a variable.

As Error clearly suggests, that error is at line 32,
ORA-06512: at "ANET.FUNC_OMNI_CHECK_ESSENTIAL", line 32

open up the code and see the statement at line no 32 to pin-point the problem

Author

Commented:
I found the problem.  It was bad data. Thanks to you both. Splitting points

Author

Commented:
Thanks to you both
You are Welcome ..!! Glad that we could be of help :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial