9 Replies Latest reply: Jan 21, 2014 5:35 PM by rp0428 RSS

    type object question

    Roger25
      create or replace type TListProd as object (
        -- FIELDS
        product_id number(9),
        -- METHODS
        constructor function TListProd return self as result
      );
      /
      show errors type TListProd;
      
      create or replace type body TListProd as
        constructor function TListProd return self as result as
          begin
            return;
          end;
      end;
      /
      show errors type body TListProd;
      
      create or replace type TListProdTab is table of TListProd;
      /
      
      create table test123(a varchar2(30), b number(9));
      
      declare
           l_dt TListProdTab:= TListProdTab();
      begin
          for i in 1..10 loop
             l_dt.extend;
             l_dt(l_dt.last) := TListProd(i);
          end loop;
          insert into test123 values('Something', (select * from table(l_dt)));
      end;
      /
      
      

       

      In this case, I want to bulk insert 10 rows in test123 (with 'Something', and values from 1 to 10). Using that select, I got a "single-row subquery returns more than one row".

      Is it possible this, somehow, using "for" statement?

      And can TListProdTab checked if it's empty using "l_dt is EMPTY"?

       

      Thank you!

        • 1. Re: type object question
          BluShadow

          Well, you're probably going to want something along the lines of...

           

           

          insert into test123

          select 'Something', t.*

          from table(l_dt) t;

           

          (untested)

           

          Using "Values" in your insert implies you are inserting a single row.

          • 2. Re: type object question
            Ramin Hashimzadeh

            declare 

                 l_dt TListProdTab:= TListProdTab(); 

            begin 

                for i in 1..10 loop 

                   l_dt.extend; 

                   l_dt(l_dt.last) := TListProd(i); 

                end loop; 

                insert into test123

                select 'Something', t.* from table(l_dt) t; 

            end; 

             

            -----

            Ramin Hashimzade

            • 3. Re: type object question
              Roger25

              Worked, thanks.

              But is it possible using "forall" and bulk insert (forall applied on that "object" type)?

              • 4. Re: type object question
                BluShadow

                Roger25 wrote:

                 

                Worked, thanks.

                But is it possible using "forall" and bulk insert (forall applied on that "object" type)?

                 

                Why?

                You want to incorporate more PL code and try and make it slower?

                • 5. Re: type object question
                  Roger25

                  I have read that sometimes, using "bulk" operations can be good for performance, and execution time

                  • 6. Re: type object question
                    Billy~Verreynne

                    And sometimes the heavens fall and we all wear blue hats?

                    • 7. Re: type object question
                      BluShadow

                      Roger25 wrote:

                       

                      I have read that sometimes, using "bulk" operations can be good for performance, and execution time

                       

                      What Billy means is that you shouldn't believe everything you read, or in some cases you should investigate further to better understand what is meant.

                       

                      Yes, Bulk operations can be fast.... when you compare them to row by row processing in a loop.

                      But Bulk operations will not be faster than a single SQL statement.

                       

                      In terms of performance, you should always aim to do it just in SQL first, and then if that's not possible, look at doing bulk operations (though those occasions should be rare!)

                      • 8. Re: type object question
                        Billy~Verreynne

                        Yep, more than likely the comment that "bulk collection is faster" came within a very specific context and disclaimers that were not read, or ignored, or not understood.

                        • 9. Re: type object question
                          rp0428
                          But is it possible using "forall" and bulk insert (forall applied on that "object" type)?

                          Yes - it is possible: Just use 'forall applied on that "object" type':

                          declare

                               l_dt TListProdTab:= TListProdTab();

                          begin

                              for i in 1..10 loop

                                 l_dt.extend;

                                 l_dt(l_dt.last) := TListProd(i);

                              end loop;

                              forall i in l_dt.first..l_dt.last

                              insert into test123 values('Something', l_dt(i).product_id);

                          end;

                          /

                          Can we assume that your example is just an 'example' of you trying to learn some concepts regarding collections? Because that 'example' doesn't need collections or object types at all to just insert a string constant and a row number.