8 Replies Latest reply: Mar 12, 2014 2:58 PM by rp0428 RSS

    Function returning null instead of expected value

    debit72

      I am using SQL Developer version 3.2.20.09. When I run a certain custom standalone function, I get a null result instead of the expected numerical result. Other people in my workgroup get the correct result. When I run the function using SQL Plus, I get the expected result. Is it possible there is some setting in SQL Developer that is causing this behavior?

       

      Any insight appreciated.

       

      -- Debi

        • 1. Re: Function returning null instead of expected value
          Jeff Smith Sqldev Pm-Oracle

          Yeah, but w/o seeing your code, it's hard to know for sure. The first thing I would check is your NLS settings in Tools > Preferences.

          • 2. Re: Function returning null instead of expected value
            debit72

            Wow Jeff, you hit the nail on the head. I had made a modification to my NLS settings (Date Format) because I want to see full timestamps. I changed it to "DD-MON-RR HH:MI:SS AM".

             

            When I reset the NLS settings to default (Date Format = "DD-MON-RR"), the function works correctly.

             

            The function does expect a date as a parameter. I thought the NLS Date Format only affected how date fields are displayed in the Query Results of a SQL worksheet.

             

            Is there a way I can still see the hours/minutes/seconds in the query results, that won't affect anything else?

             

            In other words, if I run

             

            select sysdate from dual;

             

            I want to see a format of DD-MON-YYYY HH24:MI:SS instead of just DD-MON-RR, automatically, all the time, without me having to do a to_char or anything like that.

             

            THANK YOU!!

            • 3. Re: Function returning null instead of expected value
              rp0428

              Is there a way I can still see the hours/minutes/seconds in the query results, that won't affect anything else?

              You need to reread Jeff's first reply:

              Yeah, but w/o seeing your code, it's hard to know for sure.

              We can't know what your function is really doing, or how, if you don't post the code. All you said was sthis:

              The function does expect a date as a parameter.

              That seems suspect to me since DATEs don't have formats - they are DATEs, not strings.

               

              So what 'query results' are you even talking about? You said 'When I run the function'; you didn't say anthing about a query.

              • 4. Re: Function returning null instead of expected value
                debit72

                Sorry if I'm giving mixed messages. I went from the specific to the general.

                 

                I have reverted the NLS Date Format setting to the default DD-MON-RR. (Under those settings, the function returns proper results.)

                 

                But for my daily work, I often need to see a full date and time picture.

                 

                If I run "select sysdate from dual;" of course I get 10-MAR-14 as a result.

                 

                I would like to know if there is way that I can have such a query return 10-MAR-2014 14:21:33 instead of just 10-MAR-14, without using to_char(sysdate,'DD-MON-YYYY HH24:MI:SS').

                • 5. Re: Function returning null instead of expected value
                  Gary Graham-Oracle

                  Hi Debi,

                   

                  The only way to get dates to format the way you want without using to_char expressions is to modify the NLS settings, as noted above.  But that interferes with a "certain custom standalone function" for which you haven't posted the code.

                   

                  We could surmise the function contains some to_char(datetime, fmt, nlsparam) expression as documented here:

                  TO_CHAR (datetime)

                  where the function's logic assumes default values for fmt and nlsparam for your company, but your override of the NLS settings breaks the logic.  If that's the case, then the function is defective and fmt should be explicitly provided in that code.  But this is all supposition -- you need to post the code.

                   

                  Regards,

                  Gary

                  • 6. Re: Function returning null instead of expected value
                    rp0428
                    Sorry if I'm giving mixed messages. I went from the specific to the general.

                     

                    I have reverted the NLS Date Format setting to the default DD-MON-RR. (Under those settings, the function returns proper results.)

                     

                    But for my daily work, I often need to see a full date and time picture.

                     

                    If I run "select sysdate from dual;" of course I get 10-MAR-14 as a result.

                     

                    I would like to know if there is way that I can have such a query return 10-MAR-2014 14:21:33 instead of just 10-MAR-14, without using to_char(sysdate,'DD-MON-YYYY HH24:MI:SS').

                    That simple query is NOT using a custom function. Jeff's answer tells you what to do for that.

                     

                    But that simple query is NOT what you first asked about. Your question talked about using a custom function:

                    When I run a certain custom standalone function, I get a null result instead of the expected numerical result.

                    You still aren't telling us what your function is doing. All you said was:

                    The function does expect a date as a parameter

                    . . .

                    I get a null result instead of the expected numerical result.

                    A DATE parameter does NOT have a format. DATE values don't have formats; strings have formats. If that parameter is really a STRING and not a DATE datatype then you also already have your answer.

                     

                    Otherwise, if you want help with your function problem you need to post the DDL for the function so we can see what the datatypes of the parameters are and what the function is doing with them.

                    • 7. Re: Function returning null instead of expected value
                      debit72

                      I apologize if I'm being unhelpful. I'm not the person who wrote the function, in fact I'm not a developer at all. I asked one of the developers to get the code so I can post it here. I removed some identifying information from the comments but otherwise it's intact.

                       

                      FUNCTION
                      CITSBS_CUR_BAL_TRAN_DATE_FUN
                        ( p_entity_id        IN  NUMBER,
                          p_entity_type      IN  VARCHAR2,
                          p_current_date     IN  DATE
                        )
                      RETURN  NUMBER IS
                      -- =======================================================================================
                      -- FILE NAME    :  CITSBS_CUR_BAL_TRAN_DATE_FUN.pls
                      -- DATE CREATED :  05/23/02
                      -- DESCRIPTION  :  Function to calculate the Balance for a given entity upto and including
                      --                 a given date, using only transaction_date and payment_date.
                      --                 This function is same as CITSBS_LAST_BALANCE_DUE, except:
                      --                     - This function does not use effective_date nor as_of_date
                      --                     - This function include transactions on the input date, while
                      --                       CITSBS_LAST_BALANCE_DUE does not
                      -- PURPOSE      :  To Calculate the balance of an entity upto and including a given date.
                      --                 This is needed for the Aging Report's current balance column.
                      -- CALLS :  None
                      -- CALLED BY :  CITSBS_Aging_Report.rdf
                      -- OBJECTS :  Query on tables:
                      --    sbs_transactions
                      --    sbs_payments
                      -- PARAMETERS   :
                      --    p_entity_id      Entity id
                      --    p_entity_type    Entity type, 'P' or 'O'
                      --    p_current_date   The date used to calculate the balance.  Note that it can be any
                      --                     prior date.  Also the returned amount includes transactions
                      --                     on this date.
                      -- OUTPUT       :  Returns an amount which is the last_balance_due amount, without
                      --                 taking effective_date and as-of-date into account.
                      -- The following statements should or should not be used based on the need
                      -- WHENEVER SQLERROR EXIT 1 ROLLBACK
                      -- WHENEVER OSERROR  EXIT 1 ROLLBACK

                      -- start $APPL_TOP/connect/xxx.sql
                      -- where xxx = apps, citgl, citgms, etc.

                      v_SumCharges    sbs_bills.balance_due%TYPE;
                      v_SumPayments    sbs_bills.balance_due%TYPE;
                      v_errmsg                VARCHAR2(200) := NULL;

                      BEGIN
                         SELECT  NVL(SUM(NVL(original_amount,0)),0)
                           INTO  v_SumCharges
                           FROM  sbs_transactions
                          WHERE  entity_id = p_entity_id
                            AND  TRUNC(NVL(transaction_date,sysdate)) <= TRUNC(TO_DATE(p_current_date,'DD-MON-rrrr'))
                            AND  entity_type = p_entity_type
                            AND  transaction_type in ('C','AP'); -- treat ap's as charges

                         SELECT  NVL(SUM(NVL(original_amount,0)),0)
                           INTO  v_SumPayments
                           FROM  sbs_payments
                          WHERE entity_id = p_entity_id
                            AND TRUNC(NVL(payment_date,sysdate)) <= TRUNC(TO_DATE(p_current_date,'DD-MON-rrrr'))
                            AND entity_type = p_entity_type;

                         RETURN  (v_SumCharges - v_SumPayments);

                      EXCEPTION

                         WHEN OTHERS THEN
                            dbms_output.put_line( 'ERROR: CITSBS_LAST_BAL_TRAN_DATE_FUN:');
                            dbms_output.put_line( '   Entity id/entity type/date = '
                                                  || TO_CHAR(p_entity_id) || '/'
                                                  || p_entity_type || '/'
                                                  || TO_CHAR(p_current_date, 'DD-MON-YYYY')
                                                 );
                            v_errmsg := SUBSTR( SQLERRM,1,200 );
                            dbms_output.put_line( '   Oracle Error Msg: ' || v_errmsg );

                            RETURN NULL;
                      END;

                      • 8. Re: Function returning null instead of expected value
                        rp0428
                        I'm not the person who wrote the function, in fact I'm not a developer at all. I asked one of the developers to get the code so I can post it here.

                        And now I have to be the bearer of bad news. This is what your thread was all about:

                        Function returning null instead of expected value - When I run a certain custom standalone function, I get a null result instead of the expected numerical result.

                        Yes - that function is DESIGNED to return 'null instead of expected value'! Did you ever ask the developers why the function was returning NULL?

                         

                        So you really do NOT have a question for the forum at all! The code is 'working as designed'.

                         

                        Unfortunately for you the code was designed poorly. And that is because that code has a SERIOUS bug in it.

                        EXCEPTION

                           WHEN OTHERS THEN
                              dbms_output.put_line( 'ERROR: CITSBS_LAST_BAL_TRAN_DATE_FUN:');
                              dbms_output.put_line( '   Entity id/entity type/date = '
                                                    || TO_CHAR(p_entity_id) || '/'
                                                    || p_entity_type || '/'
                                                    || TO_CHAR(p_current_date, 'DD-MON-YYYY')
                                                   );
                              v_errmsg := SUBSTR( SQLERRM,1,200 );
                              dbms_output.put_line( '   Oracle Error Msg: ' || v_errmsg );

                              RETURN NULL;

                        That is why you get NULL. The function is returning NULL for ANY exception that occurs. One of those exceptions (NO DATA FOUND) will be when one of the queries doesn't return any data.

                         

                        Worse - the function HIDES those exceptions so no one can EVER see them. Unless someone just happens to be looking at a console they will have no idea (as you might not have known) that any exception even occurred.

                         

                        It is a rookie mistake to write code that uses a WHEN OTHERS exception handler that does not actually handle the exceptions or re-raise them.

                         

                        Something went wrong in the function so it returned a NULL as it was told to do. What went wrong? Who knows - the code HIDES the error so you don' t even know it happened. It could be anything.

                         

                        Since the code uses SELECT INTO it could just be a NO DATA FOUND exception where there was no data for one of the queries.

                         

                        One thing I can assure you of. If you had posted that code in the SQL and PL/SQL where I typically hang out you would probably have had a dozen others jump all over that use of WHEN OTHERS.

                         

                        Since this is the sql dev forum I won't even comment on the use of PL/SQL to do what could have just been done in SQL to begin with. Or the fact that using TRUNC (a function) on a table column like this guarantees that Oracle can't use any index that might exist on that column. Or the fact that the second query may have different data available than the first query since they are NOT part of the same transaction. Oracle is a multi-user database so another user could have issued a COMMIT that altered the data between the two queries in the function.

                        AND  TRUNC(NVL(transaction_date,sysdate)) <= TRUNC(TO_DATE(p_current_date,'DD-MON-rrrr'))

                        It wouldn't surprise me at all if you had performance issues with that function due to full table scans having to be performed for both of those queries.

                         

                        1. SQL is ALWAYS faster than PL/SQL

                        2. If PL/SQL is used it should ALWAYS have proper exception handlers

                        3. Queries embedded in PL/SQL should ALWAYS be written and tested manually to identify any performance or other issues.