why the loop cannot pick the next record?

I am debugging a package. I don't understand why the loop cannot pick up the next record, I have record something like below

inventory_id       url
1234                  http://test1.html
1234                  http://test2.html
1234                 http://test3.html

I want to know if lopping for next record will not pick up the next record of the inventory_id is the same?

I have the oracle program something like below
....
BEGIN
         v_inventory_item_id := p_dev_prods.FIRST;
 WHILE v_inventory_item_id IS NOT NULL
         LOOP
 DECLARE
               l_url           VARCHAR2 (3000);
...........

 BEGIN
               l_url :=........

......
 v_inventory_item_id := p_dev_prods.NEXT (v_inventory_item_id);

 END;
         END LOOP;
......

I don't understand why the output url only show http://test1.html
wasabi3689Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I might have been mistaken.  In trying to come up with a test that shows what I was thinking, I cam acorss a code example that seems to mirror what you posted:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#BABFJJIH

The sample is in the code window below.

For me to troubleshoot what is happening in your code, I'll need to see the actual code to include the type definition, how it's populated and the full loop code that isn't working.



Example 5-31 Using FIRST and LAST With a Collection

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,3,5,7);
   counter INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST);
   DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST);
-- When the subscripts are consecutive starting at 1, 
-- it's simple to loop through them.
   FOR i IN n.FIRST .. n.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));
   END LOOP;
   n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps or the collection might be uninitialized,
-- the loop logic is more extensive. We start at the first element, and
-- keep looking for the next element until there are no more.
   IF n IS NOT NULL THEN
      counter := n.FIRST;
      WHILE counter IS NOT NULL
      LOOP
         DBMS_OUTPUT.PUT_LINE('Element #' || counter || ' = ' || n(counter));
         counter := n.NEXT(counter);
      END LOOP;
   ELSE
      DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.');
   END IF;
END;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Post your code that populates the list.  It's possible you have an empty item in the list.
0
 
wasabi3689Author Commented:
how? give me example
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Instantly
 
slightwv (䄆 Netminder) Commented:
What datatype is p_dev_prods?  I assume its a PL/SQL table but before I can post an example, I want to verify it is a table or Varray.
0
 
wasabi3689Author Commented:
FUNCTION format_unlicensed_text (p_dev_prods IN OUT product_tab)
0
 
slightwv (䄆 Netminder) Commented:
I need the base type so I can produce you an example of what I mean.

Now, what is product_tab defines as?

I need the pase types.
0
 
wasabi3689Author Commented:
I don't find any for product_tab defined as. I search around the package, I don't find it. I do see some procedures has the following like

  p_standard_products      IN OUT   product_tab,
0
 
slightwv (䄆 Netminder) Commented:
It is a type created external to the package.

From a sql prompt: desc product_tab
0
 
Franck PachotCommented:
Hi,
For me the code that you have shown is fine. WHILE ... IS NOT NULL ... NEXT is the right way to loop through a collection. the problem is elsewhere. did you caount the loops to see if it loops through the 3 elements ? Do you have an exception block that catches some errors ?
Regards,
Franck.
0
 
slightwv (䄆 Netminder) Commented:
I have triple checked and confirmed that I am mistaken.  I cannot create a 'gap' be deleting one of the roes in the middle.

If this is happening then there is a bug in your version.

Add a .COUNT to verify you have three rows:
....
dbms_output.put_line('Count: ' || to_char(p_dev_prods.count));
BEGIN
         v_inventory_item_id := p_dev_prods.FIRST;
...
0
 
wasabi3689Author Commented:
I think my code is following this logic

counter := n.FIRST;

WHILE counter IS NOT NULL
      LOOP
         DBMS_OUTPUT.PUT_LINE('Element #' || counter || ' = ' || n(counter));
         counter := n.NEXT(counter);
      END LOOP;

 v_inventory_item_id := p_dev_prods.FIRST;
WHILE v_inventory_item_id IS NOT NULL
         LOOP
               v_inventory_item_id := p_dev_prods.NEXT (v_inventory_item_id);
 END LOOP;

What is wrong with my code?

Also, I make up 3 records, actually there is a list for it, may be 2 records, maybe 10 records...etc
0
 
slightwv (䄆 Netminder) Commented:
We understand that part of your code.  You've posted that before.

You still haven't said 'what' v_inventory_item_id is and how it is defined.

You haven't provided any of the suggest tracking.  The suggested 'counter' method was meant to define a number variable and increment it inside the loop:

...
counter number := 0;
begin
...
while...loop
counter := counter+1;
end loop;
dbms_output.put)line('Count: ' || to_char(counter));

I would suggest just out-putting the collection COUNT from the example http:#a35007841

0
 
wasabi3689Author Commented:
here is what I find

TYPE product_tab IS TABLE OF xx_license_interfaces_pkg.license_product_type
      INDEX BY BINARY_INTEGER;

v_inventory_item_id   NUMBER;

0
 
slightwv (䄆 Netminder) Commented:
Now what is the definition of:
xx_license_interfaces_pkg.license_product_type
0
 
