CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Xavier
Xavier

Reputation: 4017

Get a array of values form a PLSQL Function

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 ?

Upvotes: 0

Views: 315

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

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.

Upvotes: 1

Related Questions