Avatar of jknj72
jknj72
 asked on

TOAD Oracle Function error

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...
DatabasesOracle DatabaseSQL

Avatar of undefined
Last Comment
Wasim Akram Shaik

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Wasim Akram Shaik

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jknj72

ASKER
I found the problem.  It was bad data. Thanks to you both. Splitting points
jknj72

ASKER
Thanks to you both
Wasim Akram Shaik

You are Welcome ..!! Glad that we could be of help :)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23