0

I coded a function to select some flux in a queue and lock them with an updated flag. I made it with a cursor and it worked great. But i need to get the ID of the flux i locked to process them in my application.

So i start to code a function:

CREATE OR REPLACE Function getIDArray
  RETURN VARCHAR2 is
  arr varchar2(1000);

          CURSOR flux_to_process                                                          
          IS                                                                              
           SELECT  FLUX_ID, LOCKED_FLAG
           FROM (
               SELECT FLUX_ID, FLUX, GROUP_STORE_ID, STORE_ID, REFID, FLUX_TYPE, LOCKED_FLAG
                 FROM DEV_ISB_TRANSACTIONS.BUFFER_FLUX                                      
                 WHERE status = 0                                                           
                 AND LOCKED_FLAG = 0                                                                                                             
                 ORDER BY DATE_CREATION ASC)
            WHERE ROWNUM <= 8;

          BEGIN                                                                           

          FOR flux_rec IN flux_to_process                                                 
              LOOP                                                                        
              IF flux_rec.LOCKED_FLAG = 0
                THEN      

                UPDATE DEV_ISB_TRANSACTIONS.BUFFER_FLUX                                    
                SET LOCKED_FLAG = 1                                                         
                WHERE FLUX_ID = flux_rec.FLUX_ID; 
                arr := flux_rec.FLUX_ID;

              else exit;
          COMMIT;                                                                         
          END IF;
          END LOOP; 
          RETURN arr;
         END;

The function compilation return an OK but i got no return of my values.

Do you guys have any clue to how to do this ?

8
  • Why do you use a cursor and loop if your cursor query only returns only one row? (WHERE ROWNUM <= 1) Commented Jan 7, 2015 at 16:52
  • Because it can return more than 1 row, actually it's a variable in my application. i edited the question :) Commented Jan 7, 2015 at 16:56
  • 3
    @angezanetti Maybe I am wrong, but at first sight all of this seems over-complicated for a simple task. Can't you do all that job using only one request UPDATE ... WHERE ... IN (SELECT ...) RETURNING ... BLUK COLLECT INTO ... (docs.oracle.com/cd/B19306_01/appdev.102/b14261/…) Commented Jan 7, 2015 at 17:07
  • 2
    My best guess is that the cursor is returning no data. Try running the cursor SQL in SQL*Plus or something similar. Best of luck. Commented Jan 7, 2015 at 17:09
  • Reason should be exit keyword, i suggest you remove the else part, because if in your first flux_rec the flux_rec.LOCKED_FLAG <> 0 then the program will exit without results. Commented Jan 7, 2015 at 17:10

1 Answer 1

1

Concerning your issue per se, the only two reasons I can see for the function to return "no value" would be either:

  • the SELECT part returns an empty set,
  • you have one record where FLUX_ID is NULL.

For improbable that could be that later option given the name of the column, it would be rather coherent with the fact that you override the result at each iteration -- and the ORDER BY orders NULL after not-NULL by default.

Sign up to request clarification or add additional context in comments.

1 Comment

The select returns 8 values, FLUX_ID is never NULL... i rewrited the request here (The update works fine) stackoverflow.com/questions/27836393/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.