11 Replies Latest reply: Jul 7, 2014 4:01 PM by rp0428 RSS

    Help in Usage of PLSQL Collection type

    Venkat Rakesh


      Hi Experts,

      I need a help in PLSQL collection type. Below is the sample code snipet

       

      {Code}

       

      Type Temp_obj IS Object

      (

        Column1 NUMBER,

        Column2 NUMBER,

        Process_flag VARCHAR2(10)

      );

       

      l_tab_tmp Temp_obj := Temp_obj( );

       

      FOR loop_var IN (SELECT * FROM TABLE(l_tab_tmp))

      LOOP

         -- << Logic >>

         -- Want to mark the column process_flag = 'PROCESSED'

      END LOOP;

       

      {Code}

       

       

      In the above example , I want to do some processing based on the records in above object type. Inside te loop I want to mark that particular record as processed based on some conditions. But How can I get the correct index to assign the process flag to 'PROCESSED' .

       

      Can any one help if there is any approach for my requirement.

       

      Thanks for your help in advance,

      Rakesh

        • 1. Re: Help in Usage of PLSQL Collection type
          Billy~Verreynne

          Do NOT push PL/SQL collections through the SQL engine like that. It is a huge and unnecessary overhead.

           

          You want to mark the attribute as processed then:

          for i in 1 .. collectionVariable.Count loop
            .. do something ..
            collectionVariable(i).flag := 'PROCESSED';
          end loop;
          

           

          Also note the use of PROPER standards - no silly uppercase of chosen reserved words. PL/SQL is Oracle's implementation of Ada as database stored procedural language. Ada standards apply DIRECTLY to PL/SQL. See Ada 95 Quality and Style Guide for details.

          • 2. Re: Help in Usage of PLSQL Collection type
            Venkat Rakesh

            Hi Billy,

            Thanks for your response.

            The problem is I cannot use the collectionvariable.count in the loop. Because I want use this as a query, I mean I can have where condition in the query so I may not get all records in the loop and loop index won't match the collection type index.

             

            Regards,

            RAkesh

            • 3. Re: Help in Usage of PLSQL Collection type
              Billy~Verreynne

              Why a SQL query on the collection? What does the SQL query provide that is needed, and cannot be done using a FOR loop as shown?

              • 4. Re: Help in Usage of PLSQL Collection type
                Venkat Rakesh

                Hi Billy,

                No , My requirement is some thing like I want to process only certain records based on a condition. If I make it as a query I can easily filter out the num of records which I actually want to process.

                else I have to iterate the loop for all records from first to last and in addition to that I have multiple other loops down the line using the same collection variable and I dont want to access the same record in the next loop. That is the reason I want to flag it.

                 

                Regards,

                Rakesh

                • 5. Re: Help in Usage of PLSQL Collection type
                  2683628

                  Could all of the logic in the various loops be included in the main processing loop ?

                   

                  Using if then else or case statements ?

                  • 6. Re: Help in Usage of PLSQL Collection type
                    Billy~Verreynne

                    Still does not make sense.

                     

                    If you want to filter, then use IF or CASE conditions in the loop, e.g.

                    for i in 1 .. collectionVariable.Count loop  
                    
                      if condition-1 then
                        .. do someting-1 ..
                        collectionVariable(i).flag := 'PROCESSED';  
                    
                      end if;
                    
                      case
                         when case-1 then ..do something-2..;
                         when case-2 then ..do something-3..;
                      else
                         ..do something-else..;
                      end case;
                    
                    end loop;
                    
                    

                     

                    If the body of the loop seems clunky and large, modularise it. E.g.

                    for i in 1 .. collectionVariable.Count loop
                      ProcessPayment( collectionVariable(i) );
                      UpdateBalance( collectionVariable(i) );
                      ..etc..
                    end loop;
                    
                    

                     

                    Pushing a collection (an array in PL/SQL memory), as a bind variable to the SQL engine (copy it), just because it seems you are too lazy to code a predicate in PL/SQL? - how is that sound justification?

                    • 7. Re: Help in Usage of PLSQL Collection type
                      Karthick_Arp

                      Can you give a simple example of what you are trying to achieve (The primary objective, not the technique that you have chosen). Provide us table structure, sample data and your DB version. I am sure we can offer you a better solution without using collection, and just with pure SQL. 

                      • 8. Re: Help in Usage of PLSQL Collection type
                        BluShadow

                        Venkat Rakesh wrote:

                         

                        Hi Billy,

                        No , My requirement is some thing like I want to process only certain records based on a condition. If I make it as a query I can easily filter out the num of records which I actually want to process.

                        else I have to iterate the loop for all records from first to last and in addition to that I have multiple other loops down the line using the same collection variable and I dont want to access the same record in the next loop. That is the reason I want to flag it.

                         

                        Regards,

                        Rakesh

                         

                        And what makes you think that SQL isn't going to have to iterate through all the records to apply the condition to just give you the required records.

                        By passing it to the SQL engine you're creating an overhead of passing all that data between PL and SQL engines, just to get SQL to apply a condition that you could easily do using an IF or CASE statement in PL.  As Billy says, it doesn't make sense.

                         

                        Has the data come from database tables in the first place?  In which case you shouldn't even load it into a collection, you should just process it directly in SQL.

                         

                        Explain fully where this data is coming from and why, if it's just created in a collection, that it needs SQL for it?

                        • 9. Re: Help in Usage of PLSQL Collection type
                          rp0428

                          l_tab_tmp Temp_obj := Temp_obj( );

                           

                          FOR loop_var IN (SELECT * FROM TABLE(l_tab_tmp))

                          LOOP

                             -- << Logic >>

                             -- Want to mark the column process_flag = 'PROCESSED'

                          END LOOP;

                           

                          In the above example , I want to do some processing based on the records in above object type

                          There ARE NO records in that 'object type'.

                           

                          All you created is an empty collection. So there is NOTHING to iterate through and NO data to work with.

                           

                          As others have said you could just populate that collection with the data you want to use.

                           

                          Post the actual example of what you are trying to do.

                          • 10. Re: Help in Usage of PLSQL Collection type
                            kendenny

                            Also your object is not a collection. It is a single object. In order to have a collection you need another type which is a table of temp_obj.

                            • 11. Re: Help in Usage of PLSQL Collection type
                              rp0428

                              kendenny wrote:

                               

                              Also your object is not a collection. It is a single object. In order to have a collection you need another type which is a table of temp_obj.

                              No - I do NOT need another type. I do NOT have any objects.

                               

                              OP has objects - maybe you should reply to one of OPs posts.