Avatar of orauser
orauserFlag for Canada asked on

cursor not returning

I have to test a function that finds a date's year and week number based on the company fiscal year.  It works for the first record but not the second, even though they both pass the same date.  What is wrong?  DBMS output results in attachment.

        OK_DATE := VERIF_DATE (to_Date(to_char(R.WKL_DATE), 'DD-MON-YYYY'));
---
      FUNCTION VERIF_DATE (P_DATE DATE)
         RETURN VARCHAR2 IS
         CURSOR C (X_DATE DATE) IS
            SELECT ZMER0002 (X_DATE)
                 , ZMER0001 (X_DATE)
                 , ZMER0029 (ZMER0002 (X_DATE)
                           , ZMER0001 (X_DATE)
                           , 'WEK'
                           , 'E')
                 , 'Y'
              FROM DUAL;

         CURSOR D IS
            SELECT TO_CHAR (P_DATE, 'D') FROM DUAL;

         V_WEEK_DAY   VARCHAR2 (30);
         V_DATE           DATE;
         DUMMY            VARCHAR2 (1);
         --new
         s_40                date;
         
      BEGIN
     
      --new
       DUMMY := 'N';
      s_40 := (to_date(to_char(sysdate),'DD-MON-YYYY') - 40);
     
        dbms_output.put_line('dummy start = ' || DUMMY);
       dbms_output.put_line('P_DATE = ' || P_DATE);
           dbms_output.put_line('s_40 = ' ||s_40);
           
        IF ((P_DATE > SYSDATE)  or (p_date < s_40)) THEN
        dbms_output.put_line('here');
            V_YYYY := NULL;
           V_WEEK := NULL;
           DUMMY := 'N';
        ELSE
            V_DATE := P_DATE;
           --             V_DATE := TO_DATE(P_DATE,'DD-MON-RRRR');

       dbms_output.put_line('V_DATE = ' || V_DATE);
       
            OPEN D;
            FETCH D INTO V_WEEK_DAY;
            CLOSE D;

            IF V_WEEK_DAY = '1' THEN -- sunday means celui de la semaine precedente
               V_DATE := V_DATE - 1;
            END IF;

            OPEN C (V_DATE);
            FETCH C INTO V_YYYY, V_WEEK, V_CAL_END_DATE, DUMMY;
           
                                 dbms_output.put_line('dummy1 = ' || DUMMY);
                      dbms_output.put_line('year1 = ' || V_YYYY);
                                            dbms_output.put_line('week1 = ' ||V_WEEK);

            IF C%NOTFOUND THEN
                V_YYYY := NULL;
                V_WEEK := NULL;
                DUMMY := 'N';
            END IF;

            CLOSE C;            
         END IF;
         
                     dbms_output.put_line('dummy2 = ' || DUMMY);
                      dbms_output.put_line('year2 = ' || V_YYYY);
                                            dbms_output.put_line('week2 = ' ||V_WEEK);
  dbms_output.put_line('end');
         RETURN (DUMMY);
      END;

toad-dbms-output.doc
Oracle Database

Avatar of undefined
Last Comment
orauser

8/22/2022 - Mon
slightwv (䄆 Netminder)

I'm unable to open the attached file.  Can you upload it as a flat text file?
anumoses

use loop and end loop
anumoses

since you are not looping into multiple records, it give one record and comes out.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

I missed that completely.  Yes.  For you to loop through cursor results you must have a loop.  

To return this from a function you either need to return the entire cursor or to return individual rows, make it a pipelined function.

http://www.akadia.com/services/ora_pipe_functions.html
johnsone

No need to loop.  Both cursors are selecting from DUAL and would only return 1 record.  I think it can be rewritten more simply, but I cannot see what is going on.
johnsone

For example, this:

            OPEN D;
            FETCH D INTO V_WEEK_DAY;
            CLOSE D;

            IF V_WEEK_DAY = '1' THEN -- sunday means celui de la semaine precedente
               V_DATE := V_DATE - 1;
            END IF;

Could simply be written as this:

   IF to_char(P_DATE, 'D') = '1' then
       V_DATE := V_DATE - 1;
   END IF;
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

I give up...  missed that as well.

Get rid of the cursors and go with 'INTO':

SELECT TO_CHAR (P_DATE, 'D') into V_WEEK_DAY FROM DUAL;

same for the other one.
slightwv (䄆 Netminder)

>>Could simply be written as this:

Even better!!!
johnsone

The second query is calling 3 other functions.  Is it possible one of those is erroring and not returning the error back?  Like a WHEN OTHERS NULL?
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
johnsone

Also, after the second cursor, you check for NOTFOUND.  This would never happen.  You will always get 1 record back because DUAL always contains 1 record.

ASKER
orauser

I loop through a table (outside this code) and for each record in the table, call this function and pass the date field:
OK_DATE := VERIF_DATE (to_Date(to_char(R.WKL_DATE), 'DD-MON-YYYY')).  

dbms results:

dummy start = N
P_DATE = 04-NOV-10
s_40 = 23-OCT-10
V_DATE = 04-NOV-10
dummy1 = Y
year1 = 2010
week1 = 40
dummy2 = Y
year2 = 2010
week2 = 40
end
v_counter = 2
dummy start = N
P_DATE = 04-NOV-10
s_40 = 23-OCT-10
V_DATE = 04-NOV-10
dummy1 = Y
year1 =
week1 =
dummy2 = Y
year2 =
week2 =
end

----

The cursor c returns the year (yyyy) and week number (##) for the one date sent to it based on the company fiscal year.

ASKER CERTIFIED SOLUTION
johnsone

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.
See how we're fighting big data
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
ASKER
orauser

Thanks Johnsone,
The problem was that I changed the p_date to format dd-mon-yyyy and sysdate to the same to compare (because the original code didn't work) and then I passed this formatted date to the functions, which wouldn't accept that format.  I don't know why the first record worked but the rest didn't, but anyway it works now.  Personally I prefer your function call to the cursor but I was just asked to investigate why it didn't work and tried not to change their format too much.  Thanks!!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.