1 2 3 Previous Next 33 Replies Latest reply: Feb 19, 2013 3:57 AM by user2626293 Go to original post RSS
      • 15. Re: Query On PL/SQL Collections
        jeneesh
        Between,

        I am not Pat, my name is jeneesh :)

        Pat was a name in your sample data..
        • 16. Re: Query On PL/SQL Collections
          user2626293
          Hi Jeneesh :),

          Below is that package that i have.

          Code removed.

          Thanks and Regards
          Srinivas

          Edited by: BluShadow on 07-Feb-2013 08:36
          added {noformat}
          {noformat} tags for readability.  Please read {message:id=9360002} and learn to do this yourself.
          
          Edited by: user2626293 on Feb 26, 2013 2:39 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          • 17. Re: Query On PL/SQL Collections
            user2626293
            Code removed

            Edited by: user2626293 on Feb 26, 2013 2:40 AM
            • 18. Re: Query On PL/SQL Collections
              user2626293
              Code removed

              Edited by: user2626293 on Feb 26, 2013 2:40 AM
              • 19. Re: Query On PL/SQL Collections
                user2626293
                Code removed

                Edited by: user2626293 on Feb 26, 2013 2:41 AM
                • 20. Re: Query On PL/SQL Collections
                  user2626293
                  I am trying to retrive the values out of this package using the below code.
                  DECLARE 
                    l_parent_rec     apps.seagrs_ext_ref_call_pkg.drive_rec_type;
                    l_childtbl       apps.seagrs_ext_ref_call_pkg.child_sn_tbl_type;
                    p_part_number varchar2(30):='9HH131-188'; 
                    p_serial_number varchar2(30):='3KGQ0051';
                    l_return_code number:=0;
                    l_return_message varchar2(500):=NULL;
                  begin
                    seagrs_ext_ref_call_pkg.get_warranty(p_part_number,p_serial_number,l_parent_rec,l_childtbl,l_return_code,l_return_message);
                    dbms_output.put_line(l_parent_rec.serial_number);
                    dbms_output.put_line(l_parent_rec.warranty_code);  
                    for i in l_childtbl.first .. l_childtbl.last 
                    loop
                      dbms_output.put_line(l_childtbl.serial_number);  
                    end loop;
                    dbms_output.put_line('The return code and error message are : '||l_return_code||'   '||l_return_message);
                  END;
                  If i comment the loop part in the above snippet of code, it works. Please advice.

                  Thanks and Regards
                  Srinivas

                  Edited by: BluShadow on 07-Feb-2013 08:39
                  • 21. Re: Query On PL/SQL Collections
                    user2626293
                    Dear Jeneesh/All,

                    Please help me with the issue above.

                    Thanks and Regards
                    Srinivas
                    • 22. Re: Query On PL/SQL Collections
                      BluShadow
                      user2626293 wrote:
                      Dear Jeneesh/All,

                      Please help me with the issue above.

                      Thanks and Regards
                      Srinivas
                      Have patience. This is an international forum and people have their own lives to lead. Many people were (or still are) asleep when you were posting your code.
                      • 23. Re: Query On PL/SQL Collections
                        user2626293
                        Hi Blushadow,

                        Thanks for your response, will wait. Sorry for the inconvenience.

                        Thanks and Regards
                        Srinivas

                        Edited by: user2626293 on Feb 7, 2013 12:50 AM
                        • 24. Re: Query On PL/SQL Collections
                          AlbertoFaenza
                          user2626293 wrote:
                          Dear Jeneesh/All,

                          Please help me with the issue above.

                          Thanks and Regards
                          Srinivas
                          If we only could understand exactly what issue you are facing, yes.

                          You said:
                          If i comment the loop part in the above snippet of code, it works. Please advice.
                          I guess you are referring to this loop:
                            for i in l_childtbl.first .. l_childtbl.last 
                            loop
                              dbms_output.put_line(l_childtbl.serial_number);  
                            end loop;
                          Which error are you getting? I think you missed the index when displaying the collection element:
                              dbms_output.put_line(l_childtbl(i).serial_number);  
                          Please notice: actually this loop can give you an error if
                          a) collection is not empty
                          b) collection is not dense

                          Assuming that your collection (nested table) is dense as I don' see any collection delete in your package I would suggest to make a change to avoid error in case your collection is empty:
                            for i in l_1 .. l_childtbl.count 
                            loop
                              dbms_output.put_line(l_childtbl(i).serial_number);  
                            end loop;
                          Regards.
                          Al
                          • 25. Re: Query On PL/SQL Collections
                            user2626293
                            Hi Al,

                            Thanks for your response. You are correct below is the loop that i was refering to;

                            for i in l_childtbl.first .. l_childtbl.last
                            loop
                            dbms_output.put_line(l_childtbl.serial_number);
                            end loop;

                            Below is the error that i am facing.

                            Error report:
                            ORA-06531: Reference to uninitialized collection
                            ORA-06512: at line 12
                            06531. 00000 - "Reference to uninitialized collection"
                            *Cause:    An element or member function of a nested table or varray
                            was referenced (where an initialized collection is needed)
                            without the collection having been initialized.
                            *Action:   Initialize the collection with an appropriate constructor
                            or whole-object assignment.

                            Actually i have indexed the collection element (missed in the below snippet), but gives me the same error.

                            I have tried executing below code with the suggested changes, but it throws the same error.

                            declare
                            l_parent_rec apps.seagrs_ext_ref_call_pkg.drive_rec_type;
                            l_childtbl apps.seagrs_ext_ref_call_pkg.child_sn_tbl_type;
                            p_part_number varchar2(30):='9HH131-188';
                            p_serial_number varchar2(30):='3KGQ0051';
                            l_return_code number:=0;
                            l_return_message varchar2(500):=NULL;
                            begin
                            apps.seagrs_ext_ref_call_pkg.get_warranty(p_part_number,p_serial_number,l_parent_rec,l_childtbl,l_return_code,l_return_message);
                            dbms_output.put_line(l_parent_rec.ship_to_customer);
                            dbms_output.put_line(l_parent_rec.country_code);
                            /*(for i in l_childtbl.first .. l_childtbl.last
                            loop
                            dbms_output.put_line(l_childtbl(i).product_part_number);
                            end loop;*/
                            for i in 1 .. l_childtbl.count
                            loop
                            dbms_output.put_line(l_childtbl(i).serial_number);
                            end loop;
                            dbms_output.put_line('The return code and error message are : '||l_return_code||' '||l_return_message);
                            END;

                            Thanks and Regards
                            Srinivas
                            • 26. Re: Query On PL/SQL Collections
                              BrendanP
                              I would find out which indices have been set, and loop using FIRST and NEXT, something like this:
                              i := array.FIRST;
                              -- print i
                              WHILE i IS NOT NULL LOOP
                                ...
                                i := array.NEXT(i);
                              
                              -- print i
                              
                              END LOOP
                              Edited by: BrendanP on 07-Feb-2013 01:31
                              • 27. Re: Query On PL/SQL Collections
                                AlbertoFaenza
                                Hi Srinivas,

                                read my previous answer and change the way you loop through the collection in the way I have suggested or Brendan suggested.

                                Brendan solution will work even in case your collection is sparse or empty.

                                The one I have suggested will work with empty collections (which seems to be your problem) but only if the collection is dense.

                                Regards.
                                Al
                                • 28. Re: Query On PL/SQL Collections
                                  user2626293
                                  Hi Al/Brendan

                                  I have tried both of you methods but, i get the same error. Please advice. Is there anything wrong with the package body. Please advice.

                                  --By Al
                                  for i in 1 .. l_childtbl.count
                                  loop
                                  dbms_output.put_line(l_childtbl(i).serial_number);
                                  end loop;

                                  --By Brendan

                                  i := l_childtbl.first;
                                  dbms_output.put_line(i);
                                  while i is not null loop
                                  DBMS_OUTPUT.PUT_LINE(L_CHILDTBL(i).SERIAL_NUMBER);
                                  i := l_childtbl.next;
                                  DBMS_OUTPUT.PUT_LINE(i);
                                  end loop;

                                  The above code fails in the statement one itself i.e. i := l_childtbl.first;

                                  Thanks and Regards
                                  Srinivas
                                  • 29. Re: Query On PL/SQL Collections
                                    AlbertoFaenza
                                    Hi Srinivas,

                                    I rewrite completely my post.

                                    It seems that in your package when one of these conditions are not true:
                                    IF (l_soap_response IS NOT NULL OR l_soap_response != '')
                                    OR
                                    IF (NVL(p_parent_rec.child_sn_count,0) != 0) THEN
                                    then your collection is not initialized.

                                    To avoid this problem I suggest always to assign the empty collection l_child_tbl at the beginning of your procedure:
                                    ...
                                       l_path         VARCHAR2 (2000);
                                    BEGIN
                                       p_child_tbl := l_child_tbl;
                                       -- p_part_number must provide by calling program
                                       l_error_message := 'Error - p_part_number is missing.';
                                    ...
                                    Regards.
                                    Al

                                    Edited by: Alberto Faenza on Feb 7, 2013 11:18 AM

                                    Edited by: Alberto Faenza on Feb 7, 2013 11:27 AM