12 Replies Latest reply: Apr 14, 2014 11:47 AM by 3050d5ea-07a7-4d00-ba74-013e3b861a69 RSS

    How to copy data from a collection to an assocarray

    3050d5ea-07a7-4d00-ba74-013e3b861a69

      Hi Friend,

       

      I am getting below error while trying to copy data from one collection to other.

       

      • Error(17,8): PL/SQL: ORA-00904: "COLUMN_VALUE": invalid identifier

       

      Please help me providing a better way.


      create or replace function scrub_final_2 return sys_refcursor IS

      x type_tbl;                                   /*create or replace type type_record as object(employee_id NUMBER(6), first_name VARCHAR2(20));*/

      test1 type_tbl;                              /*create or replace type type_tbl as table of type_record;*/

      y sys_refcursor;

      z sys_refcursor;

      begin

       

       

      x:=type_tbl();

      z:=scrub_final_1;       /*This is a function which returns a refcursor*/

      loop

      fetch z bulk collect into test1;

      exit when z%NOTFOUND;

      select column_value bulk collect into x from  table(test1) ;

      end loop;

      open y for select employee_id,first_name from employees a where not exists (select employee_id from table(x) where a.employee_id=employee_id);

      return y;

      end;

       

      Awaiting your help friends, please help me.

       

      Thanks !!

        • 1. Re: How to copy data from a collection to an assocarray
          JustinCave

          Copying from my answer over on your identical StackOverflow thread

          First, using x, y, z, and test1 as variable names makes it relatively hard to understand your code since it is not obvious at any point which variables represent a cursor and which represent a collection. Calling an object type_record is also confusing since it is not, in fact, a record which is a PL/SQL structure very similar to a SQL object.

          Second, your loop as currently constructed doesn't make any sense. If you're going to have a loop, you'd want to do a bulk collect with a limit. If you're not going to use a limit in your bulk collect, there is no point in looping since you'll only ever have one iteration of the loop.

          Third, there appears to be no reason to copy the data from one collection to another. You can use the data in test1 to open y rather than using x in the query. Using a second collection just means that you're wasting valuable space in the PGA.

          Fourth, if you really do want to copy the data from one collection to another, you can do a simple assignment

          x := test1;

          Fifth, if you're going to write a select against a collection defined on an object type, the columns in the result will be the names of the object type's attributes. column_value is only a column name for collections of built-in types. If you really, really wanted to so the assignment the hard way with a select statement, you'd do something like

          SELECT type_record( employee_id, first_name ) BULK COLLECT INTO x FROM TABLE( test1 );

          • 2. Re: How to copy data from a collection to an assocarray
            3050d5ea-07a7-4d00-ba74-013e3b861a69

            Thank you sir for the response.

             

            Here what i wanted to do is

            1. will call a function(scrub_final_1) from the current function which returns a refcursor(with many column) and i will store the results in another cursor variable.(z)

            2. want to copy some columns to a collection(x) so that i can use this in the select query using a cursor

             

            like : open cursor y for select employee_id,first_name from employees a where not exists (select employee_id from table(x) where a.employee_id=employee_id);

             

            can you provide some alternare solution.

             

            Thanks !!

            • 3. Re: How to copy data from a collection to an assocarray
              JustinCave

              Are you saying that scrub_final_1 (not a great name for a function, by the way, final_1 seems like a suffix that is added in lieu of proper version control) returns a sys_refcursor that contains more columns than you have defined in your object type?  If so, you won't be able to fetch the data into a collection of your object types.  You'd need to fetch the data into collections, objects, or variables that match the projection of your cursor.

               

              I still don't see any obvious reason to copy the data from one collection to another.  As I said, just fetch the data into a single collection and use that in your subsequent query.

               

              Of course, this whole approach is not going to be very efficient.  Functions that return cursors are useful when you're returning data to a client application, they're generally rather inefficient when you're subsequently processing data in PL/SQL.  And moving data out of SQL into PL/SQL only to send it back to SQL is going to be inefficient if you have any sort of data volume.

               

              Justin

              • 4. Re: How to copy data from a collection to an assocarray
                3050d5ea-07a7-4d00-ba74-013e3b861a69

                Hi Sir,

                 

                Function "scrub_final_1" returns the same no of column that is defined in the object type. Please find bellow code. I have changed the variable names for better understanding.


                create or replace function scrub_final_2 return sys_refcursor IS

                x_type_tbl type_tbl:=type_tbl();

                p_type_tbl type_tbl:=type_tbl();

                y_cur sys_refcursor;

                z_cur sys_refcursor;

                 

                begin

                       /*calling function scrub_final_1*/

                z_cur:=scrub_final_1;                            

                 

                        /*Appending values from cursor to collection*/

                 

                for i in z_cur loop

                p_type_tbl.extend;

                p_type_tbl(p_type_tbl.count):=type_record(i.employee_id);                /*Appending only one column to collection as i do not need rest columns*/

                end loop;

                       /*Returning cursor after doing some data checks*/

                open y_cur for select * from employees minus (select * from table(p_type_tbl));

                return y_cur;

                end;

                 

                For the above code i am getting

                • Error(11,10): PLS-00221: 'Z_CUR' is not a procedure or is undefined

                 

                Thanks !!

                • 5. Re: How to copy data from a collection to an assocarray
                  3050d5ea-07a7-4d00-ba74-013e3b861a69

                  Hello Sir,

                  Can we use associate array to copy a single column from a collection ?? If yes, please help me by provide the code.

                   

                  Thanks !

                  • 6. Re: How to copy data from a collection to an assocarray
                    JustinCave

                    First, none of the collections you're using are associative arrays.  Do you really mean to ask about associative arrays?  Because all the code you've posted uses nested tables.

                     

                    Second, if you want to iterate through a returned SYS_REFCURSOR, you'd use explicit fetch operations, you can't use an implicit cursor loop.  You'd presumably want to do a BULK COLLECT like you were initially.  You'd just want to do one BULK COLLECT or do a BULK COLLECT with a LIMIT in a loop. 

                     

                    Third, unless your TYPE_RECORD body contains a single-argument constructor, you cannot construct an instance of TYPE_RECORD passing in a  single parameter.

                     

                    Fourth, since you're not using it, there is no need to declare X_TYPE_TBL.

                     

                    Fifth, if your MINUS query actually works, that implies that the EMPLOYEES table has exactly the same set of columns that are defined in your TYPE_RECORD.  That seems unlikely.

                     

                    Justin

                    • 7. Re: How to copy data from a collection to an assocarray
                      3050d5ea-07a7-4d00-ba74-013e3b861a69

                      Hi Sir,

                       

                      I have not yet used associated array in mu coding, was thinking to use it if possible.

                       

                      Can you guide me to for second and third point, how to give bulk collect to insert all record from cursor to a collection.

                       

                      And to copy only one column to an associated array ?

                      • 8. Re: How to copy data from a collection to an assocarray
                        rp0428

                        As I ask in my reply to the thread you posted in the Sql Developer forum:

                         

                        What PROBLEM are you trying to solve? If we know the real problem we can help you find the best way to deal with it.

                         

                         

                        Most of the code you posted has errors on some sort and whatever you are trying to do using ref cursors and collections in that way is NOT the way to do it.

                         

                        You could replace ALL of that code with a SQL query.

                        • Error(17,8): PL/SQL: ORA-00904: "COLUMN_VALUE": invalid identifier
                          . . .
                          select column_value bulk collect into x from  table(test1) ;

                          There is no column named 'column_value in 'test1'. 

                         

                        Test1 is a table of 'type_record' which has columns named 'employee_id' and 'first_name'

                        z:=scrub_final_1;       /*This is a function which returns a refcursor*/

                        Why are you using a function and returning a ref cursor?

                        loop

                        fetch z bulk collect into test1;

                        exit when z%NOTFOUND;

                        x.extend;

                        select column_value bulk collect into x from  table(test1) ;

                        Why are you doing an 'extend' when you are going to bulk collect into the collection? That makes no sense.

                         

                        Why are you using a loop if you aren't using a LIMIT clause?

                        open y for select employee_id,first_name from employees a where not exists (select employee_id from table(x) where a.employee_id=employee_id);

                        Why are you executing this statement if the loop doesn't return any data?

                         

                        That code is a TERRIBLE example of: 1) ref cursors, 2) use of collections, 3) use of PL/SQL when SQL should be used, 4) poor code constructs that are not scalable or performant.

                         

                        Again, what PROBLEM are you trying to solve?

                        • 9. Re: How to copy data from a collection to an assocarray
                          3050d5ea-07a7-4d00-ba74-013e3b861a69

                          Thank you for the response.

                           

                          Here what i wanted to do is

                          1. will call a function(scrub_final_1) from the current function which returns a refcursor(with many column) and i will store the results in another cursor variable.(z)

                          2. will copy all data from the cursor to a collection.

                          2. And then copy some columns from the collection to anothe associated array, so that i can use this in the select query as table function t return some data.

                           

                          like : open cursor y for select employee_id,first_name from employees a where not exists (select employee_id from table(x) where a.employee_id=employee_id);

                           

                          can you provide some alternare solution.

                          • 10. Re: How to copy data from a collection to an assocarray
                            rp0428
                            can you provide some alternare solution.

                             

                            Sorry - that isn't possible without first knowing the answer to the question I ask:

                            What PROBLEM are you trying to solve? If we know the real problem we can help you find the best way to deal with it.

                            So far all you've done is keep saying what you want to do.

                            Here what i wanted to do is

                            What you 'want to do' is a 'solution', and the wrong one at that. We need to know the PROBLEM.

                             

                            1. You, presumably, have a problem

                            2. You've chosen a terrible solution to that problem

                            3. You've ask for help fixing your terrible solution

                             

                            It would be better to abandon that solution and choose a better one. We can't help with that if we don't know the problem.

                             

                            Assuming you even need a multi-stage, row-by-row solution one choice to look at is PIPELINED functions. See the doc

                            http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm#CHDJEGHC

                            1. will call a function(scrub_final_1) from the current function which returns a refcursor(with many column) and i will store the results in another cursor variable.(z)

                            And that 'scrub_final_1' should likely NOT be returning a ref cursor if more steps are needed. That function is likely getting data from some source, transforming it somehow and then creating a cursor to return.

                             

                            Future steps should just USE the transformed data directly. There should be no need to create a cursor on it.

                             

                            2. will copy all data from the cursor to a collection.

                            The concept you are missing is this: a cursor does NOT have any data

                             

                            Why would you want to move data from whereever 'scrub_final_1' puts it to a collection?> Just let the following steps access it from where it is. Is that data in a table? In a temporary table? Where is it?

                            2. And then copy some columns from the collection to anothe associated array, so that i can use this in the select query as table function t return some data.

                            WHY? WHY? WHY? That makes no sense at all. Now you want to take it out of one collection and put it into another rather than just access it from the first collection (which you shouldn't be using anyway).

                             

                            Why are you adding so many seemingly unnecessary steps to the process?

                            like : open cursor y for select employee_id,first_name from employees a where not exists (select employee_id from table(x) where a.employee_id=employee_id);

                            And now we come full circle - you want to create yet another cursor based on collections instead of the actual source data.

                             

                            If this is the LAST step AND you are returning that cursor the a CLIENT application then it is ok.

                            • 11. Re: How to copy data from a collection to an assocarray
                              3050d5ea-07a7-4d00-ba74-013e3b861a69

                              Let me explain the whole scenario.

                               

                              I have a function which returns a refcursor after doing some calculation.

                               

                              I have been asked to create a function which will use the data from that function and compre(probably using minus opeartor or not exist clause) it with the data ofrom another separate table and return the difference data througha cursor.

                               

                              So to compare, i need to store the data in a object type and then i can use minus/not exist and return the result to a cursor, which will be the last step of the function.

                               

                              Steps as per my understanding need to be done.

                              1. declare a object type

                              2.decalre 2 rfcursor.

                              3.call the function which returns refcursor from the 1st refcursor.

                              4.then fetch the cursor to object type variable.

                              5.open another cursor where i can comare the object type data(using table function) and a separate table data.

                              6. return the cursor.

                               

                              Can u help me in this please. I have been asked to submit the task by todat EOD.

                               

                              Thanks, awaiting ur early response.