wasabi3689Author Commented:
TYPE LICENSE_PRODUCT_TYPE IS RECORD(
        BASE_VERSION             xx_licensed_products.BASE_VERSION%TYPE,
        LICENSED_PRODUCTS_ID     xx_licensed_products.LICENSED_PRODUCTS_ID%TYPE,
        LICENSE_ID               xx_licensed_products.LICENSE_ID%TYPE,
      .......
    .........
   .........
);

 xx_licensed_products is a base table, all of those like base-version, licensed_products_id... are its fields
0
 
slightwv (䄆 Netminder) Commented:
Thanks for that.

Now did you try either the collection.count method suggested above to verify the collections holds what you think it holds?
0
 
wasabi3689Author Commented:
I add the counter to the code but, I don't know how to apply your numlist to the code, can you provide the sample based on my example again?
0
 
wasabi3689Author Commented:
counter number := 0;
         
      BEGIN
         v_inventory_item_id := p_dev_prods.FIRST;

WHILE v_inventory_item_id IS NOT NULL
         LOOP
            counter := counter+1;
         v_out :=.....
......
        end loop;
......
0
 
slightwv (䄆 Netminder) Commented:
Not the counter.  On mobile right now and cannot reference a link above but here's the example:

Add a .COUNT to verify you have three rows:
....
dbms_output.put_line('Count: ' || to_char(p_dev_prods.count));
BEGIN
         v_inventory_item_id := p_dev_prods.FIRST;
...

0
 
wasabi3689Author Commented:
after adding this line

dbms_output.put_line('Count: ' || to_char(p_dev_prods.count));


I have the following error

Error(1730,21): PLS-00103: Encountered the symbol "." when expecting one of the following:     constant exception <an identifier>    <a double-quoted delimited-identifier> table LONG_ double ref    char time timestamp interval date binary national character    nchar The symbol "<an identifier>" was substituted for "." to continue.

I don't know if my package can accept dbms_output.put_line, but I have a debug procedure I can output the p_dev_prods.count to a table
0
 
slightwv (䄆 Netminder) Commented:
Dmbs_output is a standard package.  Not sure why it is causing an error.

If you can capture the output of the .count, that's all I'm after so have at it.

If the count shows 4 or more objects and the loop only shows 3, we have a problem and we'll go from there.
0
 
wasabi3689Author Commented:

if I use my debug procedure output to t able, i have code like

 p_debug_log('Count1: ' || p_dev_prods.count);

or

p_debug_log('Count1: ' || to_char(p_dev_prods.count));


I have this error

Error(1730,21): PLS-00103: Encountered the symbol "(" when expecting one of the following:     constant exception <an identifier>    <a double-quoted delimited-identifier> table LONG_ double ref    char time timestamp interval date binary national character    nchar The symbol "<an identifier>" was substituted for "(" to continue.
0
 
wasabi3689Author Commented:
anyone knows why I experience this error?
0
 
slightwv (䄆 Netminder) Commented:
I can't help debug your other procedure code and why it doesn't accept those values.

I'll try to set up a simple test using your types tomorrow.  I'm sure I cannot reproduce the loop error of your original question.

The docs state that is is basically impossible for a collection to have gaps and the loop code you posted should work.

So the answer to this question is one of two things:
1- you don't have as many objects in the collection as you think you do
Or
2- it's a bug and you need to contact Oracle Support.

If you can't get the output of the collection.count before you enter the loop, there is no way to figure out which.
0
 
wasabi3689Author Commented:
i am using oracle 8i, is this an issue to make this as  issue?
0
 
slightwv (䄆 Netminder) Commented:
8i is very old and anything is possible.  The bad news is Oracle will not support you.  The worse news, I doubt any Experts here have any 8i left where they can test this for you.
0
 
wasabi3689Author Commented:
even I use

dbms_output.put_line('Count1: ');

I have the same error
0
 
slightwv (䄆 Netminder) Commented:
Are you using sqlplus or some other tool to do all this?
0
 
wasabi3689Author Commented:
no sqlplus
0
 
slightwv (䄆 Netminder) Commented:
And.... What tool are you using?
0
 
wasabi3689Author Commented:
I use oracle sql developer, PL/SQL developer, i experience the same error
0
 
slightwv (䄆 Netminder) Commented:
Never used either of those tools so I cannot help with syntax specific issues with them.

A quick Google shows SQL Developer can handle dbms_output:
http://forums.oracle.com/forums/thread.jspa?threadID=687127

At this point I cannot tell if the issue is with the 8i database or the tools.  Can you connect directly to the database server and try from sqlplus?
0
 
wasabi3689Author Commented:
this code work

begin

dbms_output.enable;
dbms_output.put_line('Count2:'||to_char(p_dev_prods.count));

end;

how do I catch the output, where to see the output of o_char(p_dev_prods.count?
0
 
slightwv (䄆 Netminder) Commented:
You'll need to refer to the documentation for the tool you are using since I've never used any of those tools.

The link I provided mentioned some 'output' window in SQL Developer.
0
 
wasabi3689Author Commented:
The issue remained unresolved yet but all the info provided here are helpful
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here